From: Andres Freund <andres@2ndquadrant.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: foreign key locks
Date: Fri, 16 Nov 2012 15:55:50 +0100
Message-ID: <20121116145550.GC6505@awork2.anarazel.de> (raw)
In-Reply-To: <20121114162726.GE5161@alvh.no-ip.org>
References: <4FEAB90A0200002500048B7D@gw.wicourts.gov>
<1346389151-sup-8379@alvh.no-ip.org>
<20121018195820.GO3763@alvh.no-ip.org>
<201210270006.54728.andres@2ndquadrant.com>
<20121114152347.GA5161@alvh.no-ip.org>
<20121114162726.GE5161@alvh.no-ip.org>
On 2012-11-14 13:27:26 -0300, Alvaro Herrera wrote:
> Alvaro Herrera wrote:
>
> > > * In heap_lock_tuple's XMAX_IS_MULTI case
> > >
> > > [snip]
> > >
> > > why is it membermode > mode and not membermode >= mode?
> >
> > Uh, that's a bug. Fixed. As noticed in the comment above that snippet,
> > there was a deadlock possible here. Maybe I should add a test to ensure
> > this doesn't happen.
>
> Done:
> https://github.com/alvherre/postgres/commit/df2847e38198e99f57e52490e1e9391ebb70d770
>
> (I don't think this is worth a v24 submission).
I have started doing some performance testing and I fear I was right in
being suspicious about the performance difference for FOR SHARE locks:
Tested with
pgbench -p 5442 -U andres \
-c 30 -j 30 \
-M prepared -f ~/tmp/postgres-fklocks/select-for-share.sql \
-T 20 postgres
on a pgbench -i -s 10 database, where select-for-share.sql is:
BEGIN;
\set naccounts 1000000
\setrandom aid 1 :naccounts
SELECT abalance FROM pgbench_accounts WHERE aid = :aid FOR SHARE;
\setrandom aid 1 :naccounts
SELECT abalance FROM pgbench_accounts WHERE aid = :aid FOR SHARE;
\setrandom aid 1 :naccounts
SELECT abalance FROM pgbench_accounts WHERE aid = :aid FOR SHARE;
COMMIT;
which very roughly resembles workloads I have seen in reality (locking
some records your rely on while you do some work).
With
52b4729fcfc20f056f17531a6670d8c4b9696c39 (alvherre/fklocks)
vs
273986bf0d39e5166eb15ba42ebff4803e23a688 (latest merged master)
I get
tps = 8986.133496 (excluding connections establishing)
vs
tps = 25307.861193 (excluding connections establishing)
Thats nearly a factor of three which seems to be too big to be
acceptable to me.
So I really think we need to bring FOR SHARE locks back as a flag.
I have done some benchmarking of other cases (plain pgbench, pgbench
with foreign keys, large insertions, large amounts of FOR SHARE locks)
and haven't found anything really outstanding so far.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andres@2ndquadrant.com, alvherre@2ndquadrant.com
Subject: Re: foreign key locks
In-Reply-To: <20121116145550.GC6505@awork2.anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox