PowerCenter - Session Performance

1 - About

Look for performance bottlenecks in the following order:

  • Target
  • Source
  • Mapping
  • Session
  • System

2 - Thread statistics

By default, the Integration Service uses one reader thread, one transformation thread, and one writer thread to process a session.

The session log provides the thread statistics.

where:

thread statistics Description
Run time Amount of time the thread runs
Idle time Amount of time the thread is idle. It includes the time the thread waits for other thread processing within the application. Idle time includes the time the thread is blocked by the Integration Service, but it not the time the thread is blocked by the operating system.
Busy time Percentage of the run time the thread is by according to the following formula: {{run time - idle time} / { run time }} * 100
Thread work time The percentage of time the Integration Service takes to process each transformation in a thread. The session log shows the following information for the transformation thread work time:
Thread work time breakdown:
<transformation name>: <number> percent
<transformation name>: <number> percent
<transformation name>: <number> percent
If a transformation takes a small amount of time, the session log does not include it. If a thread does not have accurate statistics, because the session ran for a short period of time, the session log reports that the statistics are not accurate.

3 - Identifying Bottlenecks

The thread with the highest busy percentage identifies the bottleneck in the session. You can ignore high busy percentages when the total run time is short, such as under 60 seconds.

  • If the reader or writer thread is 100% busy, consider using string datatypes in the source or target ports. Non-string ports require more processing.
  • If a transformation thread is 100% busy, consider adding a partition point in the segment. When you add partition points to the mapping, the Integration Service increases the number of transformation threads it uses for the session. However, if the machine is already running at or near full capacity, do not add more threads.
  • If one transformation requires more processing time than the others, consider adding a pass-through partition point to the transformation.
Measure writer transformation reader
Max Time Target Bottleneck Transformation Bottleneck Reader Bottleneck

When the Integration Service spends more time on one transformation, it is the bottleneck in the transformation thread.

  • Analyze performance counters. High errorrows and rowsinlookupcache counters indicate a mapping bottleneck.
  • Add a Filter transformation before each target definition. Set the filter condition to false so that no data is loaded into the target tables. If the time it takes to run the new session is the same as the original session, you have a mapping bottleneck.

3.1 - Session bottleneck

  • If you do not have a source, target, or mapping bottleneck, you may have a session bottleneck. Small cache size, low buffer memory, and small commit intervals can cause session bottlenecks.
  • To identify a session bottleneck, analyze the performance details. Performance details display information about each transformation, such as the number of input rows, output rows, and error rows.

3.2 - System Bottlenecks

You can view system resource usage in the Workflow Monitor. You can use system tools to monitor Windows and UNIX systems.

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.
  • 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.

4 - Partition

If you tune all the bottlenecks, you can further optimize session performance by increasing the number of pipeline partitions in the session. Adding partitions can improve performance by utilizing more of the system hardware while processing the session.

5 - Bottlenecks

5.1 - Target

Small checkpoint intervals, small database network packet sizes, or problems during heavy loading operations can cause target bottlenecks.

5.2 - Source

Inefficient query or small database network packet sizes can cause source bottlenecks. You can create a read test mapping to identify source bottlenecks. A read test mapping isolates the read query by removing the transformation in the mapping and connect the source qualifiers to a file target.

Possible resolutions:

  • Set the number of bytes the Integration Service reads per line if the Integration Service reads from a flat file source.
  • optimize the query.
  • Increase the database network packet size.

5.3 - Mapping

To eliminate mapping bottlenecks, optimize transformation settings in mappings.

6 - Monitor

6.1 - Integration Service

In the Workflow Monitor by selecting an Integration Service, you get the Integration Service Monitor where you can see per running task processes:

  • CPU%. The percentage of CPU usage includes other external tasks running on the system.
  • 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.

dit/powercenter/session_performance.txt ยท Last modified: 2017/09/06 20:13 by 108.162.246.243