Thread: 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.m.guerrero@usit.uio.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/
Attachment
On Mon, Jun 13, 2011 at 2:25 PM, Rafael Martinez <r.m.guerrero@usit.uio.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-----