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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: Kevin Grittner <kgrittn(at)gmail(dot)com>, Kevin Grittner <kgrittn(at)postgresql(dot)org>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, "pgsql-committers(at)postgresql(dot)org" <pgsql-committers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Re: pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold <
Date: 2016-04-13 17:25:14
Message-ID: CA+Tgmoako8yQh5HE8qK04_oCBkY3C-AncPkSjaVJvWoOuSqzuA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers pgsql-hackers

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

In response to

Responses

Browse pgsql-committers by date

  From Date Subject
Next Message Andres Freund 2016-04-13 17:35:44 Re: [HACKERS] Re: pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold <
Previous Message Andres Freund 2016-04-13 17:19:55 Re: Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold <

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2016-04-13 17:27:49 Re: SET ROLE and reserved roles
Previous Message reiner peterke 2016-04-13 17:25:02 Re: Problems with huge_pages and IBM Power8