Horton

Re: Speed up Clog Access by increasing CLOG buffers

Dilip Kumar <dilipbalaut@gmail.com>

View thread View on postgr.es
From: Dilip Kumar <dilipbalaut@gmail.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2016-09-19T04:20:49Z
Lists: pgsql-hackers 
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

Commits

  1. Use group updates when setting transaction status in clog.

  2. Improve 64bit atomics support.

  3. Add ProcArrayGroupUpdate wait event.

  4. Make the different Unix-y semaphore implementations ABI-compatible.

  5. Fix broken ALTER INDEX documentation

  6. Code and docs review for commit 3187d6de0e5a9e805b27c48437897e8c39071d45.

  7. Partition the freelist for shared dynahash tables.

  8. Correct StartupSUBTRANS for page wraparound

  9. Make idle backends exit if the postmaster dies.

  10. contrib/sslinfo: add ssl_extension_info SRF

  11. Reduce ProcArrayLock contention by removing backends in batches.

  12. Fix `make installcheck` for serializable transactions.

  13. Lockless StrategyGetBuffer clock sweep hot path.

  14. Reduce sinval synchronization overhead.