Capture queue has backlog / reader is slow (4250617)

Reader process falls behind frequently (is slow) and the capture queue always has messages in the backlog. Capture is current with Oracle. CPU usage for the reader is high, renicing the read process does not help.

One reason on 6.0 and later version is skip full rollback logic causes more entries to be written to tcache file and result in growing large tcache file when there are lots of concurrent full rollback transactions happening on source system . When checkpointing, the reader will read the tcache file, if this file is large then the reader will have more work to perform, hence the high CPU usage.
 

WORK AROUND:

For 7.0 and above, you can dump the tcache file to see if there are a lot of entries related to skip full rollback messages.

go to prod dir/util

./dumpfile -o dump.out

choose 6 to dump tcache file and provide the tcache file name (under var dir/state, ls -lt *tcache*)

If there are a lot of entries related to full rollback, then set this parameter on source on disable skip full rollback feature

sp_ctrl> stop capture

sp_ctrl> set param SP_OCT_REQUIRED_DATA_IS_LOGGED 0

sp_ctrl> start capture

This parameter is introduced in 6.1.1 and above.

If the tcache is already very big, you may need to rename the tcache files and allow read to create brand new ones by the following method.

Warning: this action may have a slight chance of reader failed to route commits thus leading to OOS.

1. Run ls -l $SP_SYS_VARDIR/state/*tcache*

2. If the files are more than 10 meg,

a. Stop read
b. Make sure it has stopped
c. Rename both tcache files (a and b)
d. Start read
e. The reader process will automatically create the new tcache files

STATUS:

This issue will be addressed in future release after 7.5.2.24 and 7.6.0

.

The tcache file keeps a list of open transactions. When reader sees an open transaction it writes to the tcache file, later it removes from this file when there is a commit.