Thread: WAL segments pile up during standalone mode
I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. I can reproduce in HEAD easily by doing a large bulk insertion in standalone mode. If I stop the server, start in normal mode and shutdown (or checkpoint), the extra segments go away as expected. This was reported in pgsql-es-ayuda yesterday. -- Álvaro Herrera <alvherre@alvh.no-ip.org>
On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > I noticed that in standalone mode, WAL segments don't seem to be > recycled. This could get problematic if you're forced to vacuum large > tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: > On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > > I noticed that in standalone mode, WAL segments don't seem to be > > recycled. This could get problematic if you're forced to vacuum large > > tables in that mode and space for WAL is short. > > Checkpoint is required to recycle old WAL segments. Can checkpoint > be executed in standalone mode? even during VACUUM FULL? Hmm, I guess it would violate POLA that the standalone server would decide to run checkpoint in the middle of vacuum. I imagine that in some cases the only option would be to process the tables manually, with the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). So I can see that there is no good fix for this problem, yet it is a very inconvenient situation to be in. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: >> On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: >> > I noticed that in standalone mode, WAL segments don't seem to be >> > recycled. This could get problematic if you're forced to vacuum large >> > tables in that mode and space for WAL is short. >> >> Checkpoint is required to recycle old WAL segments. Can checkpoint >> be executed in standalone mode? even during VACUUM FULL? > > Hmm, I guess it would violate POLA that the standalone server would > decide to run checkpoint in the middle of vacuum. I imagine that in > some cases the only option would be to process the tables manually, with > the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). > > So I can see that there is no good fix for this problem, yet it is a > very inconvenient situation to be in. I don't think it would violate the POLA for a standalone backend to checkpoint periodically, but I have to admit I can count the number of times I've run a standalone backend on one hand. Does this come up much? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Excerpts from Robert Haas's message of jue mar 03 11:18:38 -0300 2011: > On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera > <alvherre@commandprompt.com> wrote: > > Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: > >> On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > >> > I noticed that in standalone mode, WAL segments don't seem to be > >> > recycled. This could get problematic if you're forced to vacuum large > >> > tables in that mode and space for WAL is short. > >> > >> Checkpoint is required to recycle old WAL segments. Can checkpoint > >> be executed in standalone mode? even during VACUUM FULL? > > > > Hmm, I guess it would violate POLA that the standalone server would > > decide to run checkpoint in the middle of vacuum. I imagine that in > > some cases the only option would be to process the tables manually, with > > the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). > > > > So I can see that there is no good fix for this problem, yet it is a > > very inconvenient situation to be in. > > I don't think it would violate the POLA for a standalone backend to > checkpoint periodically, but I have to admit I can count the number of > times I've run a standalone backend on one hand. Does this come up > much? I admit I have no idea why these guys seem to run into wraparound problems so much. On the other hand, I'm not sure that it would work to try to checkpoint "during" vacuum, because the backend is in a transaction. Maybe it would work to force a checkpoint after each command, and between tables in a multi-table vacuum (which is presumably a common thing to do in a standalone backend) or something like that? -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> I admit I have no idea why these guys seem to run into wraparound
> problems so much.
> On the other hand, I'm not sure that it would work to try to checkpoint
> "during" vacuum, because the backend is in a transaction. Maybe it
> would work to force a checkpoint after each command, and between tables
> in a multi-table vacuum (which is presumably a common thing to do in a
> standalone backend) or something like that?
I really don't care for the idea of standalone mode doing *anything*
the user didn't explicitly tell it to. In its role as a disaster
recovery tool, that's just a recipe for shooting yourself in the foot.
Perhaps this problem would be adequately addressed by documentation,
ie suggest that when vacuuming very large tables in standalone mode,
you should issue CHECKPOINT after each one.
regards, tom lane
On Thu, Mar 3, 2011 at 10:16 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Excerpts from Robert Haas's message of jue mar 03 11:18:38 -0300 2011: >> On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera >> <alvherre@commandprompt.com> wrote: >> > Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: >> >> On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: >> >> > I noticed that in standalone mode, WAL segments don't seem to be >> >> > recycled. This could get problematic if you're forced to vacuum large >> >> > tables in that mode and space for WAL is short. >> >> >> >> Checkpoint is required to recycle old WAL segments. Can checkpoint >> >> be executed in standalone mode? even during VACUUM FULL? >> > >> > Hmm, I guess it would violate POLA that the standalone server would >> > decide to run checkpoint in the middle of vacuum. I imagine that in >> > some cases the only option would be to process the tables manually, with >> > the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). >> > >> > So I can see that there is no good fix for this problem, yet it is a >> > very inconvenient situation to be in. >> >> I don't think it would violate the POLA for a standalone backend to >> checkpoint periodically, but I have to admit I can count the number of >> times I've run a standalone backend on one hand. Does this come up >> much? > > I admit I have no idea why these guys seem to run into wraparound > problems so much. > > On the other hand, I'm not sure that it would work to try to checkpoint > "during" vacuum, because the backend is in a transaction. Maybe it > would work to force a checkpoint after each command, and between tables > in a multi-table vacuum (which is presumably a common thing to do in a > standalone backend) or something like that? I doubt it's necessary to force a checkpoint after each command - I assume that if you want one, you can just execute the CHECKPOINT command explicitly. The multi-table VACUUM case could be handled similarly - VACUUM each table, then checkpoint, and so on. It'd probably be more worthwhile to pursue the approach of allowing the system to be brought up in multi-user mode, but allow only super-users to log in and don't allow them to do anything except VACUUM until some semblance of sanity is achieved. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > I admit I have no idea why these guys seem to run into wraparound > > problems so much. > > > On the other hand, I'm not sure that it would work to try to checkpoint > > "during" vacuum, because the backend is in a transaction. Maybe it > > would work to force a checkpoint after each command, and between tables > > in a multi-table vacuum (which is presumably a common thing to do in a > > standalone backend) or something like that? > > I really don't care for the idea of standalone mode doing *anything* > the user didn't explicitly tell it to. In its role as a disaster > recovery tool, that's just a recipe for shooting yourself in the foot. > > Perhaps this problem would be adequately addressed by documentation, > ie suggest that when vacuuming very large tables in standalone mode, > you should issue CHECKPOINT after each one. I documented that there is no automatic background processing (checkpoints) in single-user mode. I did not mention the idea of running checkpoints manually. Applied patch attached. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml index d332cba..bd4ae33 100644 *** a/doc/src/sgml/ref/postgres-ref.sgml --- b/doc/src/sgml/ref/postgres-ref.sgml *************** PostgreSQL documentation *** 745,751 **** <para> Note that the single-user mode server does not provide sophisticated ! line-editing features (no command history, for example). </para> </refsect1> --- 745,754 ---- <para> Note that the single-user mode server does not provide sophisticated ! line-editing features (no command history, for example). ! Single-User mode also does not do any background processing, like ! automatic checkpoints. ! </para> </refsect1>