From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Fujii Masao <masao(dot)fujii(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: WAL segments pile up during standalone mode |
Date: | 2011-03-03 15:36:18 |
Message-ID: | AANLkTikBmLEbOynQZKM8f_nkvRhu_htvg=r9AFh89jQz@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Mar 3, 2011 at 10:16 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)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(at)commandprompt(dot)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(at)alvh(dot)no-ip(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2011-03-03 15:38:47 | Re: Mark deprecated operators as such in their comments? |
Previous Message | Tom Lane | 2011-03-03 15:36:10 | Re: WAL segments pile up during standalone mode |