survey of WAL blocksize changes - Mailing list pgsql-hackers

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