8 Replies Latest reply on Oct 28, 2020 11:44 AM by user101600

    To increase throughput of a session in informatica

    User informatica Active Member

      Hi,

       

      I want to increase the through put of a session which has a webservice. Is there a way i can increase the through put in admin console or anywhere else ?Kindly advice.

       

      Thanks in advance!

        • 1. Re: To increase throughput of a session in informatica
          Nico Heinze Guru

          As far as I know, there are no sessions available in IDQ, only in PowerCenter.

          But the Administrator tool is nothing to manage anything in PowerCenter.

          So, for which application world do you ask?

          Furthermore there's a Performance Tuning guide available for both (IDQ and PowerCenter) as part of the documentation set for the Informatica Platform. Please read the initial parts of each chapter; this will help you to find out whether you have a so-called source bottleneck, a target bottleneck, and/or a transformation bottleneck.

           

          Regards,

          Nico

          • 2. Re: To increase throughput of a session in informatica
            Seema Yaligar Seasoned Veteran

            Yes, as suggested by Nico you need to identify the bottleneck first based on that you can increase the performance.

            Please share the complete session log and the workflow export from repository manager to check which thread is taking more time.

             

            Thanks,

            Seema

            • 3. Re: To increase throughput of a session in informatica
              Seema Yaligar Seasoned Veteran

              And below id the performance tuning guide for PowerCenter which Nico suggested. You can refer the same for more information,

               

              https://docs.informatica.com/data-integration/powercenter/10-4-0/performance-tuning-guide/preface.html

               

              Hope this helps.

               

              Thanks,

              Seema

              • 4. Re: To increase throughput of a session in informatica
                User informatica Active Member

                Hi Seema,

                 

                Thanks for the information. Please find the requested files and advice.

                • 5. Re: To increase throughput of a session in informatica
                  Seema Yaligar Seasoned Veteran

                  Hi,

                   

                  From the shared session log I can see session has failed with the below error,

                   

                  2020-10-20 14:18:10 : ERROR : (42271162 | MAPPING) : (IS | PSX_Int_DevQ) : node01_DEVQ : CMN_1022 : Database driver error...

                  CMN_1022 [

                  Database driver error...

                  Function Name : Logon

                  ORA-12154: TNS:could not resolve the connect identifier specified

                   

                   

                  Database driver error...

                  Function Name : Connect

                  Database Error: Failed to connect to database using user [PHL_READ_ONLY] and connection string [QCLOUDTIP].]

                   

                  Please check whether you are able to connect to the Oracle DB outside the Informatica or not using sqlplus from Informatica machine.

                   

                  Please fix this issue and share us the successful session log which will give us more information on which thread has taken more time.

                   

                  Thanks,

                  Seema

                  • 6. Re: To increase throughput of a session in informatica
                    JanLeendert Wijkhuijs Active Member

                    Hi,

                     

                    First you would need to identify what kind of issue you have. This can be cpu related or memory related (or a combination both of course).

                    If your issue is memory related you can find this in the session log.
                    You could encounter messages like "Waiting for consumer to free buffer block. Please increase the dtm buffer pool size."
                    Also messages regarding memory allocations for sorters like "Sorter Transformation [sorter_transformationname] required 2-pass sort (1-pass temp I/O: 212697088 bytes). You may try to set the cache size to 243 MB or higher for 1-pass in-memory sort."

                    Memory settings can be viewed in the session on the mapping tab >> Memory Properties.

                    Hope this helps.

                    Regards,
                    JanLeendert

                    • 7. Re: To increase throughput of a session in informatica
                      user101600 Guru

                      In all honesty there is no feature that will increase the session speed by the press of a button. Just like other have said you have to identify the bottle neck.

                      The various areas that can cause performance problems are as follows:

                       

                      - The issue can be a problem with the source Database or target Database:

                      For example are we reading the data slowly or we are writing the data slowly

                      - The bottleneck can be attributed to slow network performance

                      - The bottleneck can be attributed to Hardware related issues.

                      For example there are not enough resources or memory or slow cpu on the powercenter server Box

                      - The bottleneck can be attributed to the PC Mapping logic.

                      For example The time that it takes to Create Cached lookups or a joiner.

                      - The bottleneck can be attributed to PowerCenter  session/workflow not being optimally configured

                       

                       

                      In order to help debug this issue the very first thing that we need to do is to try establish some sort of a baseline. Once we establish a baseline we can start to add or remove things out of the equation one at a time in order to get down to the core problem.

                      • 8. Re: To increase throughput of a session in informatica
                        user101600 Guru

                        Please be aware that there are only a handful of performance and tuning knobs you can adjust in PC.

                        The knobs that you can turn in the session properties are as follows:

                         

                        Commit interval =

                        DTM Pool size =

                        block size =

                        - Check your commit interval in the session properties

                        The lower the number, the more frequently the commits occur and the slower the session will run.

                         

                        -PC load balancer settings

                        If you have multiple nodes and using the PC load balance functionality then The following parms can be adjusted  for load balancing.

                        -Number of Process

                        -Maximum CPU run Queue Length

                        -Maximum Memory %

                         

                        - PC mapping settings that can affect performance.

                        a. Lookups

                        - Do you have any lookups in the mapping? If so, then this can definitely slow things down when the lookup takes a very long time to retrieve data.

                        - You might have a lookup that is defined with the wrong datatype in the mapping, thus the lookup is doing an implicit conversion and not using the indexes defined in the lookup table.

                        - The Problem can also be attributed to a persisted lookup cache. If you have lookups in your mapping check to see if you can rebuilt the original look-up, then make a copy instead of a short cut so you will no longer be persisting the Cache, that should help as well.

                         

                        b. sorters

                        - If you have sorters in your mapping then you might need to increase the sorter cache size.

                        - If you have a joiner in the mapping then it might be a good practice to Add sorter right before joiner. Then make sure that you have defined the following options ‘sorted Input’ and ‘master sort order’ ports are checked in joiner.

                         

                        c. Partitions

                        - Are you using using partitioning in the mapping?

                        Please be aware that you cannot use partitioning if you have a union transformation in the mapping.

                         

                        e. Datatypes

                        If you have some target tables defined with the nvarchar2 datatype, the you might consider to change it varchar2

                         

                         

                        In regards to documentation here another link that you might find useful

                        Velocity - Performance and Tuning

                        and

                        http://velocity.informatica.com/index.php/velocity-content/best-practices-all-bp