I've been playing with the attached patch, which adds an additional
light-weight lock mode, LW_SHARED2. LW_SHARED2 conflicts with
LW_SHARED and LW_EXCLUSIVE, but not with itself. The patch changes
ProcArrayEndTransaction() to use this new mode. IOW, multiple
processes can commit at the same time, and multiple processes can take
snapshots at the same time, but nobody can take a snapshot while
someone else is committing.
Needless to say, I don't we'd really want to apply this, because
adding a LW_SHARED2 mode that's probably only useful for ProcArrayLock
would be a pretty ugly wart. But the results are interesting.
pgbench, scale factor 100, unlogged tables, Nate Boley's 32-core AMD
box, 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, results are median of three five-minute runs:
#clients tps(master) tps(lwshared2)
1 657.984859 683.251582
8 4748.906750 4946.069238
32 10695.160555 17530.390578
80 7727.563437 16099.549506
That's a pretty impressive speedup, but there's trouble in paradise.
With 80 clients (but not 32 or fewer), I occasionally get the
following error:
ERROR: t_xmin is uncommitted in tuple to be updated
So it seems that there's some way in which this locking is actually
incorrect, though I'm not seeing what it is at the moment. Either
that, or there's some bug in the existing code that happens to be
exposed by this change.
The patch also produces a (much smaller) speedup with regular tables,
but it's hard to know how seriously to take that until the locking
issue is debugged.
Any ideas?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company