On Thu, Sep 18, 2014 at 2:10 PM, Mkrtchyan, Tigran <tigran.mkrtch...@desy.de > wrote:
> > > ----- Original Message ----- > > From: "Mark Kirkwood" <mark.kirkw...@catalyst.net.nz> > > To: "Merlin Moncure" <mmonc...@gmail.com>, "Tigran Mkrtchyan" < > tigran.mkrtch...@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.mkrtch...@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? > Probably the first thing I'd so is strace -p the backend process with -T and -ttt while pgbench is running and watch a few seconds go by to see if anything stands out. Then strace -c and see what that shows. pg_test_fsync with the file put in each of the pg_xlog directory. (Actually, that is probably the first thing to do.) run pgbench with -l and see if the throughput is smooth or spiky. What does sar, top or vmstat say? Run with track_io_timing = on and with pg_stat_statements and see what they show. Also turn on log_checkpoints. Cheers, Jeff