Обсуждение: wal_buffers
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
Вложения
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, Suporte24x7 e Treinamento
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
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
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
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
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
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
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 inventa 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
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. +
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
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. +
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.
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
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
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_SYNCmethod 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers