Horton

Thread

  1. wal_buffers

    Robert Haas <robertmhaas@gmail.com> — 2012-02-19T05:24:12Z

    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

  2. Re: wal_buffers

    Euler Taveira de Oliveira <euler@timbira.com> — 2012-02-19T14:46:33Z

    On 19-02-2012 02:24, Robert Haas wrote:
> 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
>
Isn't it useful to print some messages on the log when we have "wrap around"?
In this case, we have an idea that wal_buffers needs to be increased.


-- 
   Euler Taveira de Oliveira - Timbira       http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

  3. Re: wal_buffers

    Robert Haas <robertmhaas@gmail.com> — 2012-02-19T18:08:28Z

    On Sun, Feb 19, 2012 at 9:46 AM, Euler Taveira de Oliveira
<euler@timbira.com> wrote:
> On 19-02-2012 02:24, Robert Haas wrote:
>> 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
>>
> Isn't it useful to print some messages on the log when we have "wrap around"?
> In this case, we have an idea that wal_buffers needs to be increased.

I was thinking about that.  I think that what might be more useful
than a log message is a counter somewhere in shared memory.  Logging
imposes a lot of overhead, which is exactly what we don't want here,
and the volume might be quite high on a system that is bumping up
against this problem.  Of course then the question is... how would we
expose the counter value?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  4. Re: wal_buffers

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-02-19T18:33:11Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Sun, Feb 19, 2012 at 9:46 AM, Euler Taveira de Oliveira
> <euler@timbira.com> wrote:
>> Isn't it useful to print some messages on the log when we have "wrap around"?
>> In this case, we have an idea that wal_buffers needs to be increased.

> I was thinking about that.  I think that what might be more useful
> than a log message is a counter somewhere in shared memory.  Logging
> imposes a lot of overhead, which is exactly what we don't want here,
> and the volume might be quite high on a system that is bumping up
> against this problem.  Of course then the question is... how would we
> expose the counter value?

Why do you need a counter, other than the current LSN?  Surely the
number of WAL buffer ring cycles can be deduced directly from that.

			regards, tom lane

  5. Re: wal_buffers

    Simon Riggs <simon@2ndquadrant.com> — 2012-02-19T18:40:00Z

    On Sun, Feb 19, 2012 at 6:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Sun, Feb 19, 2012 at 9:46 AM, Euler Taveira de Oliveira
>> <euler@timbira.com> wrote:
>>> Isn't it useful to print some messages on the log when we have "wrap around"?
>>> In this case, we have an idea that wal_buffers needs to be increased.
>
>> I was thinking about that.  I think that what might be more useful
>> than a log message is a counter somewhere in shared memory.  Logging
>> imposes a lot of overhead, which is exactly what we don't want here,
>> and the volume might be quite high on a system that is bumping up
>> against this problem.  Of course then the question is... how would we
>> expose the counter value?
>
> Why do you need a counter, other than the current LSN?  Surely the
> number of WAL buffer ring cycles can be deduced directly from that.

The problem isn't how many times its cycled, the issue is whether
there was a wait induced by needing to flush wal buffers because of
too many writes. You can't count those waits in the way you suggest,
though you can calculate an upper limit on them, but that's not very
useful.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  6. Re: wal_buffers

    Fujii Masao <masao.fujii@gmail.com> — 2012-02-20T04:10:21Z

    On Mon, Feb 20, 2012 at 3:08 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sun, Feb 19, 2012 at 9:46 AM, Euler Taveira de Oliveira
> <euler@timbira.com> wrote:
>> On 19-02-2012 02:24, Robert Haas wrote:
>>> 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
>>>
>> Isn't it useful to print some messages on the log when we have "wrap around"?
>> In this case, we have an idea that wal_buffers needs to be increased.
>
> I was thinking about that.  I think that what might be more useful
> than a log message is a counter somewhere in shared memory.  Logging
> imposes a lot of overhead, which is exactly what we don't want here,
> and the volume might be quite high on a system that is bumping up
> against this problem.  Of course then the question is... how would we
> expose the counter value?

There is no existing statistics view suitable to include such information.
What about defining pg_stat_xlog or something?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

  7. Re: wal_buffers

    Greg Smith <greg@2ndquadrant.com> — 2012-02-20T04:26:10Z

    On 02/19/2012 12:24 AM, Robert Haas wrote:
> 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.

That's an easy enough idea to throw into my testing queue.  The 16MB 
auto-tuning upper bound was just the easiest number to suggest that was 
obviously useful and unlikely to be wasteful.  One of the reasons 
wal_buffers remains a user-visible parameter was that no one every 
really did an analysis at what its useful upper bound was--and that 
number might move up as other bottlenecks are smashed too.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

  8. Re: wal_buffers

    Simon Riggs <simon@2ndquadrant.com> — 2012-02-20T08:59:25Z

    On Mon, Feb 20, 2012 at 4:10 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Mon, Feb 20, 2012 at 3:08 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Sun, Feb 19, 2012 at 9:46 AM, Euler Taveira de Oliveira
>> <euler@timbira.com> wrote:
>>> On 19-02-2012 02:24, Robert Haas wrote:
>>>> 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
>>>>
>>> Isn't it useful to print some messages on the log when we have "wrap around"?
>>> In this case, we have an idea that wal_buffers needs to be increased.
>>
>> I was thinking about that.  I think that what might be more useful
>> than a log message is a counter somewhere in shared memory.  Logging
>> imposes a lot of overhead, which is exactly what we don't want here,
>> and the volume might be quite high on a system that is bumping up
>> against this problem.  Of course then the question is... how would we
>> expose the counter value?
>
> There is no existing statistics view suitable to include such information.
> What about defining pg_stat_xlog or something?

