memory_limit parameter

The memory_limit parameter determines the amount of memory that a Q Apply program can use as a buffer to process transactions from one receive queue. You set this parameter for the replication queue map that contains the receive queue.

When the memory limit is reached, the browser thread stops getting messages from the receive queue and waits for agent threads to apply more transactions to the target table to free memory. If a single transaction is large enough to exceed the memory limit, the browser thread applies partial rows of the transaction before processing more rows of the same transaction. Both of these actions slow performance.

A useful approach for determining the memory that is required for each receive queue is to start with a high value for the memory_limit parameter, such as 32 MB, and then check the value of the following columns in the IBMQREP_APPLYMON table to determine whether this value is adequate for your workload:
  • The CURRENT_MEMORY column measures the memory usage by a Q Apply program for a receive queue. If the value is close to your setting for the memory_limit parameter, you might need to increase the amount of memory that is allocated for the receive queue.
  • The MEM_FULL_TIME column measures the number of milliseconds that a Q Apply program could not process transactions from a receive queue because agents were using all available memory. If this value is greater than 0, increase the memory allocation for the receive queue. If the value of MEM_FULL_TIME is 0, increasing the amount of memory available for a receive queue will not speed processing time or improve throughput.
  • The MONSTER_TRAN column measures the number of transactions that exceeded the memory limit.
  • The APPLY_SLEEP_TIME column measures the number of milliseconds that all Q Apply agents went to sleep mode because they could not get a transaction from the memory. If this value is 0 and MEM_FULL_TIME is greater than 0, you might need to increase the value of the memory limit. If MEM_FULL_TIME is equal to 0 and the percent sleep time for agents is greater than 30%, you might need to reduce the value of the num_apply_agents parameter. If both MEM_FULL_TIME and APPLY_SLEEP_TIME are greater than 0, then you either have a long transaction or batch job or your target database needs to be tuned.

The default setting for the Q Capture program is to replicate LOB values inline with the transaction messages. You should adjust the memory limit so that transaction messages with LOB values do not exceed the memory limit and result in slower performance.

Requirement: The memory limit for a receive queue must be at least three times greater than the max_message_size parameter for the Q Capture program.

The maximum allowed value for memory_limit is 2 GB.