pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Martin Foster <martin@ethereal-realms.org>
Cc: Gaetano Mendola <mendola@bigfoot.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Performance Bottleneck
Date: Fri, 06 Aug 2004 20:00:21 -0400
Message-ID: <24609.1091836821@sss.pgh.pa.us> (raw)
In-Reply-To: <41140D17.7070101@ethereal-realms.org>
References: <ktQPc.23297$yT2.16983@clgrps13>
	<410FDC53.5080509@bigfoot.com>
	<X0ZPc.23543$yT2.23053@clgrps13>
	<4110FFF6.1030001@bigfoot.com>
	<41140D17.7070101@ethereal-realms.org>

Martin Foster <martin@ethereal-realms.org> writes:
> Gaetano Mendola wrote:
>> change this values in:
>> shared_buffers = 50000
>> sort_mem = 16084
>> 
>> wal_buffers = 1500

This value of wal_buffers is simply ridiculous.

There isn't any reason to set wal_buffers higher than the amount of
WAL log data that will be generated by a single transaction, because
whatever is in the buffers will be flushed at transaction commit.
If you are mainly dealing with heavy concurrency then it's the mean time
between transaction commits that matters, and that's even less than the
average transaction length.

Even if you are mainly interested in the performance of large updating
transactions that are not concurrent with anything else (bulk data load,
perhaps), I'm not sure that I see any value in setting wal_buffers so
high.  The data will have to go to disk before commit in any case, and
buffering so much of it just means that you are going to have a serious
spike in disk traffic right before commit.  It's almost certainly better
to keep wal_buffers conservatively small and let the data trickle out as
the transaction proceeds.  I don't actually think there is anything very
wrong with the default value (8) ... perhaps it is too small, but it's
not two orders of magnitude too small.

In 8.0, the presence of the background writer may make it useful to run
with wal_buffers somewhat higher than before, but I still doubt that
order-of-a-thousand buffers would be useful.  The RAM would almost
certainly be better spent on general-purpose disk buffers or kernel
cache.

Note though that this is just informed opinion, as I've never done or
seen any benchmarks that examine the results of changing wal_buffers
while holding other things constant.  Has anyone tried it?

			regards, tom lane



view thread (38+ messages)  latest in thread

Message-ID: <24609.1091836821@sss.pgh.pa.us>
Permalink:  ../24609.1091836821@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/24609.1091836821@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, martin@ethereal-realms.org, mendola@bigfoot.com
  Subject: Re: Performance Bottleneck
  In-Reply-To: <24609.1091836821@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox