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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mark Wong <markwkm(at)gmail(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: survey of WAL blocksize changes
Date: 2009-05-28 01:37:20
Message-ID: 10A32BCE-433E-438C-98FA-630A4BF0AEC2@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I find it pretty hard to beleive that 8k is precisely where a drop in  
performance shows up unless there's some peculiar reason.

The only peculiar reason I can imagine is full page writes. If the  
dbt2 workload is modifying already full pages then the full page  
writes will always be just shy of a complete page and with the xlog  
record might consistently be just larger than a full block.

I'm not immediately sure why that would cause a problem but it's been  
a while since I traced through the xlog code.

-- 
Greg


On 28 May 2009, at 02:09, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mark Wong <markwkm(at)gmail(dot)com> writes:
>> Oopsies.  I've rerun, but now that there is no dip, the average
>> throughput still didn't change much:
>
>> BS notpm % Change from default
>> -- ----- ----------
>> 1 14673 -5.1%
>> 2 15864 2.7%
>> 4 15774 2.1%
>> 8 15454 (default)
>> 16 16118 4.3%
>> 32 16051 3.9%
>> 64 14874 -3.8%
>
> So, if we assume that these numbers are real and not artifacts, it  
> seems
> we have to postulate at least four distinct block-size-dependent
> performance effects:
>
> 1. A strong penalty for smaller block sizes, which becomes dominant
> below 2KB.
>
> 2. A strong penalty for larger block sizes, which becomes dominant
> above 32KB.
>
> 3. A weak benefit for smaller block sizes, which is visible at 2-4KB
> but fades away at 8KB.
>
> 4. A weak benefit for larger block sizes, which only becomes visible
> above 8KB.
>
> It's not too hard to believe any of those individually, and even to
> think of plausible mechanisms.  But it seems a bit unlikely that  
> effects
> 3 and 4 would exist but consistently cross over right at our  
> traditional
> choice of block size.
>
> I'm suspecting that this curve is heavily dependent on details of the
> DBT2 test and/or the hardware used.  It would be interesting to see if
> anyone can replicate it using a different benchmark.
>
>            regards, tom lane
>
> -- 
> 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: andyDate: 2009-05-28 01:43:00
Subject: sun blade 1000 donation
Previous:From: Greg StarkDate: 2009-05-28 01:29:35
Subject: Re: User-facing aspects of serializable transactions

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