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

Re: survey of WAL blocksize changes

From: Greg Stark <greg(dot)stark(at)enterprisedb(dot)com>
To: Mark Wong <markwkm(at)gmail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: survey of WAL blocksize changes
Date: 2009-05-27 13:40:52
Message-ID: DC717247-34BC-4403-B2E1-4FB7A87C814F@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2009-05-27 13:41:18
Subject: Re: New trigger option of pg_standby
Previous:From: Peter EisentrautDate: 2009-05-27 13:36:36
Subject: Re: survey of WAL blocksize changes

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