From: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Petr Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org, Noah Misch <noah(at)leadboat(dot)com>, Jason Petersen <jason(at)citusdata(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] Concurrent ALTER SEQUENCE RESTART Regression |
Date: | 2017-05-11 20:27:48 |
Message-ID: | 90255529-1de1-0aad-c545-f75d08479a9a@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
On 5/10/17 12:24, Andres Freund wrote:
> Upthread I theorized whether
> that's actually still meaningful given fastpath locking and such, but I
> guess we'll have to evaluate that.
I did some testing.
I ran this script
CREATE SEQUENCE seq1;
DO LANGUAGE plpythonu $$
plan = plpy.prepare("SELECT nextval('seq1')")
for i in range(0, 10000000):
plpy.execute(plan)
$$;
and timed the "DO".
I compared 9.1 (pre fast-path locking) with 9.2 (with fast-path locking).
I compared the stock releases with a patched version that replaces the
body of open_share_lock() with just
return relation_open(seq->relid, AccessShareLock);
First, a single session:
9.1 unpatched
Time: 57634.098 ms
9.1 patched
Time: 58674.655 ms
(These were within each other's variance over several runs.)
9.2 unpatched
Time: 64868.305 ms
9.2 patched
Time: 60585.317 ms
(So without contention fast-path locking beats the extra dance that
open_share_lock() does.)
Then, with four concurrent sessions (one timed, three just running):
9.1 unpatched
Time: 73123.661 ms
9.1 patched
Time: 78019.079 ms
So the "hack" avoids the slowdown from contention here.
9.2 unpatched
Time: 73308.873 ms
9.2 patched
Time: 70353.971 ms
Here, fast-path locking beats out the "hack".
If I add more concurrent sessions, everything gets much slower but the
advantage of the patched version stays about the same. But I can't
reliably test that on this hardware.
(One wonders whether these differences remain relevant if this is run
within the context of an INSERT or COPY instead of just a tight loop.)
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2017-05-11 20:34:33 | Re: [BUGS] Concurrent ALTER SEQUENCE RESTART Regression |
Previous Message | Peter Eisentraut | 2017-05-11 15:35:22 | Re: [HACKERS] Concurrent ALTER SEQUENCE RESTART Regression |
From | Date | Subject | |
---|---|---|---|
Next Message | Konstantin Knizhnik | 2017-05-11 20:30:46 | Re: Cached plans and statement generalization |
Previous Message | Andres Freund | 2017-05-11 19:52:26 | Re: Cached plans and statement generalization |