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

survey of WAL blocksize changes

From: Mark Wong <markwkm(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: survey of WAL blocksize changes
Date: 2009-05-27 02:51:05
Message-ID: 70c01d1d0905261951w31709520gddde6e1603f69c77@mail.gmail.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2009-05-27 02:53:00
Subject: Re: commitfest management webapp
Previous:From: Greg SmithDate: 2009-05-27 02:15:58
Subject: Re: [HACKERS] commitfest management webapp

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