IBM Infosphere Datastage - Performance Tuning

Performance Tuning will have special considerations when tuning the overall system versus individual job. The approach we need to take to tune individual job will be different to tuning overall batch/application.

When we need to tune individual job,

1.     We need to first isolate the performance issue i.e. which stage is running longer and what operation within the stage is slowing down the job. In order to identify stage impacting the performance, we could use one of following techniques

·        Use “Record Job Performance data” option. This can be checked in job properties (under execution tab) or while running the job from designer , in the job run options and general tab. Then we can analyze this performance data from designer “Fileà Performance Analysis” or from director “Jobà Performance Analysis”. The location of the performance files is defined in the environment variable APT_PERFORMANCE_DATA

·        Use the job monitor function from Director. The job monitor provides job's performance data at a moment of execution. Note: Automatic insertion of sort or similar operations are not considered in this performance data.

·        Set the APT_PM_PLAYER_TIMING environment variable, this will give each operation and CPU time by operator in the job log. This information could be used to identify the stage slowing down the job

·       We can also use Performance Tab in the job details in the Operations Console to view the performance data

2.     Once we know which staging is running longer, we need to investigate further into details by using score dump (APT_DUMP_SCORE) to analyze number of operators, parallelism for each stage and take stage specific performance improvement action. Simple example is, Stage might be running sequentially, we could change this to run parallel to improve the performance.

When we need to tune overall batch/application,

3.      When overall batch is running slow, this mean we either have serious design issues or CPU/Memory/Configuration issue

4.      We need to first check if CPU is utilized near to 100%.  Ideally a good job flow should consume as much CPU as possible. We can check CPU and Memory usage in the IBM InfoSphere Information Server Operation Console à Dashboard or Activity/Resources. If CPU is not utilitsed as much available, this indicate bottlenecks are elsewhere may be at I/O.. so we could increase number of nodes in configuration files and increate number of partitions to increase CPU load.

5.      Unix command IOSTAT could be used to understand the CPU utilization and through put of disk devices. If some disks are used too much and some are not used at all, then we could as unutilized disks as “resource disks” in the configuration file, this would distribute the i/o operations across the disks, could reduce the network latency

6.      If both CPU and Disk resources are properly utilized and no further scope for improvement, then we could manage work load using “workload management” in the IBM InfoSphere Information Server Operation console, to run only certain number of jobs at any given point in time by queuing the jobs (adjust Maximum running jobs in WLM)

7.      Balanced optimization could be used to push the load down to DBMS database servers, this will reduce workload on datastage server improving overall performance

8.      We could Segregate jobs as priority vrs non-priority, and use different configuration files for priority jobs to consume maximum resources and for non-priority jobs to consume less resources and let these non-priority jobs run longer

9.      Despite all the above efforts or increasing the number of nodes by extending resources, if the jobs execution time is not reduced then it must be serious design issue in multiple jobs. In this case we need to tune the jobs or redesign the jobs.