How to Identify Throughput Issues
Ideally, how do you determine if your performance issues is due to
read or write throughput of your session? Below are few typical issues that
result in poor performance (low throughput values)
Target
Commit Point is too small
The default target commit point of 10000 is used when processing
large data volumes. This results in commits every 10000 rows even if the
mapping is processing millions or hundreds of millions of rows. Setting a
commit point of 100,000 or even 1 million rows is not unreasonable as data
volumes grow and grow. There is a point where roll-back and commit points
should be balanced, but if processing 40 million rows, a commit point of under
500,000 is probably too small.
Auto
Memory settings are too small
Adding memory to the session is the single item with the biggest
impact if you see throughput values in the Workflow Monitor (or in the query
results) that are relatively small in relation to the number of rows being
processed. Testing is needed to find the sweet spot of adding more memory and
not adding memory when it no longer helps, and also adding memory while keeping
in mind the resource requirements of other jobs running concurrently. Usually
adjusting to 1 GB or 2 GB of Auto Memory can make a difference without being
out of line. Be sure to balance the Maximum value for the suto memory with the
Maximum Percentage of available memory since it is the smaller of the two that
is used.
Multi-pass
Sorts
Review the session log for messages about multi-pass sorts. This
is an indication that more memory is required. The sorter transformations
default to Auto for the sorter cache size, and in the past defaulted to 200 for
this value. See the message in the log and set the Auto Memory or explicitly
set the sorter cache to a larger value so that the sort can be performed in 1
or 2 passes. Note that the Auto Memory setting for sorters and other
transformations that cache data is a shared value across each transformation.
You may need to adjust the Auto Memory setting to make it an appropriate value.
Not
Sorting before Aggregators and Joiners
Review the session log for messages about nested-loop algorithms
for Aggregators and Joiners. These are indications that you did not sort before
them, or you did not mark the transformation as having sorted input. Sorting
does take some cycles, but the sort-merge algorithm is much faster and makes
the other cache files much smaller, and so is worth the effort to improve
performance.
Complex
SQL Overrides
Using SQL Overrides pushes the logic to the database when
initially querying the data or in lookups, etc. This is not a best practice for
a number of reasons:
a. It hides the
metadata of the mapping
b. It hides
transformation logic being done on the data
c. It does not
allow the Informatica server to take some of the load of the transformation of
data
It is a good practice to take the SQL that will be run against the
database from the session log and to run an explain plan and tune it
appropriately in the database by adding the right indexes, etc., that assist in
the query performance.
Using a
Lookup instead of a Join
Both Lookups and Joins can be used to reference data in another
table based on data in the main source data and specified logic. Lookup results
are cached either in memory, if they are small enough, or to a file on disk. If
the results are large, then it does not make sense to use the lookup and is
likely more efficient to use a Join instead. This can be a join performed in a
Source Qualifier if it is coming from the same instance as the source table, or
with a Joiner transformation if they are disparate database sources.
It was observed that there are mappings that were caching lookup
results for 40 million or more rows. This is a clear indication that a joiner
should have been used instead. Generally, for any table with more than a
million rows it is better to use a Join instead of a Lookup. The items marked
in the session log below are lookups that should become joins instead because
of the number of rows returned.
Unsorted
Input in a Lookup
The session log might indicate messages with error code TT_11195
regarding unsorted input for a lookup transformation.
This occurs because the SQL Override eliminated the sort, the the
ports in the lookup transformation are not the same datatype and exact
precision as the definition in the database, or there is binary data as part of
the lookup results and the sort order is ascii and not binary in the mapping.
Some of the items with poor throughput were flagged and we reviewed those mappings, sessions, and session logs
to look at Auto Memory Settings, DTM buffer Size settings, session log erros
and bottlenecks, logic in the mapping regarding SQL Overrides, Lookup size,
sorting before Aggregator and Joins, etc. The candidates for this review were
selected because, the execution time was longer than 1 hour, the number of rows
per second of throughput was less than 10000 while the number of rows processed
was greater than 10000. This was a starting point to try to find some of the worst
jobs and review them. We did not get input from the development or production
support teams to guide our efforts.
List "WRITE" Throughput of the
session
SELECT l.subject_area,
workflow_name,
l.session_name,
l.table_name,
l.successful_rows,
l.successful_affected_rows,
l.throughput AS write_thruput,
l.start_time,
l.end_time,
ROUND ( (l.end_time - l.START_time) *
24 * 60 * 60) Run_Time
FROM
rep_sess_tbl_log l, rep_workflows w
WHERE
l.start_time > TO_DATE ('20181101', 'YYYYMMDD')
AND last_error_code IN (0, 7004) -- last one without an error
AND (l.start_time, l.session_name) IN
( SELECT MAX (start_time), -- latest run date -
uncomment to get all
session_name
FROM rep_sess_tbl_log
GROUP BY session_name)
AND l.successful_rows > 0
AND l.workflow_id = w.workflow_id
ORDER
BY l.subject_area,
w.workflow_name,
l.session_name,
l.start_time
List "READ" Throughput of the session
SELECT t.task_name AS workflow_name,
ti.instance_name AS session_name,
swl.instance_name AS table_name,
swl.applied_rows,
swl.affected_rows,
swl.thruput AS read_thruput,
swl.start_time,
swl.end_time,
ROUND ( (swl.end_time -
swl.START_time) * 24 * 60 * 60) Run_Time
FROM opb_swidginst_log swl, opb_task_inst
ti, opb_task t
WHERE
swl.last_err_code IN (0, 7004)
AND (swl.start_time,
swl.instance_name) IN
( SELECT MAX (start_time), instance_name
FROM opb_swidginst_log
GROUP BY instance_name)
AND swl.session_id = ti.task_id
AND TI.WORKFLOW_ID = T.TASK_ID
AND (applied_rows > 0 OR
affected_rows > 0)
GROUP
BY t.task_name,
ti.instance_name,
swl.instance_name,
swl.applied_rows,
swl.affected_rows,
swl.thruput,
swl.start_time,
swl.end_time,
ROUND ( (swl.end_time -
swl.START_time) * 24 * 60 * 60)
ORDER
BY t.task_name,
ti.instance_name,
swl.instance_name,
swl.start_time
Average
Run Time and Throughput
SELECT l.subject_area,
w.workflow_name,
l.session_name,
l.table_name,
AVG (l.throughput) AS write_thruput,
AVG (ROUND ( (l.end_time -
l.START_time) * 24 * 60 * 60)) Run_Time,
AVG (l.successful_rows)
AS avg_success_rows,
AVG (l.successful_affected_rows)
AS avg_affected_rows
FROM rep_sess_tbl_log l, rep_workflows w
WHERE
l.start_time > TO_DATE ('20180210', 'YYYYMMDD')
--and subject_area='FOLDERName'
AND l.workflow_id = w.workflow_id
GROUP BY l.subject_area,
w.workflow_name,
l.session_name,
l.table_name
ORDER BY l.subject_area,
w.workflow_name,
l.session_name,
l.table_name
No comments:
Post a Comment