Re: survey of WAL blocksize changes

Why on earth would there be a dip precisely at 8k with both smaller
and larger block sizes being faster??

--
Greg

On 27 May 2009, at 03:51, Mark Wong <markwkm(at)gmail(dot)com> wrote:

> Hi all,
>
> A long long time ago (in 2004) I ran a series of tests surveying the
> results of changing BLCKSZ when it was used for both the WAL logs and
> the rest of the database system:
>
> http://archives.postgresql.org/pgsql-hackers/2004-03/msg01194.php
>
> Now more than 5 years later and now being able to set the WAL log and
> the rest of the database to different block sizes, I have a set of
> test results with DBT-2 showing the effects of changing the WAL log
> block size on OLTP transaction throughput on ext2, ranging from 1KB to
> 64KB:
>
> BS notpm % Change from default
> -- ----- ----------
> 1 14673 -4.8%
> 2 15864 2.9%
> 4 15774 2.3%
> 8 15413 (default)
> 16 16118 4.6%
> 32 16051 4.1%
> 64 14874 -3.5%
>
> Pointers to raw data:
>
> BS url
> -- ---
> 1 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.wal.1/
> 2 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.wal.2/
> 4 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.wal.4/
> 8 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.2/
> 16 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.wal.16/
> 32 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.wal.32/
> 64 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.wal.64/
>
>
> It appears for this workload using a 16KB or 32KB gets more than 4%
> throughput improvement, but some of that could be noise. Nothing
> quite jaw dropping yet. It'll be interesting to see if the
> combination of changing the table block size can further improve the
> performance. It will probably be interesting to try different
> filesystems and filesystem blocksizes too.
>
> Regards,
> Mark Wong
>
> --
> 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