Finding/Fixing Performance Bottlenecks
The goal of performance tuning is to
optimize session performance so that the sessions run during the available load
window
and get completed by minimal expected time. Performance tuning s nothing but
identifying the bottlenecks and eliminate them
to get
a better acceptable ETL load time.
The first step in performance tuning
is to identify performance bottlenecks. Performance bottlenecks can occur in
the source and target, the mapping, the session, and the system.
Whenever a session is triggered, Integration service
starts
Data
Transformation Manager (DTM)
process, which is responsible to start reader thread,
transformation thread and writer thread.
Source Bottlenecks
The source side bottlenecks occurs when the
reader thread runs very slow with poor throughput and causes the transformation
thread and writer thread to wait for data. The complete session performance get
impacted with source bottlenecks.
Inefficient source query, source
side database server performance or small database network packet
sizes can cause source bottlenecks
We can upgrade the source database
infrastructure to obtain better performance
and enough network bandwidth. Most importantly the source qualifier
query can be tuned to retrieve the complete data set within minimum time
interval.
Using hints or implementing indexes can boost
up the performance of the select query as expected.
Target Bottlenecks
The target side bottlenecks occurs when the
writer thread runs very slow with poor throughput and causes the transformation
thread and reader thread to wait for memory space. The complete session
performance get impacted with target bottlenecks.
Database network
packet sizes, target side database server poor
performance due to inefficient infrastructure or heavy data load can
cause the
target bottlenecks.
We can schedule our job when the target
database server will have very less data load and all other resources will be
free to gain better bandwidth for our respective jobs. Loading in bulk mode,
increasing commit interval, Dropping Indexes and Key Constraints
or using external loader utility can improve the target performance to some
extant.
Transformation Bottlenecks
Development strategy by not following the
transformation best practices or very complex implementation of business logics
can lead to a critical transformation bottleneck.
In this case the transformation
thread process incoming data very slowly and keeps the reader thread waiting
till the memory is free up and the writer thread waits to get the transformed
data to be written. All together resulting the session performance to be
degraded.
We may redesign the business logic and
implement the most convenient way of using the transformations to get the
optimal output. For example, using sorter at the very beginning of the pipeline
to get rid of unwanted data processing time can improve the mapping
performance.
Session Bottlenecks
Informatica may encounter session bottlenecks
even when there is no reader, writer or transformation bottlenecks. It could
happen due to incorrect settings provided for session
memory
configuration. For example, the DTM buffer size, The default buffer block size,
informatica data cache and index cache size or other UNIX mount size can be
incremented as required to bypass the bottlenecks.
For better performance and reducing the task execution time, there are some other methodologies available which can be implemented as per business requirement. For example session partitioning, Pushdown optimization, Concurrent Workflows etc.
