Re: postgres 9.3 vs. 9.4

On Thu, Sep 18, 2014 at 2:10 PM, Mkrtchyan, Tigran <tigran(dot)mkrtchyan(at)desy(dot)de
> 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?
>

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