----- Original Message ----- > From: "Mark Kirkwood" <mark.kirkwood@xxxxxxxxxxxxxxx> > To: "Tigran Mkrtchyan" <tigran.mkrtchyan@xxxxxxx> > Cc: "Merlin Moncure" <mmoncure@xxxxxxxxx>, "postgres performance list" <pgsql-performance@xxxxxxxxxxxxxx> > Sent: Friday, September 19, 2014 12:49:05 AM > Subject: Re: 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@xxxxxxxxxxxxxxx> > >>> To: "Merlin Moncure" <mmoncure@xxxxxxxxx>, "Tigran Mkrtchyan" > >>> <tigran.mkrtchyan@xxxxxxx> > >>> Cc: "postgres performance list" <pgsql-performance@xxxxxxxxxxxxxx> > >>> Sent: Thursday, September 18, 2014 10:56:36 PM > >>> Subject: Re: 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@xxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
