9 Replies Latest reply on May 27, 2019 6:23 AM by Rajan Rath

    Slow Performance of Workflow in Different Environment - V10.1

    Mohit Verma Active Member

      Hi Gurus,

       

      Thanks for providing all the help in the past and I have one more issue faced today.

       

      We have one Main Repository and one Temp repository and both are setup on same unix server. I am running a workflow on both repo and the run time is different i.e. 31 mins on Main repo and 90 mins on Temp repo. This workflow is reading the data from same Database and dumping it in a Flat file. I checked the configuration settings(General, Repositories, Database,Advanced properties) of both repo and did not find any difference. As i said both repo has same primary Node. Worflows and Session properties are same. Relational connection settings are same.

       

      Can you please guide what could be the problem? As I am Developer so please guide the step by step détails if I need to check any other settings anywhere else. I have access on Infomatica Admin tool.

       

      Regards,

      MKV

        • 1. Re: Slow Performance of Workflow in Different Environment - V10.1
          Nico Heinze Guru

          The repository usually doesn't have much to do with performance issues. You will have to investigae the Integration Service settings first. As each Integration Service is associated with one repository only, you must have executed this workflow on separate Integration Services.

          Or did you switch the Integration Service from one repository to the other one before re-running this workflow?

           

          Also it would help if you could please set the Tracing Override in the session properties to Verbose Initialization in both environments and run again. The session logs in Verbose Init mode often give good indication to reasons for differences in total runtime.

          Of course that works only if both sessions save their session logs in different locations.

           

          Regards,

          Nico

          • 2. Re: Slow Performance of Workflow in Different Environment - V10.1
            Mohit Verma Active Member

            Hi Nico,

             

            Thanks for your reply.

             

            We have different repositories(Main and Temp) and so different integration services(Main and Temp) but both are using the same node Infa_Node_nxxxx5469.

             

            I ran the workflows again with Verbose Initialization in both environment and compared the session logs and only below 3 lines are not present in the Temp Session log.

            2019-04-28 08:48:42 : INFO : (7431| DIRECTOR) : (IS | Infa_ABC_Main) : Infa_Node_nxxxx5469 : TM_6708 : Using configuration property [DebuggerDTMHostName,nxxxx5469.us.net.intra]

            2019-04-28 08:48:42 : INFO : (7431| DIRECTOR) : (IS | Infa_ABC_Main) : Infa_Node_nxxxx5469 : TM_6708 : Using configuration property [DebuggerMaxPort ,9239]

            2019-04-28 08:48:42 : INFO : (7431| DIRECTOR) : (IS | Infa_ABC_Main) : Infa_Node_nxxxx5469 : TM_6708 : Using configuration property [DebuggerMinPort,9229]

             

            I compared the both Integration Service properties and found only 2 differences - Security Audit Trail and Optimize Database Schema is set to Yes in Main integration service vs No in Temp.

             

            I noticed a weird thing - Previously, Main repo's workflow was running fast and throughput rows/sec was ~8000 but today Temp repo's workflow ran fast with same throughput. Now I am more confused.

             

            Regards,

            MKV

            • 3. Re: Slow Performance of Workflow in Different Environment - V10.1
              Nico Heinze Guru

              If both Integration Services run on the same node, then chances are that the operating system doesn't distribute resources equally between these processes.

              To be frank, I don't see much you can do about it. Only one thing: you could run the Integration Services on different nodes (as long as you have enough CPU cores licensed), that's all you can do. But as long as both services run on the same node, you will have to look for OS and hardware resources allocated to each service and the dependent processes (i.e. the pmdtm processes running the respective sessions) to get any meaningful statements about resource consumption.

               

              Sorry that I don't have any "better" advice to share. That's just my experience.

               

              Regards,

              Nico

              • 4. Re: Slow Performance of Workflow in Different Environment - V10.1
                Rajan Rath Guru

                Hello Mohit,

                 

                The best way to start with analyzing is to check the session logs for the two repository runs and check where most of the time is spent or where the delay is observed; as the difference / delay is about 60 minutes.

                This should help with the initial review on the issue.

                 

                Regards,

                Rajan

                • 5. Re: Slow Performance of Workflow in Different Environment - V10.1
                  Mohit Verma Active Member

                  Hi Nico,

                   

                  You always proivde the good suggestions. So No sorry please and I am very thankful to you.

                   

                  In Dev, we have only one Node available so cant change it.

                   

                  I doubt it is OS or Hardware issue because nothing is gonna change when I am running the process. I am using same Autosys, Unix Server, Database, Scripts, Node etc. I just change the Informatica Repo and Integration Service name each time in our Config file to run the workflow on different repo/IS.

                   

                  I checked everything but could not find anything which could lead to performance issue and now will raise the ticket for Informatica Support to debug it. Lets see what they will find.

                   

                  Regards,
                  MKV

                  • 6. Re: Slow Performance of Workflow in Different Environment - V10.1
                    Rajan Rath Guru

                    Hello Mohit,

                     

                    The issue could here be with the session/workflow execution which takes more time.

                    So no matter what is done/used Autosys, Unix server, scripts etc; the issue might still occur. - -This is just an assumption at this point.

                     

                    You can review the two session logs and workflow logs to compare where most of the time is spent; for an initial review.

                    In parallel, you may have the Informatica GCS contacted for a detailed review.

                     

                    Regards,

                    Rajan

                    • 7. Re: Slow Performance of Workflow in Different Environment - V10.1
                      Mohit Verma Active Member

                      Hi Rajan,

                       

                      Sure. I will compare the session log again. Just FYI, This mapping has below design -

                       

                      12 SD -> SQ -> Exp -> Filter - > File Target.

                       

                      SQ has a big SQL query which is fetching ~65M rows so we have created 5 Pass Through Partitions to process ~13M rows each to dump the data in a single file. When I checked the session logs in past with Verbose Intitialization(as suggested by Nico) then only the Reader was taking too much time to read the rows in Temp(~1800 rows/sec per partition). I will check the logs again and confirm you.

                       

                      Regards,

                      MKV

                      • 8. Re: Slow Performance of Workflow in Different Environment - V10.1
                        Mohit Verma Active Member

                        Hi Rajan/Nico,

                         

                        This issue has resolved. We have raised this issue to Informatica Support team and provided all the Session logs and Database AWR reports.

                         

                        Support Team has suspected below two main points during the workflow run.

                        1)High load in database server

                        2)Network latency is slow

                         

                        They also demanded PMSTACK report on PMDTM and PMSERVER when the issue was observed but we did not generate this report when we faced the issue. Meanwhile, we migrated to V10.2 from V10.1 and when I ran the workflow again on both IS then did not notice the performance issue

                         

                        We have asked Support Team to close the ticket becuase we are not facing any performance issue now. It seems then some issue of v10.1 or there was high load in database server that time. So I would suggest everyone if they also face same kind of perfomance issue then immediately investigate the AWR and PMSTACK reports.

                         

                         

                        • 9. Re: Slow Performance of Workflow in Different Environment - V10.1
                          Rajan Rath Guru

                          Agree. The analysis of the logs, pmstrack traces, AWR reports and strace output (at times) needs to be used to figure out the exact bottleneck.

                          In a case if the support team has already analyzed the information (with the evidences) the bottleneck might be at the DB.

                           

                          It's good to know that it has been resolved now.

                           

                          However, if it pops up again, do collect the same information and have it reviewed to see if the bottleneck is at DB. In that case, the DBA will need to review on the exact issue at DB.

                           

                          Regards,

                          Rajan