Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

On Wed, Aug 30, 2017 at 12:54 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> That would have been better. In any case, will do the tests on some
> higher end machine and will share the results.
>
>> Given that we've changed the approach here somewhat, I think we need
>> to validate that we're still seeing a substantial reduction in
>> CLogControlLock contention on big machines.
>>
>
> Sure will do so.  In the meantime, I have rebased the patch.

I have repeated some of the tests we have performed earlier.

Machine:
Intel 8 socket machine with 128 core.

Configuration:

shared_buffers=8GB
checkpoint_timeout=40min
max_wal_size=20GB
max_connections=300
maintenance_work_mem=4GB
synchronous_commit=off
checkpoint_completion_target=0.9

I have run taken one reading for each test to measure the wait event.
Observation is same that at higher client count there is a significant
reduction in the contention on ClogControlLock.

Benchmark:  Pgbench simple_update, 30 mins run:

Head: (64 client) : (TPS 60720)
53808  Client          | ClientRead 26147  IPC             | ProcArrayGroupUpdate  7866  LWLock          |
CLogControlLock 3705  Activity        | LogicalLauncherMain  3699  Activity        | AutoVacuumMain  3353  LWLock
  | ProcArrayLoc  3099  LWLock          | wal_insert  2825  Activity        | BgWriterMain  2688  Lock            |
extend 1436  Activity        | WalWriterMain
 

Patch: (64 client) : (TPS 67207)53235  Client          | ClientRead 29470  IPC             | ProcArrayGroupUpdate  4302
LWLock          | wal_insert  3717  Activity        | LogicalLauncherMain  3715  Activity        | AutoVacuumMain  3463
LWLock          | ProcArrayLock  3140  Lock            | extend  2934  Activity        | BgWriterMain  1434  Activity
    | WalWriterMain  1198  Activity        | CheckpointerMain  1073  LWLock          | XidGenLock   869  IPC
| ClogGroupUpdate
 

Head:(72 Client): (TPS 57856)
55820  Client          | ClientRead 34318  IPC             | ProcArrayGroupUpdate 15392  LWLock          |
CLogControlLock 3708  Activity        | LogicalLauncherMain  3705  Activity        | AutoVacuumMain  3436  LWLock
  | ProcArrayLock
 

Patch:(72 Client): (TPS 65740)
 60356  Client          | ClientRead 38545  IPC             | ProcArrayGroupUpdate  4573  LWLock          | wal_insert
3708 Activity        | LogicalLauncherMain  3705  Activity        | AutoVacuumMain  3508  LWLock          |
ProcArrayLock 3492  Lock            | extend  2903  Activity        | BgWriterMain  1903  LWLock          | XidGenLock
1383 Activity        | WalWriterMain  1212  Activity        | CheckpointerMain  1056  IPC             |
ClogGroupUpdate


Head:(96 Client): (TPS 52170)
 62841  LWLock          | CLogControlLock 56150  IPC             | ProcArrayGroupUpdate 54761  Client          |
ClientRead 7037  LWLock          | wal_insert  4077  Lock            | extend  3727  Activity        |
LogicalLauncherMain 3727  Activity        | AutoVacuumMain  3027  LWLock          | ProcArrayLock
 

Patch:(96 Client): (TPS 67932)
 87378  IPC             | ProcArrayGroupUpdate 80201  Client          | ClientRead 11511  LWLock          | wal_insert
4102 Lock            | extend  3971  LWLock          | ProcArrayLock  3731  Activity        | LogicalLauncherMain  3731
Activity        | AutoVacuumMain  2948  Activity        | BgWriterMain  1763  LWLock          | XidGenLock  1736  IPC
         | ClogGroupUpdate
 

Head:(128 Client): (TPS 40820)
182569  LWLock          | CLogControlLock 61484  IPC             | ProcArrayGroupUpdate 37969  Client          |
ClientRead 5135  LWLock          | wal_insert  3699  Activity        | LogicalLauncherMain  3699  Activity        |
AutoVacuumMain

Patch:(128 Client): (TPS 67054)
174583  IPC             | ProcArrayGroupUpdate 66084  Client          | ClientRead 16738  LWLock          | wal_insert
4993 IPC             | ClogGroupUpdate  4893  LWLock          | ProcArrayLock  4839  Lock            | extend
 

Benchmark: select for update with 3 save points, 10 mins run

Script:
\set aid random (1,30000000)
\set tid random (1,3000)

BEGIN;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid for UPDATE;
SAVEPOINT s1;
SELECT tbalance FROM pgbench_tellers WHERE tid = :tid for UPDATE;
SAVEPOINT s2;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid for UPDATE;
SAVEPOINT s3;
SELECT tbalance FROM pgbench_tellers WHERE tid = :tid for UPDATE;
END;

Head:(64 Client): (TPS 44577.1802)
 53808  Client          | ClientRead 26147  IPC             | ProcArrayGroupUpdate  7866  LWLock          |
CLogControlLock 3705  Activity        | LogicalLauncherMain  3699  Activity        | AutoVacuumMain  3353  LWLock
  | ProcArrayLock  3099  LWLock          | wal_insert
 

Patch:(64 Client): (TPS 46156.245)
53235  Client          | ClientRead 29470  IPC             | ProcArrayGroupUpdate  4302  LWLock          | wal_insert
3717 Activity        | LogicalLauncherMain  3715  Activity        | AutoVacuumMain  3463  LWLock          |
ProcArrayLock 3140  Lock            | extend  2934  Activity        | BgWriterMain  1434  Activity        |
WalWriterMain 1198  Activity        | CheckpointerMain  1073  LWLock          | XidGenLock   869  IPC             |
ClogGroupUpdate


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