Re: [HACKERS] Re: pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold < - Mailing list pgsql-committers

On 2016-04-12 14:53:57 -0500, Kevin Grittner wrote:
> On Tue, Apr 12, 2016 at 2:28 PM, Andres Freund <andres@anarazel.de> wrote:
> > On 2016-04-12 14:17:12 -0500, Kevin Grittner wrote:
> >> Well, something is different between your environment and mine,
> >> since I saw no difference at scale 100 and 2.2% at scale 200.
> >
> > In a readonly test or r/w?
>
> Readonly with client and job counts matching scale.
>
> > A lot of this will be different between
> > single-socket and multi-socket servers; as soon as you have the latter
> > the likelihood of contention being bad goes up dramatically.
>
> Yeah, I know, and 4 socket has been at least an order of magnitude
> more problematic in my experience than 2 socket.  And the problems
> are far, far, far worse on kernels prior to 3.8, especially on 3.x
> before 3.8, so it's hard to know how to take any report of problems
> on a 4 node NUMA machine without knowing the kernel version.

On an EC2 m4.10xlarge (dedicated, but still a VM) - sorry I don't have
anything better at hand right now, and it was already running.

postgres config:
postgres -D /srv/data/dev/
         -c shared_buffers=64GB \
         -c max_wal_size=64GB \
         -c maintenance_work_mem=32GB \
         -c huge_pages=on \
         -c max_connections=400 \
         -c logging_collector=on -c log_filename='postgresql.log' \
         -c log_checkpoints=on -c autovacuum=off \
         -c autovacuum_freeze_max_age=80000000 \
         -c synchronous_commit=off

Initialized with pgbench -q -i -s 300

Before each run I prewarmed with
psql -c "create extension if not exists pg_prewarm;select sum(x.x) from (select pg_prewarm(oid) as x from pg_class
whererelkind in ('i', 'r') order by oid) x;" > /dev/null 2>&1; 

running pgbench -M prepared -c 128 -j 128 -n -P 1 -T 100 -S

With -c old_snapshot_threshold=0:

latency average = 0.218 ms
latency stddev = 0.154 ms
tps = 584666.289753 (including connections establishing)
tps = 584867.785569 (excluding connections establishing)


With -c old_snapshot_threshold=10:

latency average = 1.112 ms
latency stddev = 1.246 ms
tps = 114883.528964 (including connections establishing)
tps = 114905.555943 (excluding connections establishing)


With 848ef42bb8c7909c9d7baa38178d4a209906e7c1 (and followups) reverted:
latency average = 0.210 ms
latency stddev = 0.050 ms
tps = 607734.407158 (including connections establishing)
tps = 607918.118566 (excluding connections establishing)


A quicker (each -T 10) test, without restarts between scale reuns, of
other scales:

scale     thres=0            thresh=10
1      15377.761645       15017.789751
1         16285.111754       14829.493870
2         29563.478651       28790.462964
4         62649.628931       50935.364141
8         84557.464387       85631.348766
16        101475.002295      93908.910894
32        347435.607586      167702.527893
64        575640.880911      150139.375351
128       594782.154256      112183.933956
196       584290.957806      92080.129402
256       583921.995839      79345.378887
398       582138.372414      58100.798609


- Andres