Re: postgres 9.3 vs. 9.4 - Mailing list pgsql-performance

----- Original Message -----
> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de>
> Cc: "Merlin Moncure" <mmoncure@gmail.com>, "postgres performance list" <pgsql-performance@postgresql.org>
> Sent: Friday, September 19, 2014 12:49:05 AM
> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
>
> On 19/09/14 10:16, Mark Kirkwood wrote:
> > On 19/09/14 09:10, Mkrtchyan, Tigran wrote:
> >>
> >>
> >> ----- Original Message -----
> >>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> >>> To: "Merlin Moncure" <mmoncure@gmail.com>, "Tigran Mkrtchyan"
> >>> <tigran.mkrtchyan@desy.de>
> >>> Cc: "postgres performance list" <pgsql-performance@postgresql.org>
> >>> Sent: Thursday, September 18, 2014 10:56:36 PM
> >>> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
> >>>
> >>> On 19/09/14 08:32, Merlin Moncure wrote:
> >>>> On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran
> >>>> <tigran.mkrtchyan@desy.de> wrote:
> >>>>>
> >>>>> 9.3.5:
> >>>>>           0.035940        END;
> >>>>>
> >>>>>
> >>>>> 9.4beta2:
> >>>>>           0.957854        END;
> >>>>
> >>>>
> >>>> time being spent on 'END' is definitely suggesting i/o related issues.
> >>>> This is making me very skeptical that postgres is the source of the
> >>>> problem.   I also thing synchronous_commit is not set properly on the
> >>>> new instance (or possibly there is a bug or some such).  Can you
> >>>> verify via:
> >>>>
> >>>> select * from pg_settings where name = 'synchronous_commit';
> >>>>
> >>>> on both servers?
> >>>>
> >>>
> >>> Yes, does look suspicious. It *could* be that the 9.4 case is getting
> >>> unlucky and checkpointing just before the end of the 60s run, and 9.3
> >>> isn't.
> >>
> >> 10 minutes run had the same results.
> >>
> >> Is there some kind of statistics which can tell there time is spend?
> >> Or the only way is to run on solaris with dtrace? For me it's more
> >> important
> >> to find why I get only 1500tps with 9.3. The test with 9.4 was just a
> >> hope for
> >> a magic code change that will give me a better performance.
> >>
> >>
> >
> > Interesting. With respect to dtrace, you can use systemtap on Linux to
> > achieve similar things.
> >
> > However before getting too carried away with that - we already *know*
> > that 9.4 is spending longer in END (i.e commit) than 9.3 is. I'd
> > recommend you see what wal_sync_method is set to on both systems. If it
> > is the same, then my suspicion is that one of the SSD's needs to be
> > trimmed [1]. You can do this by running:
> >
> > $ fstrim /mountpoint
> >
> > Also - are you using the same filesystem and mount options on each SSD?
> >
> > Cheers
> >
> > Mark
> >
> > [1] if fact, for the paranoid - I usually secure erase any SSD before
> > performance testing, and then check the SMART counters too...
> >
>
> Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3
> one for 9.4), see below for results.
>
> I'm running xfs on them with trim/discard enabled:
>
> $ mount|grep pg
> /dev/sdd4 on /mnt/pg94 type xfs (rw,discard)
> /dev/sdc4 on /mnt/pg93 type xfs (rw,discard)
>
>
> I'm *not* seeing any significant difference between 9.3 and 9.4, and the
> numbers are both about 2x your best number, which is food for thought
> (those P320's should toast my M550 for write performance...).

cool! any details on OS and other options? I still get the same numbers
as before.

Tigran.

>
>
> 9.3:
>
> $ pgbench -r -j 1 -c 1 -T 60 bench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> query mode: simple
> number of clients: 1
> number of threads: 1
> duration: 60 s
> number of transactions actually processed: 194615
> tps = 3243.567115 (including connections establishing)
> tps = 3243.771688 (excluding connections establishing)
> statement latencies in milliseconds:
>     0.000798    \set nbranches 1 * :scale
>     0.000302    \set ntellers 10 * :scale
>     0.000276    \set naccounts 100000 * :scale
>     0.000330    \setrandom aid 1 :naccounts
>     0.000265    \setrandom bid 1 :nbranches
>     0.000278    \setrandom tid 1 :ntellers
>     0.000298    \setrandom delta -5000 5000
>     0.012818    BEGIN;
>     0.065403    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE
> aid = :aid;
>     0.048516    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
>     0.058343    UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE
> tid = :tid;
>     0.057763    UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE
> bid = :bid;
>     0.043293    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
> VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
>     0.017087    END;
>
>
> 9.4:
>
> $ pgbench -r -j 1 -c 1 -T 60 bench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> query mode: simple
> number of clients: 1
> number of threads: 1
> duration: 60 s
> number of transactions actually processed: 194130
> latency average: 0.309 ms
> tps = 3235.488190 (including connections establishing)
> tps = 3235.560235 (excluding connections establishing)
> statement latencies in milliseconds:
>     0.000460    \set nbranches 1 * :scale
>     0.000231    \set ntellers 10 * :scale
>     0.000224    \set naccounts 100000 * :scale
>     0.000258    \setrandom aid 1 :naccounts
>     0.000252    \setrandom bid 1 :nbranches
>     0.000266    \setrandom tid 1 :ntellers
>     0.000272    \setrandom delta -5000 5000
>     0.011724    BEGIN;
>     0.083750    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE
> aid = :aid;
>     0.045553    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
>     0.054412    UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE
> tid = :tid;
>     0.053371    UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE
> bid = :bid;
>     0.041501    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
> VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
>     0.015273    END;
>
> configuration:
>
> logging_collector = 'on'
> wal_writer_delay = '10s'
> vacuum_cost_delay = 50
> synchronous_commit = 'off'
> wal_buffers = '16MB'
> checkpoint_segments = 64
> shared_buffers = '2GB'
> max_connections = 100
> random_page_cost = 1.5
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>