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

reducing the overhead of frequent table locks - now, with WIP patch

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: reducing the overhead of frequent table locks - now, with WIP patch
Date: 2011-06-03 13:17:08
Message-ID: BANLkTimVboKxzGS9BhL7XphBCr4iy-s0BQ@mail.gmail.com (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-hackers
I've now spent enough time working on this issue now to be convinced
that the approach has merit, if we can work out the kinks.  I'll start
with some performance numbers.

The case where the current system for taking table locks is really
hurting us is where we have a large number of backends attempting to
access a small number of relations.  They all fight over the lock
manager lock on whichever partition (or partitions) that relation (or
those relations) fall in.  Increasing the number of partitions doesn't
help, because they are all trying to access the same object, and that
object is only ever going to be in one partition.  To exercise this
case, I chose the following benchmark: pgbench -n -S -T 300 -c 36 -j
36.  I first tested this on my MacBook Pro, with scale factor 10 and
shared_buffers=400MB.  Here are the results of alternating runs
without and with the patch:

tps = 23997.120971 (including connections establishing)
tps = 25003.186860 (including connections establishing)
tps = 23499.257892 (including connections establishing)
tps = 24435.793773 (including connections establishing)
tps = 23579.624360 (including connections establishing)
tps = 24791.974810 (including connections establishing)

As you can see, this works out to a bit more than a 4% improvement on
this two-core box.  I also got access (thanks to Nate Boley) to a
24-core box and ran the same test with scale factor 100 and
shared_buffers=8GB.  Here are the results of alternating runs without
and with the patch on that machine:

tps = 36291.996228 (including connections establishing)
tps = 129242.054578 (including connections establishing)
tps = 36704.393055 (including connections establishing)
tps = 128998.648106 (including connections establishing)
tps = 36531.208898 (including connections establishing)
tps = 131341.367344 (including connections establishing)

That's an improvement of about ~3.5x.  According to the vmstat output,
when running without the patch, the CPU state was about 40% idle.
With the patch, it dropped down to around 6%.

There are numerous problems with the code as it stands at this point.
It crashes if you try to use 2PC, which means the regression tests
fail; it probably does horrible things if you run out of shared
memory; pg_locks knows nothing about the new mechanism (arguably, we
could leave it that way: only locks that can't possibly be conflicting
with anything can be taken using this mechanism, but it would be nice
to fix, I think); and there are likely some other gotchas as well.
Still, the basic mechanism appears to work.

The code is attached, for anyone who may be curious.  Known idiocies
are marked with "ZZZ".  The design was discussed on the previous
thread ("reducing the overhead of frequent table locks"), q.v.  There
are some comments in the patch as well, but more is likely needed.

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

Attachment: fastlock-v1.patch
Description: application/octet-stream (43.1 KB)

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-06-03 13:34:03
Subject: Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
Previous:From: Andrew DunstanDate: 2011-06-03 12:42:52
Subject: Re: pg_upgrade automatic testing

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