Обсуждение: Re: [COMMITTERS] pgsql: Reduce checkpoints and WAL traffic on low activity database serv
On Wed, Nov 2, 2011 at 12:34 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Wed, Nov 2, 2011 at 4:15 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Wed, Nov 2, 2011 at 11:39 AM, Simon Riggs <simon@2ndquadrant.com> wrote: >>> Reduce checkpoints and WAL traffic on low activity database server >>> Previously, we skipped a checkpoint if no WAL had been written since >>> last checkpoint, though this does not appear in user documentation. >>> As of now, we skip a checkpoint until we have written at least one >>> enough WAL to switch the next WAL file. This greatly reduces the >>> level of activity and number of WAL messages generated by a very >>> low activity server. This is safe because the purpose of a checkpoint >>> is to act as a starting place for a recovery, in case of crash. >>> This patch maintains minimal WAL volume for replay in case of crash, >>> thus maintaining very low crash recovery time. >> >> I think you need to update the docs, for checkpoint_timeout if nothing else. > > Checkpoints have always been skipped, if no activity. So the docs > don't need changing. Eh, well, I don't agree. The section on WAL configuration says: >> A checkpoint is created every checkpoint_segments log segments, or every checkpoint_timeout seconds, whichever comes first. Now, admittedly, that doesn't mention that a checkpoint won't be created if there's absolutely no activity, but the fact that we might now not do it even if there has been some minimal activity seems worth adding. Similarly, the discussion of checkpoint_segments itself says: Maximum time between automatic WAL checkpoints, in seconds. The default is five minutes (5min). Increasing this parameter can increase the amount of time needed for crash recovery. This parameter can only be set in the postgresql.conf file or on the server command line. Again, it's no longer the maximum time between automatic checkpoints. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Nov 2, 2011 at 6:27 PM, Robert Haas <robertmhaas@gmail.com> wrote: > Again, it's no longer the maximum time between automatic checkpoints. You're missing the point that it never was like that. I've not altered the situation. And it doesn't matter either, so I will ignore. If you see a need to correct those docs, feel free. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 11/02/2011 05:48 PM, Simon Riggs wrote: > On Wed, Nov 2, 2011 at 6:27 PM, Robert Haas<robertmhaas@gmail.com> wrote: > > >> Again, it's no longer the maximum time between automatic checkpoints. >> > You're missing the point that it never was like that. I've not altered > the situation. > Robert's point is more that the existing docs are already broken; this new patch can just increase how bad the drift between reality and documentation can be. Before, the only people who ran into this had zero activity on the server, which meant there wasn't any data to be lost, either. Now it's potentially broader than that. With some trivial checkpoints containing a small amount of data skipped now, aren't there some cases where less WAL data will be written than before? In that case, the user visible behavior here would be different. I'd be most concerned about file-based log shipping case. I don't think there's any change needed to the "Write Ahead Log" section of the "Server Configuration" chapter. In the "Reliability and the Write-Ahead Log" chapter, this text in "WAL Configuration" was already highlighted as the problem here: The server's background writer process automatically performs a checkpoint every so often. A checkpoint is created every checkpoint_segments log segments, or every checkpoint_timeout seconds, whichever comes first. The default settings are 3 segments and 300 seconds (5 minutes), respectively. It is also possible to force a checkpoint by using the SQL command CHECKPOINT. I think this needs a change like this, to address the hole that was already in the docs and cover the new behavior too; this goes just before " It is also possible to force..." In cases where there are little or no writes to the WAL, checkpoints will be skipped even if checkpoint_timeout has passed. At least one new WAL segment must have been created before an automatic checkpoint occurs. The time between checkpoints and when new WAL segments are created are not related in any other way. If file-based WAL shipping is being used and you want to bound how often files are sent to standby server, to reduce potential data loss you should adjust archive_timeout parameter rather than the checkpoint ones. This area is a confusing one, so some clarification of the relation between checkpoints and replication is a useful docs improvement. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
Greg Smith <greg@2ndQuadrant.com> writes:
> On 11/02/2011 05:48 PM, Simon Riggs wrote:
>> You're missing the point that it never was like that. I've not altered
>> the situation.
> Robert's point is more that the existing docs are already broken; this
> new patch can just increase how bad the drift between reality and
> documentation can be.
Yeah. I agree that we need to adjust the docs. Anybody object
to Greg's proposed text?
regards, tom lane
On Thu, Nov 3, 2011 at 12:21 AM, Greg Smith <greg@2ndquadrant.com> wrote: > With some trivial checkpoints containing a small amount of data skipped now, > aren't there some cases where less WAL data will be written than before? In > that case, the user visible behavior here would be different. I'd be most > concerned about file-based log shipping case. Typical settings are checkpoint_timeout = 300 and archive_timeout = 30. So file-based replication users won't notice any difference. The only people who will see a difference are people with archive_timeout = 0 and who either store or stream WAL. For those people, a keepalive message will be available to ensure we can check the link is up, even if no WAL data flows, which I am working on next. The change also increases durability, since the secondary checkpoint is usually in a separate file. > In cases where there are little or no writes to the WAL, checkpoints will be > skipped even if checkpoint_timeout has passed. At least one new WAL segment > must have been created before an automatic checkpoint occurs. The time > between checkpoints and when new WAL segments are created are not related in > any other way. If file-based WAL shipping is being used and you want to > bound how often files are sent to standby server, to reduce potential data > loss you should adjust archive_timeout parameter rather than the checkpoint > ones. Committed, thanks. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Nov 2, 2011 at 8:21 PM, Greg Smith <greg@2ndquadrant.com> wrote: > In cases where there are little or no writes to the WAL, checkpoints will be > skipped even if checkpoint_timeout has passed. At least one new WAL segment > must have been created before an automatic checkpoint occurs. The time > between checkpoints and when new WAL segments are created are not related in > any other way. If file-based WAL shipping is being used and you want to > bound how often files are sent to standby server, to reduce potential data > loss you should adjust archive_timeout parameter rather than the checkpoint > ones. I think this is good, although "where there are little or no writes to the WAL" seems a bit awkward to me - how about "where little or no WAL has been written"? I would probably delete "to reduce potential data loss" from the last sentence, since I think that sentence has a few too many clauses to be easily parseable. Should we also put a similar sentence into the documentation for checkpoint_timeout? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company