Re: Speed up Clog Access by increasing CLOG buffers

On Mon, Sep 19, 2016 at 2:41 AM, Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> But now that I look at the first post, I see it apparently used a plain
> tpc-b pgbench (with synchronous_commit=on) to show the benefits, which is
> the workload I'm running right now (results sometime tomorrow).

Good option, We can test plain TPC-B also..

I have some more results.. I have got the result for "Update with no
savepoint"....

below is my script...

\set aid random (1,30000000)
\set tid random (1,3000)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

Results: (median of three, 10 minutes run).

Clients Head GroupLock
16 21452 21589
32 42422 42688
64 42460 52590 ~ 23%
128 22683 56825 ~150%
256 18748 54867

With this workload I observed that gain is bigger than my previous
workload (select for update with 2 SP)..

Just to confirm that the gain what we are seeing is because of Clog
Lock contention removal or it's
something else, I ran 128 client with perf for 5 minutes and below is my result.

I can see that after applying group lock patch, LWLockAcquire become
28% to just 4%, and all because
of Clog Lock.

On Head:
------------
- 28.45% 0.24% postgres postgres [.] LWLockAcquire
- LWLockAcquire
+ 53.49% TransactionIdSetPageStatus
+ 40.83% SimpleLruReadPage_ReadOnly
+ 1.16% BufferAlloc
+ 0.92% GetSnapshotData
+ 0.89% GetNewTransactionId
+ 0.72% LockBuffer
+ 0.70% ProcArrayGroupClearXid

After Group Lock Patch:
-------------------------------
- 4.47% 0.26% postgres postgres [.] LWLockAcquire
- LWLockAcquire
+ 27.11% GetSnapshotData
+ 21.57% GetNewTransactionId
+ 11.44% SimpleLruReadPage_ReadOnly
+ 10.13% BufferAlloc
+ 7.24% ProcArrayGroupClearXid
+ 4.74% LockBuffer
+ 4.08% LockAcquireExtended
+ 2.91% TransactionGroupUpdateXidStatus
+ 2.71% LockReleaseAll
+ 1.90% WALInsertLockAcquire
+ 0.94% LockRelease
+ 0.91% VirtualXactLockTableInsert
+ 0.90% VirtualXactLockTableCleanup
+ 0.72% MultiXactIdSetOldestMember
+ 0.66% LockRefindAndRelease

Next I will test, "update with 2 savepoints", "select for update with
no savepoints"....
I will also test the granular lock and atomic lock patch in next run..

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