Date: Fri, 19 Sep 2014 07:53:38 +0200 (CEST)
From: "Mkrtchyan, Tigran" <tigran.mkrtchyan@desy.de>
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: Merlin Moncure <mmoncure@gmail.com>,
 postgres performance list <pgsql-performance@postgresql.org>
Message-ID: <753223953.46319.1411106018106.JavaMail.zimbra@desy.de>
In-Reply-To: <541B6161.2040605@catalyst.net.nz>
References: <45142416.42827.1411033201079.JavaMail.zimbra@desy.de>
 <88549888.42916.1411034301200.JavaMail.zimbra@desy.de>
 <CAHyXU0xSJR=r4savNfMcpzxBh9ZzgMc4aZtD4DxramZiexkYSw@mail.gmail.com>
 <541B4704.9090305@catalyst.net.nz>
 <609416895.45709.1411074654752.JavaMail.zimbra@desy.de>
 <541B59AD.9020109@catalyst.net.nz> <541B6161.2040605@catalyst.net.nz>
Subject: Re: postgres 9.3 vs. 9.4
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Thread-Topic: postgres 9.3 vs. 9.4
Thread-Index: rthCreslPtFbotMJttsW+j/fdh88XQ==
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


----- 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
> 


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance