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>

Search Discussions

  • Fujii Masao at Mar 3, 2011 at 1:44 am

    On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera 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
  • Alvaro Herrera at Mar 3, 2011 at 2:16 pm

    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 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
  • Robert Haas at Mar 3, 2011 at 2:18 pm

    On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera 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 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
  • Alvaro Herrera at Mar 3, 2011 at 3:17 pm

    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
    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 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
  • Tom Lane at Mar 3, 2011 at 3:36 pm

    Alvaro Herrera 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
  • Bruce Momjian at Mar 10, 2011 at 1:21 am

    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. +
  • Robert Haas at Mar 3, 2011 at 3:36 pm

    On Thu, Mar 3, 2011 at 10:16 AM, Alvaro Herrera 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
    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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 2, '11 at 6:22p
activeMar 10, '11 at 1:21a
posts8
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase