Re: survey of WAL blocksize changes

On Wed, 2009-05-27 at 21:09 -0400, Tom Lane wrote:

> So, if we assume that these numbers are real and not artifacts, it seems
> we have to postulate at least four distinct block-size-dependent
> performance effects:

Two performance effects would be sufficient to explain the results.

* Optimal performance for small WAL changes is reached at around 4kB.
Anything smaller or larger lessens the benefit from this.

* Optimal performance for full page writes is reached at a WAL block
size 2-4 times larger than db block size, corresponding to sizes of WAL
records generated by test.

The two effects have a tail off on either side, giving the four effects
you spoke of.

> It's not too hard to believe any of those individually, and even to
> think of plausible mechanisms. But it seems a bit unlikely that effects
> 3 and 4 would exist but consistently cross over right at our traditional
> choice of block size.

I could believe two, but we would need some careful instrumentation to
reveal at what times we got benefit. We will never achieve improvements
if we look at figures averaged over longer periods.

We should be trying to improve specific parts of the checkpoint cycle,
which I would break down like this:
* ramp-up
* checkpoint spike
* post-checkpoint trough
* normal running
There is very clear modal behaviour showing in the tests and we should
look at the effects of patches in each case. I could well believe that
we make a gain at one stage and lose on another.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support