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

More benchmarking of wal_buffers

From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Hackers" <pgsql-hackers(at)postgresql(dot)org>,<pgsql-performance(at)postgresql(dot)org>,"Advocacy" <pgsql-advocacy(at)postgresql(dot)org>
Subject: More benchmarking of wal_buffers
Date: 2003-02-13 05:16:16
Message-ID: GNELIHDDFBOCMGBFGEFOKEIKCFAA.chriskl@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-hackerspgsql-performance
Hi Everyone,

I've just spent the last day and a half trying to benchmark our new database
installation to find a good value for wal_buffers.  The quick answer - there
isn't, just leave it on the default of 8.

The numbers just swing up and down so much it's impossible to say that one
setting is better than another.  I've attached an openoffice doc with my old
shared_buffers tests plus the wal_buffers tests.  The wal results are a bit
deceptive as the results I've included are really what I consider the
'average' results.  Just occasionally, I'd get a spike that I could never
repeat...

Even if you look at the attached charts and you think that 128 buffers are
better than 8, think again - there's nothing in it.  Next time I run that
benchmark it could be the same, lower or higher.  And the difference between
the worst and best results is less than 3 TPS - ie. nothing.

One proof that has come out of this is that wal_buffers does not affect
SELECT only performance in any way.  So, for websites where the
select/update ratio is very high, wal_buffers is almost an irrelevant
optimisation.

Even massively heavy sites where you are getting write transactions
continuously by 64 simultaneous people, I was unable to prove that any
setting other than the default helped.  In this situation, probably the
commit_delay and commit_siblings variables will give you the best gains.

I'm not sure what I could test next.  Does FreeBSD support anything other
than fsync?  eg. fdatasync, etc.  I can't see it in the man pages...

Chris

ps. I don't think the attachments are too large, but if they annoy anyone,
tell me.  Also, I've cross posted to make sure people who read my previous
benchmark, see this one also.

Attachment: tpcb.gif
Description: image/gif (8.2 KB) (inlined above)
Attachment: select.gif
Description: image/gif (6.7 KB) (inlined above)
Attachment: PostgreSQL Benchmarking.sxc
Description: application/vnd.sun.xml.calc (16.6 KB)

Responses

pgsql-performance by date

Next:From: Bruce MomjianDate: 2003-02-13 05:30:07
Subject: Re: [HACKERS] More benchmarking of wal_buffers
Previous:From: Tom LaneDate: 2003-02-13 05:15:02
Subject: Re: Changing the default configuration (was Re:

pgsql-hackers by date

Next:From: Paul L DanielsDate: 2003-02-13 05:27:01
Subject: Version 7.2.3 Vacuum abnormality
Previous:From: Tom LaneDate: 2003-02-13 05:15:02
Subject: Re: Changing the default configuration (was Re:

pgsql-advocacy by date

Next:From: Bruce MomjianDate: 2003-02-13 05:30:07
Subject: Re: [HACKERS] More benchmarking of wal_buffers
Previous:From: Tom LaneDate: 2003-02-13 05:15:02
Subject: Re: Changing the default configuration (was Re:

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