Re: 9.4 regression

On 2013-08-07 23:55:22 -0500, Jon Nelson wrote:
> > - enable log_checkpoints, post the lines spat out together with the results
> > - run pgbench without reinitializing the cluster/pgbench tables
> > inbetween?
>
> When I do this (as I note below), the performance difference (for me)
> disappears.

Ok. In this case the files have been written to and fsync()ed before the
test is run.

> > Basically, I'd like to know whether its the initialization that's slow
> > (measurable because we're regularly creating new files because of a too
> > low checkpoint_segments) or whether it's the actual writes.
>
> What I've found so far is very confusing.
> I start by using initdb to initialize a temporary cluster, copy in a
> postgresql.conf (with the modifcations from Thom Brown tweaked for my
> small laptop), start the cluster, create a test database, initialize
> it with pgbench, and then run. I'm also only running for two minutes
> at this time.

Hm. In a 2 minutes run there won't be any checkpoints..

> Every time I test, the non-fallocate version is faster. I modifed
> xlog.c to use posix_fallocate (or not) based on an environment
> variable.
> Once the WAL files have been rewritten at least once, then it doesn't
> seem to matter which method is used to allocate them (although
> posix_fallocate seems to have a slight edge). I even went to far as to
> modify the code to posix_fallocate the file *and then zero it out
> anyway*, and it was almost as fast as zeroing alone, and faster than
> using posix_fallocate alone.

Ok, so it seems there's performance issues with allocating the data even
though the allocation supposedly has already been made. Access by
several backends may play a role here.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services