Tuning for S&P when data amounts are large

Body

Tuning for S&P when data amounts are large

Recently came across an issue where the S&P agent had not be running for a few days and the amount of raw data to be dealt with was huge.
By this, I mean tables with millions of rows.

If the index is not updated or current, this can cause problems for the S&P agent, as a lot of I/O must be done to find the data pages on disk.

This was causing the a number of issues, with a single S&P run taking a number of days and still not finishing.

One issue seen was that there was nothing  written to the *sy_java log for a considerable time, leading to the thought that the S&P agent had hung.

However it was still running, just not writing to the files, as it was running the deletes on the table.
The only way to be sure the S&P agent is still running in these circumstances is to check for database delete actions that are actively performing I/O operations.

It can take a number of days for the agent to catch up if there is a large amount of data to process.  

In this case some of the larger tables were manually cleared of data, to allow the S&P agent to get back up to date.

As well as this some fine tuning was done.

All the values listed are set in the sy.ini file and the agent stopped and started to allow the changes to take effect.

The values of  KSY_MAX_ROWS_PER_TRANSACTION and  KSY_MAX_WORKER_THREADS were tuned.

For  KSY_MAX_WORKER_THREADS  the suggested number of worker threads is 2 or 4 times the number of processors on the host system.  In a large scale environment, even for a single processor system, you should set the KSY_MAX_WORKER_THREADS number to 4.

Note that configuring more threads than attribute groups will not decrease the processing time, because each thread works on one attribute group at a time.

 KSY_MAX_ROWS_PER_TRANSACTION this parameter specifies the maximum number of rows per database transaction, and can be thought of as a batching factor.
The defualt for this parameter is 1000.

However it should be noted that the number of rows dealt with at one time by each thread is (batch factor) :

 
KSY_MAX_ROWS_PER_TRANSACTION / KSY_MAX_WORKER_THREADS ==>  ROWNUM

In this case since  KSY_MAX_ROWS_PER_TRANSACTION was set to the default of 1000  and KSY_MAX_WORKER_THREADS=4,  which gave a ROWNUM of 250,
so only 250 rows were dealt with per batch.

In this case KSY_MAX_ROWS_PER_TRANSACTION was increased to 5000, giving 1250 rows dealt with in each batch, which considerably reduced the amount of time needed for the S&P agent to
run through the back log of data.

However there is a caveat to these tuning levels, as the threads can run out of memory if these values are set to high.

The java memory can be increased  with the KSZ_JAVA_ARGS and in this case the value was set to:
 
KSZ_JAVA_ARGS=-Xms512M -Xmx768M

There may also be issues with the database if levels are set too high, check your RDBMS logs and RDBMS Administrator for any errors after the changes have been made.
For DB2 the logs to check is db2diag.log and for Oracle it is the Alert log.