Performance Tuning

Performance Tuning The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks. The most common performance bottleneck occurs when the Informatica Server writes to a target database. Once you determine the location of a performance bottleneck, you can eliminate the bottleneck by following these guidelines: •

• • •

Eliminate source and target database bottlenecks. Have the database administrator optimize database performance by optimizing the query, increasing the database network packet size, or configuring index and key constraints. Eliminate mapping bottlenecks. Fine tune the pipeline logic and transformation settings and options in mappings to eliminate mapping bottlenecks. Eliminate session bottlenecks. You can optimize the session strategy and use performance details to help tune session configuration. Eliminate system bottlenecks. Have the system administrator analyze information from system monitoring tools and improve CPU and network performance.

If you tune all the bottlenecks above, 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. Performance bottlenecks can occur in the source and target databases, the mapping, the session, and the system. You can identify performance bottlenecks by running test sessions, viewing performance details, and using system monitoring tools. 1. Identifying Target Bottlenecks You can identify target bottlenecks by configuring the session to write to a flat file target. If the session performance increases significantly when you write to a flat file, you have a target bottleneck. If your session already writes to a flat file target, you probably do not have a target bottleneck. Causes for a target bottleneck may include small check point intervals, small database network packet size, or problems during heavy loading operations.

1 GE Confidential

2. Identifying Source Bottlenecks Performance bottlenecks can occur when the Informatica Server reads from a source database. If your session reads from a flat file source, you probably do not have a source bottleneck. You can improve session performance by setting the number of bytes the Informatica Server reads per line if you read from a flat file source. If the session reads from relational source, you can use a filter transformation, a read test mapping, or a database query to identify source bottlenecks. 3. Identifying Mapping Bottlenecks You can identify mapping bottlenecks by using a Filter transformation in the mapping. You can also identify mapping bottlenecks by using performance details. High errorrows and rowsinlookupcache counters indicate a mapping bottleneck. If you determine that you do not have a source bottleneck, you can add a Filter transformation in the mapping 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. Multiple lookups can slow down the session. You might improve session performance by locating the largest lookup tables and tuning those lookup expressions. Transformation errors impact session performance. If a session has large numbers in any of the Transformation_errorrows counters, you might improve performance by eliminating the errors. 4. Identifying a Session Bottleneck You can identify a session bottleneck by using the performance details. The Informatica Server creates performance details when you enable Collect Performance Data in the Performance settings on the Properties tab of the session properties. Performance details display information about each Source Qualifier, target definition, and individual transformation. All transformations have some basic counters that indicate the number of input rows, output rows, and error rows. Any value other than zero in the readfromdisk and writetodisk counters for Aggregator, Joiner, or Rank transformations indicate a session bottleneck. Low BufferInput_efficiency and BufferOutput_efficiency counter values also indicate a session bottleneck. Small cache size, low buffer memory, and small commit intervals can cause session bottlenecks.

2 GE Confidential

Aggregator, Rank, and Joiner Readfromdisk and Writetodisk Counters

If a session contains Aggregator, Rank, or Joiner transformations, examine each Transformation_readfromdisk and Transformation_writetodisk counter. If these counters display any number other than zero, you can improve session performance by increasing the index and data cache sizes. To view the session performance details while the session runs, right-click the session in the Workflow Monitor and choose Properties. Click the Properties tab in the details dialog box. Source and Target BufferInput_efficiency and BufferOutput_efficiency Counters

If the BufferInput_efficiency and the BufferOutput_efficiency counters are low for all sources and targets, increasing the session DTM buffer pool size may improve performance. Under certain circumstances, tuning the buffer block size may also improve session performance. 5. Identifying a System Bottleneck You can identify system bottlenecks by using system tools to monitor CPU usage, memory usage, and paging. The Informatica Server uses system resources to process transformation, session execution, and reading and writing data. The Informatica Server also uses system memory for other data such as aggregate, joiner, rank, and cached lookup tables. You can use system performance monitoring tools to monitor the amount of system resources the Informatica Server uses and identify system bottlenecks. On Windows, you can use system tools in the Task Manager or Administrative Tools. On Windows, you can view the Performance and Processes tab in the Task Manager (use Ctrl-Alt-Del and choose Task Manager). The Performance tab in the Task Manager provides a quick look at CPU usage and total memory used. You can view more detailed performance information by using the Performance Monitor on Windows (use StartPrograms-Administrative Tools and choose Performance Monitor). 1. Percent processor time 2. Pages/second 3. Physical disks percent time 4. Physical disks queue length 5. Server total bytes per second

3 GE Confidential

Optimizing the Target Database If your session writes to a flat file target, you can optimize session performance by writing to a flat file target that is local to the Informatica Server. If your session writes to a relational target, consider performing the following tasks to increase performance: •

Drop indexes and key constraints.

If you decide to drop and rebuild indexes and key constraints on a regular basis, you can create pre- and post-load stored procedures to perform these operations each time you run the session. Note: To optimize performance, use constraint-based loading only if necessary. •

Increase checkpoint intervals.

The Informatica Server performance slows each time it waits for the database to perform a checkpoint. To increase performance, consider increasing the database checkpoint interval. •

Use bulk loading.

When bulk loading, the Informatica Server bypasses the database log, which speeds performance. Without writing to the database log, however, the target database cannot perform rollback. As a result, you may not be able to perform recovery. Therefore, you must weigh the importance of improved session performance against the ability to recover an incomplete session. •

Use external loading.

If your target database runs on Oracle, you can use the Oracle SQL*Loader utility to bulk load target files. When you load data to an Oracle database using a pipeline with multiple partitions, you can increase performance if you create the Oracle target table with the same number of partitions you use for the pipeline. •

Increase database network packet size.

You can increase the network packet size in the Informatica Workflow Manager to reduce target bottleneck. For Oracle, increase the network packet size in tnsnames.ora and listener.ora. If you increase the network packet size in the Informatica Server configuration, you also need to configure the database server network memory to accept larger packet sizes. •

Optimize Oracle target databases.

4 GE Confidential

If your target database is Oracle, you can optimize the target database by checking the storage clause, space allocation, and rollback segments. Optimizing the Source Database If your session reads from a flat file source, you can improve session performance by setting the number of bytes the Informatica Server reads per line. By default, the Informatica Server reads 1024 bytes per line. If each line in the source file is less than the default setting, you can decrease the Line Sequential Buffer Length setting in the session properties. If your session reads from a relational source, review the following suggestions for improving performance: • • • • •

Optimize the query. Create tempdb as in-memory database. Use conditional filters. Increase database network packet size. Connect to Oracle databases using IPC protocol.

5 GE Confidential