survey of table blocksize changes - Mailing list pgsql-hackers

Hi all,

I ran a few more tests changing the table blocksizes, similar to:

http://archives.postgresql.org/pgsql-hackers/2009-05/msg01068.php

I did make one change, specifically enable autovacuum, which I had
disabled for the previous thread.  The WAL blocksize here is using
16KB.

Here's the data:

BS notpm % Change from default
-- ----- ---------------------1  3122 -80.1%2  8719 -44.3%4 16481   5.3%8 15659 (default)
16 13896 -11.3%
32 10279 -34.4%

Pointers to raw data:

BS url
-- ---1 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table1/2
http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table2/4
http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table4/8
http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.16/
16 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table16/
32 http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.a.wal.16.table32/


It seems like for DBT2, there might be some benefit to setting the
table blocksize to 4KB, but some of this could be noise.  But anything
smaller than 4GB and larger than 8KB looks like a fairly significant
performance drop for DBT2.  I wonder if there's any coincidence that
the blocksize of the ext2 filesystem is also 4KB.

Regards,
Mark Wong