Skip site navigation (1) Skip section navigation (2)

survey of table blocksize changes

From: Mark Wong <markwkm(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: survey of table blocksize changes
Date: 2009-06-01 00:08:07
Message-ID: 70c01d1d0905311708h74f37d00kd9a532e1a6641afa@mail.gmail.com (view raw or flat)
Thread:
Lists: 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

pgsql-hackers by date

Next:From: Joe ConwayDate: 2009-06-01 00:27:32
Subject: Re: dblink patches for comment
Previous:From: Tom LaneDate: 2009-06-01 00:05:31
Subject: Re: [GENERAL] INTERVAL data type and libpq - what format?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group