Speed up Clog Access by increasing CLOG buffers
| От | Amit Kapila |
|---|---|
| Тема | Speed up Clog Access by increasing CLOG buffers |
| Дата | |
| Msg-id | CAA4eK1+8=X9mSNeVeHg_NqMsOR-XKsjuqrYzQf=iCsdh3U4EOA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Speed up Clog Access by increasing CLOG buffers
(Andres Freund <andres@anarazel.de>)
Re: Speed up Clog Access by increasing CLOG buffers (Peter Geoghegan <pg@heroku.com>) |
| Список | pgsql-hackers |
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?
Вложения
В списке pgsql-hackers по дате отправления: