Re: wal_buffers

From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Bruce Momjian

> Added to TODO:

>    Allow reporting of stalls due to wal_buffer wrap-around
>
http://archives.postgresql.org/pgsql-hackers/2012-02/msg00826.php 

Isn't this indicates that while writing XLOG, it needs some tuning such that
when some thresh hold buffers(2/3) are full, then trigger LOGWriter. 



---------------------------------------------------------------------------

On Sun, Feb 19, 2012 at 12:24:12AM -0500, Robert Haas wrote:
> Just for kicks, I ran two 30-minute pgbench tests at scale factor 300
> tonight on Nate Boley's machine, with -n -l -c 32 -j 32.  The
> configurations were identical, except that on one of them, I set
> wal_buffers=64MB.  It seemed to make quite a lot of difference:
> 
> wal_buffers not set (thus, 16MB):
> tps = 3162.594605 (including connections establishing)
> 
> wal_buffers=64MB:
> tps = 6164.194625 (including connections establishing)
> 
> Rest of config: shared_buffers = 8GB, maintenance_work_mem = 1GB,
> synchronous_commit = off, checkpoint_segments = 300,
> checkpoint_timeout = 15min, checkpoint_completion_target = 0.9,
> wal_writer_delay = 20ms
> 
> I have attached tps scatterplots.  The obvious conclusion appears to
> be that, with only 16MB of wal_buffers, the buffer "wraps around" with
> some regularity: we can't insert more WAL because the buffer we need
> to use still contains WAL that hasn't yet been fsync'd, leading to
> long stalls.  More buffer space ameliorates the problem.  This is not
> very surprising, when you think about it: it's clear that the peak tps
> rate approaches 18k/s on these tests; right after a checkpoint, every
> update will force a full page write - that is, a WAL record > 8kB.  So
> we'll fill up a 16MB WAL segment in about a tenth of a second.  That
> doesn't leave much breathing room.  I think we might want to consider
> adjusting our auto-tuning formula for wal_buffers to allow for a
> higher cap, although this is obviously not enough data to draw any
> firm conclusions.
> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers