Re: [HACKERS] Concurrent ALTER SEQUENCE RESTART Regression

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