Re: 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