Subject: Information about WAL Configuration needs an update

From: Rafael Martinez <[email-masked]>
Date: Fri, 03 Jun 2011 09:42:42 +0200

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/

---

From: Robert Haas <[email-masked]>
Date: Mon, 13 Jun 2011 13:24:02 -0400

On Fri, Jun 3, 2011 at 3:42 AM, Rafael Martinez
<[email-masked]> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello
>

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Rafael Martinez <[email-masked]>
Date: Mon, 13 Jun 2011 20:25:11 +0200

> would seem to be rather dangerous.

Well, a good start will be to try to identify or describe the situations
where checkpoints can take very long to complete or fail altogether.

I have the first one: Creating a large GIN index on a tsvector column. I
don't know why, maybe somebody who knows postgres internals can explain
why a creation of an index can create this situation.

regards,

---

From: Robert Haas <[email-masked]>
Date: Tue, 14 Jun 2011 09:01:59 -0400

> why a creation of an index can create this situation.

I think we're discussing this on the wrong list.  It sounds to me like
you have a performance or configuration problem (which likely has
nothing to do with GIN indexes specifically) that you haven't fully
diagnosed or understood (and I don't understand it either, at least
not based on the information so far provided) and because that problem
is manifesting itself as an excess of WAL files, you're homing in on
this part of the documentation.  And it may very well be that we need
some better documentation here, because I too have seen a few systems
lately with quite a lot of WAL files floating around for no
immediately obvious reason, but we can't document what is going on
until we understand it.  If you're interested in troubleshooting this
further, I think you should post to pgsql-performance and try to get
some help understanding what is happening.  If we get to the point
where we have a clear explanation for what is occurring, then we can
work out where and how to document it.

---

From: Rafael Martinez <[email-masked]>
Date: Wed, 15 Jun 2011 08:52:20 +0200

On 06/14/2011 03:01 PM, Robert Haas wrote:
> If you're interested in troubleshooting this

Ok, thank you Robert. I will try to find more information about this and
post it to pgsql-performance.

- -- 
 Rafael Martinez Guerrero