Horton

Thread

  1. WAL segments pile up during standalone mode

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2011-03-02T18:22:01Z

    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>

  2. Re: WAL segments pile up during standalone mode

    Fujii Masao <masao.fujii@gmail.com> — 2011-03-03T01:44:45Z

    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

  3. Re: WAL segments pile up during standalone mode

    Alvaro Herrera <alvherre@commandprompt.com> — 2011-03-03T14:15:56Z

    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

  4. Re: WAL segments pile up during standalone mode

    Robert Haas <robertmhaas@gmail.com> — 2011-03-03T14:18:38Z

    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

  5. Re: WAL segments pile up during standalone mode

    Alvaro Herrera <alvherre@commandprompt.com> — 2011-03-03T15:16:56Z

    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

  6. Re: WAL segments pile up during standalone mode

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-03-03T15:36:10Z

    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

  7. Re: WAL segments pile up during standalone mode

    Robert Haas <robertmhaas@gmail.com> — 2011-03-03T15:36:18Z

    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

  8. Re: WAL segments pile up during standalone mode

    Bruce Momjian <bruce@momjian.us> — 2011-03-10T01:21:04Z

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