Re: postgres 9.3 vs. 9.4

----- Original Message -----
> From: "Mark Kirkwood" <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
> To: "Tigran Mkrtchyan" <tigran(dot)mkrtchyan(at)desy(dot)de>
> Cc: "Merlin Moncure" <mmoncure(at)gmail(dot)com>, "postgres performance list" <pgsql-performance(at)postgresql(dot)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(dot)kirkwood(at)catalyst(dot)net(dot)nz>
> >>> To: "Merlin Moncure" <mmoncure(at)gmail(dot)com>, "Tigran Mkrtchyan"
> >>> <tigran(dot)mkrtchyan(at)desy(dot)de>
> >>> Cc: "postgres performance list" <pgsql-performance(at)postgresql(dot)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(dot)mkrtchyan(at)desy(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>