Re: Speed up Clog Access by increasing CLOG buffers

On Mon, Oct 10, 2016 at 2:17 AM, Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> after testing each combination (every ~9 hours). Inspired by Robert's wait
> event post a few days ago, I've added wait event sampling so that we can
> perform similar analysis. (Neat idea!)

I have done wait event test on for head vs group lock patch.
I have used similar script what Robert has mentioned in below thread

https://www.postgresql.org/message-id/CA+Tgmoav9Q5v5ZGT3+wP_1tQjT6TGYXrwrDcTRrWimC+ZY7RRA@mail.gmail.com

Test details and Results:
--------------------------------
Machine, POWER, 4 socket machine (machine details are attached in file.)

30-minute pgbench runs with
configurations,
had max_connections = 200,
shared_buffers = 8GB,
maintenance_work_mem = 4GB,
synchronous_commit =off,
checkpoint_timeout = 15min,
checkpoint_completion_target = 0.9,
log_line_prefix = '%t [%p]
max_wal_size = 40GB,
log_checkpoints =on.

Test1: unlogged table, 192 clients
---------------------------------------------
On Head:
tps = 44898.862257 (including connections establishing)
tps = 44899.761934 (excluding connections establishing)

262092 LWLockNamed | CLogControlLock
224396 |
114510 Lock | transactionid
42908 Client | ClientRead
20610 Lock | tuple
13700 LWLockTranche | buffer_content
3637
2562 LWLockNamed | XidGenLock
2359 LWLockNamed | ProcArrayLock
1037 Lock | extend
948 LWLockTranche | lock_manager
46 LWLockTranche | wal_insert
12 BufferPin | BufferPin
4 LWLockTranche | buffer_mapping

With Patch:

tps = 77846.622956 (including connections establishing)
tps = 77848.234046 (excluding connections establishing)

101832 Lock | transactionid
91358 Client | ClientRead
16691 LWLockNamed | XidGenLock
12467 Lock | tuple
6007 LWLockNamed | CLogControlLock
3640
3531 LWLockNamed | ProcArrayLock
3390 LWLockTranche | lock_manager
2683 Lock | extend
1112 LWLockTranche | buffer_content
72 LWLockTranche | wal_insert
8 LWLockTranche | buffer_mapping
2 LWLockTranche | proc
2 BufferPin | BufferPin

Test2: unlogged table, 96 clients
------------------------------------------
On head:
tps = 58632.065563 (including connections establishing)
tps = 58632.767384 (excluding connections establishing)
77039 LWLockNamed | CLogControlLock
39712 Client | ClientRead
18358 Lock | transactionid
4238 LWLockNamed | XidGenLock
3638
3518 LWLockTranche | buffer_content
2717 LWLockNamed | ProcArrayLock
1410 Lock | tuple
792 Lock | extend
182 LWLockTranche | lock_manager
30 LWLockTranche | wal_insert
3 LWLockTranche | buffer_mapping
1 Tuples only is on.
1 BufferPin | BufferPin

With Patch:
tps = 75204.166640 (including connections establishing)
tps = 75204.922105 (excluding connections establishing)
[dilip(dot)kumar(at)power2 bin]$ cat out_300_96_ul.txt
261917 |
53407 Client | ClientRead
14994 Lock | transactionid
5258 LWLockNamed | XidGenLock
3660
3604 LWLockNamed | ProcArrayLock
2096 LWLockNamed | CLogControlLock
1102 Lock | tuple
823 Lock | extend
481 LWLockTranche | buffer_content
372 LWLockTranche | lock_manager
192 Lock | relation
65 LWLockTranche | wal_insert
6 LWLockTranche | buffer_mapping
1 Tuples only is on.
1 LWLockTranche | proc

Test3: unlogged table, 64 clients
------------------------------------------
On Head:

tps = 66231.203018 (including connections establishing)
tps = 66231.664990 (excluding connections establishing)

43446 Client | ClientRead
6992 LWLockNamed | CLogControlLock
4685 Lock | transactionid
3650
3381 LWLockNamed | ProcArrayLock
810 LWLockNamed | XidGenLock
734 Lock | extend
439 LWLockTranche | buffer_content
247 Lock | tuple
136 LWLockTranche | lock_manager
64 Lock | relation
24 LWLockTranche | wal_insert
2 LWLockTranche | buffer_mapping
1 Tuples only is on.

With Patch:
tps = 67294.042602 (including connections establishing)
tps = 67294.532650 (excluding connections establishing)

28186 Client | ClientRead
3655
1172 LWLockNamed | ProcArrayLock
619 Lock | transactionid
289 LWLockNamed | CLogControlLock
237 Lock | extend
81 LWLockTranche | buffer_content
48 LWLockNamed | XidGenLock
28 LWLockTranche | lock_manager
23 Lock | tuple
6 LWLockTranche | wal_insert

Test4: unlogged table, 32 clients

Head:
tps = 52320.190549 (including connections establishing)
tps = 52320.442694 (excluding connections establishing)

28564 Client | ClientRead
3663
1320 LWLockNamed | ProcArrayLock
742 Lock | transactionid
534 LWLockNamed | CLogControlLock
255 Lock | extend
108 LWLockNamed | XidGenLock
81 LWLockTranche | buffer_content
44 LWLockTranche | lock_manager
29 Lock | tuple
6 LWLockTranche | wal_insert
1 Tuples only is on.
1 LWLockTranche | buffer_mapping

With Patch:
tps = 47505.582315 (including connections establishing)
tps = 47505.773351 (excluding connections establishing)

28186 Client | ClientRead
3655
1172 LWLockNamed | ProcArrayLock
619 Lock | transactionid
289 LWLockNamed | CLogControlLock
237 Lock | extend
81 LWLockTranche | buffer_content
48 LWLockNamed | XidGenLock
28 LWLockTranche | lock_manager
23 Lock | tuple
6 LWLockTranche | wal_insert

I think at higher client count from client count 96 onwards contention
on CLogControlLock is clearly visible and which is completely solved
with group lock patch.

And at lower client count 32,64 contention on CLogControlLock is not
significant hence we can not see any gain with group lock patch.
(though we can see some contention on CLogControlLock is reduced at 64
clients.)

Note: Here I have taken only one set of reading, and at 32 client my
reading shows some regression with group lock patch, which may be run
to run variance (because earlier I never saw this regression, I can
confirm again with multiple runs).

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com