Re: wal_buffers

From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +