Horton

Re: Speed up Clog Access by increasing CLOG buffers

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Ashutosh Sharma <ashu.coek88@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Michael Paquier <michael.paquier@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Jim Nasby <Jim.Nasby@bluetreble.com>, Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-08-29T21:13:25Z
Lists: pgsql-hackers 
On Tue, Jul 4, 2017 at 12:33 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> I have updated the patch to support wait events and moved it to upcoming CF.

This patch doesn't apply any more, but I made it apply with a hammer
and then did a little benchmarking (scylla, EDB server, Intel Xeon
E5-2695 v3 @ 2.30GHz, 2 sockets, 14 cores/socket, 2 threads/core).
The results were not impressive.  There's basically no clog contention
to remove, so the patch just doesn't really do anything.  For example,
here's a wait event profile with master and using Ashutosh's test
script with 5 savepoints:

      1  Lock            | tuple
      2  IO              | SLRUSync
      5  LWLock          | wal_insert
      5  LWLock          | XidGenLock
      9  IO              | DataFileRead
     12  LWLock          | lock_manager
     16  IO              | SLRURead
     20  LWLock          | CLogControlLock
     97  LWLock          | buffer_content
    216  Lock            | transactionid
    237  LWLock          | ProcArrayLock
   1238  IPC             | ProcArrayGroupUpdate
   2266  Client          | ClientRead

This is just a 5-minute test; maybe things would change if we ran it
for longer, but if only 0.5% of the samples are blocked on
CLogControlLock without the patch, obviously the patch can't help
much.  I did some other experiments too, but I won't bother
summarizing the results here because they're basically boring.  I
guess I should have used a bigger machine.

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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.