Skip site navigation (1) Skip section navigation (2)

clog buffers (and FlexLocks)

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: clog buffers (and FlexLocks)
Date: 2011-12-02 22:20:05
Message-ID: CA+TgmoazLLicsCv4a+G1QURmEx5=gbWLUByPyAtu9zZ6o5NK0w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Heikki tipped me off to a possible problem with CLOG contention that
he noticed while doing some benchmarking, and so I (you know what's
coming, right?) tried to reproduce it on Nate Boley's 32-core AMD
machine.  It turns out that increasing NUM_CLOG_BUFFERS from 8 to 32
delivers a significant performance boost on this server at higher
concurrency levels.  Although there is some possible effect even at 8
clients, it's much more pronounced at 16+ clients.  I believe the root
of the problem is the case where SimpleLruReadPage_ReadOnly fails to
find the necessary page in a buffer and therefore needs to drop the
shared lock, acquire the exclusive lock, and pull the page in.  More
testing is probably necessary, but ISTM that if you have many more
CPUs than CLOG buffers, you could end up in a situation where the
number of people waiting for a CLOG buffer is greater than the number
of buffers.  On these higher velocity tests, where you have >10k tps,
you burn through a CLOG page every few seconds, so it's easy to
imagine that when you go to examine a row updated earlier in the test,
the relevant CLOG page is no longer resident.

Another point here is that the flexlock patch on latest sources seems
to be *reducing* performance on permanent tables and increasing it on
unlogged tables, which seems quite bizarre.  I'll see if I can look
into what's going on there.

Obligatory benchmark details:  m = master, c = master
w/NUM_CLOG_BUFFERS = 32, f = FlexLocks, b = FlexLocks
w/NUM_CLOG_BUFFERS = 32; number following the letter is the client
count.  scale factor 100, median of three five-minute pgbench rules,
shared_buffers = 8GB, maintenance_work_mem = 1GB, synchronous_commit =
off, checkpoint_segments = 300, checkpoint_timeout = 15min,
checkpoint_completion_target = 0.9, wal_writer_delay = 20ms.

Permanent Tables

m01 tps = 629.407759 (including connections establishing)
c01 tps = 624.163365 (including connections establishing)
f01 tps = 588.819568 (including connections establishing)
b01 tps = 622.116258 (including connections establishing)
m08 tps = 4199.008538 (including connections establishing)
c08 tps = 4325.508396 (including connections establishing)
f08 tps = 4154.397798 (including connections establishing)
b08 tps = 4442.209823 (including connections establishing)
m16 tps = 8011.430159 (including connections establishing)
c16 tps = 8424.109412 (including connections establishing)
f16 tps = 7783.139603 (including connections establishing)
b16 tps = 8524.645511 (including connections establishing)
m32 tps = 10025.079556 (including connections establishing)
c32 tps = 11247.358762 (including connections establishing)
f32 tps = 10139.320355 (including connections establishing)
b32 tps = 10942.857215 (including connections establishing)
m80 tps = 11072.246423 (including connections establishing)
c80 tps = 11845.972123 (including connections establishing)
f80 tps = 10525.232951 (including connections establishing)
b80 tps = 11757.289333 (including connections establishing)

Unlogged Tables

m01 tps = 669.939124 (including connections establishing)
c01 tps = 664.763349 (including connections establishing)
f01 tps = 671.539140 (including connections establishing)
b01 tps = 665.448818 (including connections establishing)
m08 tps = 4557.328825 (including connections establishing)
c08 tps = 4590.319949 (including connections establishing)
f08 tps = 4389.771668 (including connections establishing)
b08 tps = 4618.345372 (including connections establishing)
m16 tps = 8474.117765 (including connections establishing)
c16 tps = 9059.450494 (including connections establishing)
f16 tps = 8338.446241 (including connections establishing)
b16 tps = 9114.274464 (including connections establishing)
m32 tps = 16999.301828 (including connections establishing)
c32 tps = 19653.023856 (including connections establishing)
f32 tps = 19431.228726 (including connections establishing)
b32 tps = 24696.075282 (including connections establishing)
m80 tps = 14048.282651 (including connections establishing)

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

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-12-02 22:24:40
Subject: Re: WIP: Join push-down for foreign tables
Previous:From: Andres FreundDate: 2011-12-02 22:09:47
Subject: Re: Command Triggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group