Re: wal_buffers

On Tuesday, August 28, 2012 9:33 PM Bruce Momjian wrote:
On Tue, Aug 28, 2012 at 09:40:33AM +0530, Amit Kapila wrote:
> 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.

> I assumed the LOGWriter was already working as fast as it could, but
> couldn't keep up.

I have doubt that in some cases it might not, for example

1. Assume 16M Xlog buffers
2. 6M or less than that is filled.
3. Background writer decides how much to flush and starts writing and at the
same time backends start filling
remaining 10M of the buffers.
4. Background writer goes to sleep after flushing 6M.
5. Backends have filled all 16M, in this case it may so happen that some
backends might need to do I/O.

Some other cases where I think it can be useful to wake LogWriter
Case-1
-------
1. Log writer delay is default 200ms or set to some higher value by user.
2. All the configured buffers got filled, backend might need to do I/O.

Case-2
-------
The case-1 scenario can also happen even if user has set wal_buffers = -1
(Auto tuning of wal buffers),
Because it reserves XLog buffers equivalent to one segment file and in high
load if that gets filled, backends might need
to do I/O.

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

>
> 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. +
>
>
> --
> 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
>

With Regards,
Amit Kapila.