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

On Wed, Apr 13, 2016 at 1:19 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> 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)

Yuck. Aside from the fact that performance tanks when the feature is
turned on, it seems that there is a significant effect even with it
turned off.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company