Re: Speed up Clog Access by increasing CLOG buffers

On Mon, Sep 19, 2016 at 2:41 AM, Tomas Vondra
<tomas.vondra@2ndquadrant.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