After reducing ProcArrayLock contention in commit (0e141c0fbb211bdd23783afa731e3eef95c9ad7a), the other lock which seems to be contentious in read-write transactions is CLogControlLock. In my investigation, I found that the contention is mainly due to two reasons, one is that while writing the transaction status in CLOG (TransactionIdSetPageStatus()), it acquires EXCLUSIVE CLogControlLock which contends with every other transaction which tries to access the CLOG for checking transaction status and to reduce it already a patch [1] is proposed by Simon; Second contention is due to the reason that when the CLOG page is not found in CLOG buffers, it needs to acquire CLogControlLock in Exclusive mode which again contends with shared lockers which tries to access the transaction status.
Increasing CLOG buffers to 64 helps in reducing the contention due to second reason. Experiments revealed that increasing CLOG buffers only helps once the contention around ProcArrayLock is reduced. Performance Data ----------------------------- RAM - 500GB 8 sockets, 64 cores(Hyperthreaded128 threads total) Non-default parameters ------------------------------------ max_connections = 300 shared_buffers=8GB min_wal_size=10GB max_wal_size=15GB checkpoint_timeout =35min maintenance_work_mem = 1GB checkpoint_completion_target = 0.9 wal_buffers = 256MB pgbench setup ------------------------ scale factor - 300 Data is on magnetic disk and WAL on ssd. pgbench -M prepared tpc-b HEAD - commit 0e141c0f Patch-1 - increase_clog_bufs_v1 Client Count/Patch_ver 1 8 16 32 64 128 256 HEAD 911 5695 9886 18028 27851 28654 25714 Patch-1 954 5568 9898 18450 29313 31108 28213 This data shows that there is an increase of ~5% at 64 client-count and 8~10% at more higher clients without degradation at lower client- count. In above data, there is some fluctuation seen at 8-client-count, but I attribute that to run-to-run variation, however if anybody has doubts I can again re-verify the data at lower client counts. Now if we try to further increase the number of CLOG buffers to 128, no improvement is seen. I have also verified that this improvement can be seen only after the contention around ProcArrayLock is reduced. Below is the data with Commit before the ProcArrayLock reduction patch. Setup and test is same as mentioned for previous test. HEAD - commit 253de7e1 Patch-1 - increase_clog_bufs_v1 Client Count/Patch_ver 128 256 HEAD 16657 10512 Patch-1 16694 10477 I think the benefit of this patch would be more significant along with the other patch to reduce CLogControlLock contention [1] (I have not tested both the patches together as still there are few issues left in the other patch), however it has it's own independent value, so can be considered separately. Thoughts? [1] - http://www.postgresql.org/message-id/canp8+j+imqfhxkchfyfnxdyi6k-arazrv+zg-v_ofxetjjo...@mail.gmail.com With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
increase_clog_bufs_v1.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers