There are lots of messages in the queue. Most operations are tiny transaction of inserts. Mutilthread poster is slow and process inserts 100+ operation/per second. There are proper indexes on tables and SP_QUE_POST_SHMSIZE 134217728
Name: call_q (o.N2PB-o.N2PB) (MTPost queue)
Number of messages: 1040708 (Age 200 min; Size 1148 mb)
Backlog (messages): 953343 (Age 142 min)
SP_QUE_SYNC =1 will slow poster performance significantly because data must be written to disk three times and also affects system performance. Unless you must set it because you have no other fault-tolerance protection.
1. Change SP_QUE_SYNC back to 0, which is the default:
sp_ctrl> set param SP_QUE_SYNC 0
2. Shutdown Shareplex and restart Shareplex.
If post is still slow and there are concurrent inserts on one table, you may want to increase # of freelists on this table and its indexes. If there are many concurrent updates on one table, you may want to increase init_trans on table and its indexes. You can also increase SP_QUE_POST_SHMSIZE if there is enough memory on the server. This parameter setting is for each post queue.
Starting with SharePlex 8.6, the unit of the following parameters have changed from bytes to megabytes (MB). Hence one may want to change all references to the parameters from bytes to megabytes. Here are the latest description of the parameters:
SP_QUE_POST_SHMSIZE
This parameter determines the specific shared-memory size for the post queue.
IMPORTANT: Increasing this parameter might require an increase in the maximum shared memory segment size
in your system settings.
Default: 32 megabytes
Range of valid values: integer greater than 0
Takes effect: When sp_cop is restarted
SP_QUE_Q_SHMSIZE
This parameter determines the specific shared-memory size for the capture and export queues.
Default: 8 megabytes
Range of valid values: integer greater than 0, no maximum
Takes effect: When sp_cop is restarted
The parameter SP_QUE_POST_SHMSIZE can now be set globally as well as at a named Post queue level. This is how it is set at a queue level:
sp_ctrl>set param SP_QUE_POST_SHMSIZE queue <post_queue_name> <value>
Then bounce the Post queue in question.
Post timing debug shows shareplex is spending 30% of time on queues.
