Reducing Q Apply memory usage for synchronized processing

When you use synchronized apply, reducing the amount of memory that is required by each Q Apply agent thread for performing SQL operations at the target is important.

When running in synchronized mode, Q Apply reads ahead until it fills up available memory. For this reason, you might need to increase the memory_limit value for the replication queue map to allow more read-ahead. You might also improve performance by tuning the statement cache size to provide more memory for read-ahead.

In general, the default statement cache size is adequate for most workloads. When tuning, increase memory_limit first.

A parameter and a monitoring column are provided to help tune Q Apply memory usage:

agent_stmt_cache_sz

This parameter controls the maximum number of SQL statement prepares that each Q Apply agent thread can cache for performing insert, update, and delete operations at the target. You set the value of agent_stmt_cache_sz by updating the corresponding column in the IBMQREP_RECVQUEUES table.

You can reduce memory consumption for executing SQL statements by lowering the cache size that each agent uses for prepared SQL statements. Monitor the number of statements that are being prepared to gauge the effect of a reduced cache size.

The default for agent_stmt_cache_sz is 300 statements and the valid range is 50 to 500. To change agent_stmt_cache_sz, you must stop and start either the receive queue or the Q Apply program.

STMTS_PREPARED

This column in the IBMQREP_APPLYMON table records the number of SQL statements that the Q Apply program prepared with the database manager during the monitor interval.

Q Apply only prepares a statement if it is not found in the agent cache. If this value remains consistently high, try increasing agent_stmt_cache_sz. In addition, the ROWS_APPLIED column in IBMQREP_APPLYMON provides an estimate of the number of statements that are looked up in the cache. The formula STMTS_PREPARED / ROWS_APPLIED provides the cache miss ratio (the number of times an SQL statement was not located in the cache and had to be prepared).