Thread: checkpoint_segments 32 megs?
Hello, Tom made mention earlier this week that upping the checkpoint segments might be a bad idea because we are committing someone to 32 megs per segment. I thought it odd at the time but I figured it was an 8.1 thing. As I review the 8.1dev postgresql.conf the checkpoint_segments option still states 16 megs per segment is that correct? Sincerely, Joshua D. Drake -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org
On Wed, Jul 13, 2005 at 12:53:57PM -0700, Joshua D. Drake wrote: > Tom made mention earlier this week that upping the checkpoint segments > might be a bad idea because we are committing someone to 32 megs per > segment. I thought it odd at the time but I figured it was an 8.1 thing. > > As I review the 8.1dev postgresql.conf the checkpoint_segments option > still states 16 megs per segment is that correct? The point is that in general, the 16 MiB figure is correct, but in pathological cases there can be up to two WAL segments on disk per checkpoint_segment, so 32 MiB. I don't remember the exact details but I think it was about generating lots of WAL traffic during checkpoint, which would lead to additional files being created while the older ones can't be removed or recycled yet. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "Estoy de acuerdo contigo en que la verdad absoluta no existe... El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> The point is that in general, the 16 MiB figure is correct, but in
> pathological cases there can be up to two WAL segments on disk per
> checkpoint_segment, so 32 MiB.
The reason for this is that after a checkpoint finishes, we recycle
WAL files up to the *previous* checkpoint --- so just before the
next checkpoint, you've got 2X the checkpoint spacing worth of WAL.
Add some slop for corner cases, and you arrive at the documentation's
statement that you may have as many as 2*checkpoint_segments+1
segments of WAL.
The main reason for wanting the previous checkpoint kept around is
so that we aren't dead in the water if the primary checkpoint is
unreadable --- we back up to the prior checkpoint and replay from
that. I'm not certain how important that really is; it was part of
Vadim's original design for WAL and no one ever particularly
questioned it.
regards, tom lane
On Wed, 2005-07-13 at 17:57 -0400, Tom Lane wrote: > I'm not certain how important that really is; it was part of > Vadim's original design for WAL and no one ever particularly > questioned it. Anybody setting checkpoint_segments high is likely to have a dedicated WAL disk anyway, which easily gives you space for 1000s of WAL segment files, given current disk sizes. So saving space shouldn't be a reason to want to remove that. It seems practical sense to have more than one checkpoint available. The whole purpose of WAL is robustness and recoverability. All DBAs (should) keep both their last backup and the one before that (at least), with many sites specifying an automatic retention history of 7 or more. That same philosophy should work with the WAL files also. So, overall, I see no reason to change that feature. Best Regards, Simon Riggs
People, > So, overall, I see no reason to change that feature. Me neither. If people are concerned about WAL on-disk size, they can reduce the number of segments. In my experience, anyway, high segment numbers don't provide any benefit unless you have a dedicated WAL disk. -- Josh Berkus Aglio Database Solutions San Francisco