Re: Speed up Clog Access by increasing CLOG buffers

I think the main focus for test in this area would be at higher client

count.  At what scale factors have you taken the data and what are

the other non-default settings you have used.  By the way, have you

tried by dropping and recreating the database and restarting the server

after each run, can you share the exact steps you have used to perform

the tests.  I am not sure why it is not showing the benefit in your testing,

may be the benefit is on some what more higher end m/c or it could be

that some of the settings used for test are not same as mine or the way

to test the read-write workload of pgbench is different.

In anycase, I went ahead and tried further reducing the CLogControlLock

contention by grouping the transaction status updates.  The basic idea

is same as is used to reduce the ProcArrayLock contention [1] which is to

allow one of the proc to become leader and update the transaction status for

other active transactions in system.  This has helped to reduce the contention

around CLOGControlLock.  Attached patch group_update_clog_v1.patch

implements this idea.

Below is the LWLock_Stats information with and without patch:

Stats Data
---------
A. scale_factor = 300; shared_buffers=32GB; client_connections - 128

HEAD - 5c90a2ff
----------------
CLogControlLock Data
------------------------
PID 94100 lwlock main 11: shacq 678672 exacq 326477 blk 204427 spindelay 8532 dequeue self 93192
PID 94129 lwlock main 11: shacq 757047 exacq 363176 blk 207840 spindelay 8866 dequeue self 96601
PID 94115 lwlock main 11: shacq 721632 exacq 345967 blk 207665 spindelay 8595 dequeue self 96185
PID 94011 lwlock main 11: shacq 501900 exacq 241346 blk 173295 spindelay 7882 dequeue self 78134
PID 94087 lwlock main 11: shacq 653701 exacq 314311 blk 201733 spindelay 8419 dequeue self 92190

After Patch group_update_clog_v1
----------------
CLogControlLock Data
------------------------
PID 100205 lwlock main 11: shacq 836897 exacq 176007 blk 116328 spindelay 1206 dequeue self 54485
PID 100034 lwlock main 11: shacq 437610 exacq 91419 blk 77523 spindelay 994 dequeue self 35419
PID 100175 lwlock main 11: shacq 748948 exacq 158970 blk 114027 spindelay 1277 dequeue self 53486
PID 100162 lwlock main 11: shacq 717262 exacq 152807 blk 115268 spindelay 1227 dequeue self 51643
PID 100214 lwlock main 11: shacq 856044 exacq 180422 blk 113695 spindelay 1202 dequeue self 54435

The above data indicates that contention due to CLogControlLock is

reduced by around 50% with this patch.

The reasons for remaining contention could be:

1. Readers of clog data (checking transaction status data) can take

Exclusive CLOGControlLock when reading the page from disk, this can

contend with other Readers (shared lockers of CLogControlLock) and with
exclusive locker which updates transaction status. One of the ways to

mitigate this contention is to increase the number of CLOG buffers for which

patch has been already posted on this thread.

2. Readers of clog data (checking transaction status data) takes shared

CLOGControlLock which can contend with exclusive locker (Group leader) which

updates transaction status.  I have tried to reduce the amount of work done

by group leader, by allowing group leader to just read the Clog page once

for all the transactions in the group which updated the same CLOG page

(idea similar to what we currently we use for updating the status of transactions

having sub-transaction tree), but that hasn't given any further performance boost,

so I left it.

I think we can use some other ways as well to reduce the contention around

CLOGControlLock by doing somewhat major surgery around SLRU like using

buffer pools similar to shared buffers, but this idea gives us moderate

improvement without much impact on exiting mechanism.

Thoughts?