Re: fallocate / posix_fallocate for new WAL file creation (etc...)

On 2013-05-28 10:03:58 -0400, Robert Haas wrote:
> On Sat, May 25, 2013 at 2:55 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> >> The biggest thing missing from this submission is information about what
> >> performance testing you did. Ideally performance patches are submitted with
> >> enough information for a reviewer to duplicate the same test the author did,
> >> as well as hard before/after performance numbers from your test system. It
> >> often turns tricky to duplicate a performance gain, and being able to run
> >> the same test used for initial development eliminates a lot of the problems.
> >
> > This has been a bit of a struggle. While it's true that WAL file
> > creation doesn't happen with great frequency, and while it's also true
> > that - with strace and other tests - it can be proven that
> > fallocate(16MB) is much quicker than writing it zeroes by hand,
> > proving that in the larger context of a running install has been
> > challenging.
>
> It's nice to be able to test things in the context of a running
> install, but sometimes a microbenchmark is just as good. I mean, if
> posix_fallocate() is faster, then it's just faster, right?

Well, it's a bit more complex than that. Fallocate doesn't actually
initializes the disk space in most filesystems, just marks it as
allocated and zeroed which is one of the reasons it can be noticeably
faster. But that can make the runtime overhead of writing to those pages
higher.

I wonder whether noticeably upping checkpoint segments and then
a) COPY in a large table
b) a pgbench on a previously initialized table.

Greetings,

Andres Freund

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