Perhaps pg_stat_perf so we don't need to find a new home every time.

Thinking about it, I think renaming pg_stat_bgwriter would make more sense.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  9. Re: wal_buffers

    Robert Haas <robertmhaas@gmail.com> — 2012-02-20T19:23:31Z

    On Mon, Feb 20, 2012 at 3:59 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> There is no existing statistics view suitable to include such information.
>> What about defining pg_stat_xlog or something?
>
> Perhaps pg_stat_perf so we don't need to find a new home every time.
>
> Thinking about it, I think renaming pg_stat_bgwriter would make more sense.

When we created pg_stat_reset_shared(text), we seemed to be
contemplating the idea of multiple sets of shared counters identified
by names -- bgwriter for the background writer, and maybe other things
for other subsystems.  So we'd have to think about how to adjust that.
 I do agree with you that it seems a shame to invent a whole new view
for one counter...

Another thought is that I'm not sure it makes sense to run this
through the stats system at all.  We could regard it as a shared
memory counter protected by one of the LWLocks involved, which would
probably be quite a bit cheaper - just one machine instruction to
increment it at need.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  10. Re: wal_buffers

    Bruce Momjian <bruce@momjian.us> — 2012-08-27T19:38:20Z

    Added to TODO:

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

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

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

  11. Re: wal_buffers

    Amit Kapila <amit.kapila@huawei.com> — 2012-08-28T04:10:33Z

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

  12. Re: wal_buffers

    Bruce Momjian <bruce@momjian.us> — 2012-08-28T16:03:19Z

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

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

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

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

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

  13. Re: wal_buffers

    Amit Kapila <amit.kapila@huawei.com> — 2012-08-29T10:08:12Z

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

> 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@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
> 

With Regards,
Amit Kapila.

  14. Re: wal_buffers

    Peter Geoghegan <peter@2ndquadrant.com> — 2012-08-30T02:25:57Z

    On 19 February 2012 05:24, Robert Haas <robertmhaas@gmail.com> wrote:
> 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.

Incidentally, I wondered if we could further improve group commit
performance by implementing commit_delay with a WaitLatch call, and
setting the latch in the event of WAL buffers wraparound (or rather, a
queued wraparound request - a segment switch needs WALWriteLock, which
the group commit leader holds for a relatively long time during the
delay). I'm not really sure how significant a win this might be,
though. There could be other types of contention, which could be
considerably more significant. I'll try and take a look at it next
week.

-- 
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

  15. Re: wal_buffers

    Robert Haas <robertmhaas@gmail.com> — 2012-08-30T13:44:07Z

    On Wed, Aug 29, 2012 at 10:25 PM, Peter Geoghegan <peter@2ndquadrant.com> wrote:
> On 19 February 2012 05:24, Robert Haas <robertmhaas@gmail.com> wrote:
>> 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.
>
> Incidentally, I wondered if we could further improve group commit
> performance by implementing commit_delay with a WaitLatch call, and
> setting the latch in the event of WAL buffers wraparound (or rather, a
> queued wraparound request - a segment switch needs WALWriteLock, which
> the group commit leader holds for a relatively long time during the
> delay). I'm not really sure how significant a win this might be,
> though. There could be other types of contention, which could be
> considerably more significant. I'll try and take a look at it next
> week.

I have a feeling that one of the big bottlenecks here is that we force
an immediate fsync when we reach the end of a segment.  I think it was
originally done that way to keep the code simple, and it does
accomplish that, but it's not so hot for performance.  More generally,
I think we really need to split WALWriteLock into two locks, one to
protect the write position and the other to protect the flush
position.  I think we're often ending up with a write (which is
usually fast) waiting for a flush (which is often much slower) when in
fact those things ought to be able to happen in parallel.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  16. Re: wal_buffers

    Amit Kapila <amit.kapila@huawei.com> — 2012-08-31T05:12:20Z

    On Thursday, August 30, 2012 7:14 PM Robert Haas wrote:
On Wed, Aug 29, 2012 at 10:25 PM, Peter Geoghegan <peter@2ndquadrant.com>
wrote:
> On 19 February 2012 05:24, Robert Haas <robertmhaas@gmail.com> wrote:
>>> 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.
>
>> Incidentally, I wondered if we could further improve group commit
>> performance by implementing commit_delay with a WaitLatch call, and
>> setting the latch in the event of WAL buffers wraparound (or rather, a
>> queued wraparound request - a segment switch needs WALWriteLock, which
>> the group commit leader holds for a relatively long time during the
>> delay). I'm not really sure how significant a win this might be,
>> though. There could be other types of contention, which could be
>> considerably more significant. I'll try and take a look at it next
>> week.

> I have a feeling that one of the big bottlenecks here is that we force
> an immediate fsync when we reach the end of a segment.  I think it was
> originally done that way to keep the code simple, and it does
> accomplish that, but it's not so hot for performance.  More generally,
> I think we really need to split WALWriteLock into two locks, one to
> protect the write position and the other to protect the flush
> position.  I think we're often ending up with a write (which is
> usually fast) waiting for a flush (which is often much slower) when in
> fact those things ought to be able to happen in parallel.

  This is really good idea for splitting WALWriteLock into two locks, 
  but in that case do we need separate handling for OPEN_SYNC method where 
  write and flush happens together?

  And more about WAL, do you have any suggestions regarding the idea of
triggering
  WALWriter in case Xlog buffers are nearly full?

With Regards,
Amit Kapila.


With Regards,
Amit Kapila.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company