Re: Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold <

On 2016-04-12 14:53:57 -0500, Kevin Grittner wrote:
> On Tue, Apr 12, 2016 at 2:28 PM, Andres Freund <andres(at)anarazel(dot)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 where relkind 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