Information about WAL Configuration needs an update - Mailing list pgsql-docs

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello

I am sending this email because I think the section
"28.4. WAL Configuration" [1] 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
forums.

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

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:
http://archives.postgresql.org/pgsql-general/2011-05/msg00764.php

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?

[1] http://www.postgresql.org/docs/8.3/interactive/wal-configuration.html

regards,
- --
 Rafael Martinez Guerrero
 Center for Information Technology
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAk3okHIACgkQBhuKQurGihRLwwCglYVVAQgzlllx2h+enJXLCUCS
hW4AniXijZnRQ13F3AfyF68gaaKlvG97
=e3oI
-----END PGP SIGNATURE-----