Informatica Performance Tuning Guide, Identify Performance Bottlenecks - Part 2

  ETL Admin , Performance Tips

In our previous article in the performance tuning series, we covered the basics of Informatica performance tuning process and the session anatomy. In this article we will cover the methods to identify different performance bottlenecks. Here we will use session thread statistics, session performance counter and workflow monitor properties to help us understand the bottlenecks.

Source, Target & Mapping Bottlenecks Using Thread Statistics

Performance Tuning Tutorial Series
Part I : Performance Tuning Introduction.
Part II : Identify Performance Bottlenecks.
Part III : Remove Performance Bottlenecks.
Part IV : Performance Enhancements.

Thread statics gives run time information from all the three threads; reader, transformation and writer thread. The session log provides enough run time thread statistics to help us understand and pinpoint the performance bottleneck.

Gathering Thread Statistics

You can get thread statistics from the session long file. When you run a session, the session log file lists run time information and thread statistics with below details.

• Run Time : Amount of time the thread runs. 
• Idle Time : Amount of time the thread is idle. Includes the time the thread waits for other thread processing.
• Busy Time : Percentage of the run time. It is (run time - idle time) / run time x 100. 
• Thread Work Time : The percentage of time taken to process each transformation in a thread.

Note : Session Log file with normal tracing level is required to get the thread statistics.

Understanding Thread Statistics

When you run a session, the session log lists run information and thread statistics similar to the following text. 

If you read it closely, you will see reader, transformation and writer thread and how much time is spent on each thread and how busy each thread is. Additional to that, transformation thread shows how much  busy each transformation in the mapping is.

The total run time for the transformation thread is 506 seconds and the busy percentage is 99.7%. This means the transformation thread was never idle for the 506 seconds. The reader and writer busy percentages were significantly smaller, about 9.6% and 24%. In this session, the transformation thread is the bottleneck in the mapping. 

To determine which transformation in the transformation thread is the bottleneck, view the busy percentage of each transformation in the thread work time breakdown. The transformation RTR_ZIP_CODE had a busy percentage of 53%.

Hint : Thread with the highest busy percentage is the bottleneck.

Session Bottleneck Using Session Performance Counters

All transformations have counters to help measure and improve performance of the transformations. Analyzing these performance details can help you identify session bottlenecks. The Integration Service tracks the number of input rows, output rows, and error rows for each transformation.

Gathering Performance Counters

You can setup the session to gather performance counters in the workflow manager. Below image shows the configuration required for a session to collect transformation performance counters.

Understanding Performance Counters

Below shown image is the performance counters for a session, which you can see from the workflow monitor session run properties.. You can see the transformations in the mapping and the corresponding performance counters.

A non-zero counts for readfromdisk and writetodisk indicate sub-optimal settings for transformation index or data caches. This may indicate the need to tune session transformation caches manually.

A non-zero count for Errorrows indicates you should eliminate the transformation errors to improve performance.

• Errorrows : Transformation errors impact session performance. If a transformation has large numbers of error rows in any of the Transformation_errorrows counters, you should eliminate the errors to improve performance.
• Readfromdisk and Writetodisk : If these counters display any number other than zero, you can increase the cache sizes to improve session performance.
• Readfromcache and Writetocache :  Use this counters to analyze how the Integration Service reads from or writes to cache.
• Rowsinlookupcache : Gives the number of rows in the lookup cache. To improve session performance, tune the lookup expressions for the larger lookup tables.

Session Bottleneck Using Session Log File

When the Integration Service initializes a session, it allocates blocks of memory to hold source and target data. Not having enough buffer memory for DTM process, can slowdown reading, transforming or writing and cause large fluctuations in performance.

If the session is not able to allocate enough memory for the DTP Process, Integration service will write a warning message in to the session log file and gives you the recommended buffer size.  Below is a sample message seen in the session

Message: WARNING: Insufficient number of data blocks for adequate performance. Increase DTM buffer size of the session. The recommended value is xxxx.

System Bottleneck Using the Workflow Monitor

You can view the Integration Service properties in the Workflow Monitor to see CPU, memory, and swap usage of the system when you are running task processes on the Integration Service. Use the following Integration Service properties to identify performance issues: 

• CPU% : The percentage of CPU usage includes other external tasks running on the system. A high CPU usage indicates the need of additional processing power required by the server.
• Memory Usage : The percentage of memory usage includes other external tasks running on the system. If the memory usage is close to 95%, check if the tasks running on the system are using the amount indicated in the Workflow Monitor or if there is a memory leak. To troubleshoot, use system tools to check the memory usage before and after running the session and then compare the results to the memory usage while running the session. 
• Swap Usage : Swap usage is a result of paging due to possible memory leaks or a high number of concurrent tasks.

What is Next in the Series

The next article in this series will cover how to remove bottlenecks and improve session performance. Hope you enjoyed this article, please leave us a comment or feedback if you have any, we are happy to hear from you.

Share with your friends