On Fri, Jun 3, 2011 at 3:42 AM, Rafael Martinez
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> I am sending this email because I think the section
> "28.4. WAL Configuration"  of the manual needs to be improved to
> avoid some potential configuration problems.
> I have experienced the problem I am going to describe myself and it is
> not the first time other postgres users have asked about this in other
> This section says among other things (pg-8.3):
> " ..... There will always be at least one WAL segment file, and will
> normally not be more than (2 + checkpoint_completion_target) *
> checkpoint_segments + 1 files. Each segment file is normally 16 MB
> (though this size can be altered when building the server). You can use
> this to estimate space requirements for WAL. Ordinarily, when old log
> segment files are no longer needed, they are recycled (renamed to become
> the next segments in the numbered sequence). If, due to a short-term
> peak of log output rate, there are more than 3 * checkpoint_segments + 1
> segment files, the unneeded segment files will be deleted instead of
> recycled until the system gets back under this limit....."
> For 9.0 it is almost the same but with some additional information about
> The part I think should be improved by a note or an extra paragraph is
> this one "... If, due to a short-term peak of log output rate ..."
> What is the meaning of a "short-term peak" and how many WAL files over
> the (3 * checkpoint_segments + 1 segment files) limit can we expect
> during a short-term peak?
> I sent some days ago an email to pgsql-general about this, REF:
> But we did not get to any conclusion about how much disk for WAL files
> is really necessary.
> I've run some tests to try to get some numbers that can explain what
> happens in my case.
> What we have seen is that when creating a GIN index in a tsvector column
> the number of WAL files grow almost proportionally with the size of the
> index we are creating.
> The GIN index we are creating on a ~7GB table in one our system is
> around 17GB.
> The amount of WAL files in this system will grow to 1353 WAL files while
> this GIN index is being created (checkpoint_segments=128,
> checkpoint_completion_target=0.5 and checkpoint_timeout=5min)
> Normally, the amount of WAL files according to the documentation should
> be between 321 to 385 in our case. But it doesn't say anything about how
> many WAL files you can expect during a "short-term peak" and what can
> provoke this.
> In our case we got over 1000 "extra" WAL files that it is almost the
> equivalent to the 17GB of our GIN index. The amount of WAL files got
> back to a normal level after this GIN index was generated.
> You can see the graph with the generation of WAL files + some extra
> information for this test here: http://folk.uio.no/rafael/total_wal/
> What do you think? Shouldn't we update the documentation with some
> information about this?
Perhaps, but we'd have to think of something intelligent to say about
it first. We can't remove the old WAL files until we successfully
checkpoint, and so I think if checkpoints are taking a very long to
complete or failing altogether, there's actually no upper bound. I
don't think we have any kind of "hard stop" where, if no log space is
available, we just refuse to process write transactions - such a thing
would seem to be rather dangerous.
The Enterprise PostgreSQL Company
In response to
pgsql-docs by date
|Next:||From: Ross J. Reedstrom||Date: 2011-06-13 18:15:26|
|Subject: Re: Documentation and explanatory diagrams|
|Previous:||From: Robert Haas||Date: 2011-06-13 17:19:32|
|Subject: Re: CIDR address in pg_hba.conf|