Information about WAL Configuration needs an update

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

On Fri, Jun 3, 2011 at 3:42 AM, Rafael Martinez
<r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> wrote:
> -----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?

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

On Mon, 2011-06-13 at 13:24 -0400, Robert Haas wrote:
> On Fri, Jun 3, 2011 at 3:42 AM, Rafael Martinez
> >
> > 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.
>

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,
--
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

On Mon, Jun 13, 2011 at 2:25 PM, Rafael Martinez
<r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> wrote:
> On Mon, 2011-06-13 at 13:24 -0400, Robert Haas wrote:
>> On Fri, Jun 3, 2011 at 3:42 AM, Rafael Martinez
>> >
>> > 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.
>>
>
> 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.

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.

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

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

On 06/14/2011 03:01 PM, Robert Haas wrote:
> 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.
>

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

- --
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)

iEYEARECAAYFAk34VqQACgkQBhuKQurGihTq0gCglwJ5ij8H7oJYnla2quu2sTB2
ZYYAn1eUL/fyEbTRVNpJGFEU+WuI5ex7
=dXQ5
-----END PGP SIGNATURE-----