Re: foreign key locks

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