Re: survey of WAL blocksize changes

I find it pretty hard to beleive that 8k is precisely where a drop in
performance shows up unless there's some peculiar reason.

The only peculiar reason I can imagine is full page writes. If the
dbt2 workload is modifying already full pages then the full page
writes will always be just shy of a complete page and with the xlog
record might consistently be just larger than a full block.

I'm not immediately sure why that would cause a problem but it's been
a while since I traced through the xlog code.

--
Greg

On 28 May 2009, at 02:09, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mark Wong <markwkm(at)gmail(dot)com> writes:
>> Oopsies. I've rerun, but now that there is no dip, the average
>> throughput still didn't change much:
>
>> BS notpm % Change from default
>> -- ----- ----------
>> 1 14673 -5.1%
>> 2 15864 2.7%
>> 4 15774 2.1%
>> 8 15454 (default)
>> 16 16118 4.3%
>> 32 16051 3.9%
>> 64 14874 -3.8%
>
> 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:
>
> 1. A strong penalty for smaller block sizes, which becomes dominant
> below 2KB.
>
> 2. A strong penalty for larger block sizes, which becomes dominant
> above 32KB.
>
> 3. A weak benefit for smaller block sizes, which is visible at 2-4KB
> but fades away at 8KB.
>
> 4. A weak benefit for larger block sizes, which only becomes visible
> above 8KB.
>
> 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'm suspecting that this curve is heavily dependent on details of the
> DBT2 test and/or the hardware used. It would be interesting to see if
> anyone can replicate it using a different benchmark.
>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers