On Thu, Sep 18, 2014 at 2:10 PM, Mkrtchyan, Tigran <tigran.mkrtchyan@xxxxxxx> 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?
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
