From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Jeremy Haile <jhaile@fastmail.fm>
To: pgsql-performance@postgresql.org
Subject: Re: URGENT: Out of disk space pg_xlog
Date: Fri, 22 Dec 2006 11:24:08 -0600
Message-ID: <458BC058.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <1166806378.10592.281708161@webmail.messagingengine.com>
References: <1166803606.2828.281701761@webmail.messagingengine.com>
<1166806378.10592.281708161@webmail.messagingengine.com>
As I understand it, the log space accumulates for the oldest transaction
which is still running, and all transactions which started after it. I
don't think there is any particular limit besides available disk space.
Long running transactions can cause various problems, including table
and index bloat which can degrade performance. You should probably look
at whether the long running transaction could be broken down into a
number of smaller ones.
-Kevin
>>> On Fri, Dec 22, 2006 at 10:52 AM, in message
<1166806378.10592.281708161@webmail.messagingengine.com>, "Jeremy
Haile"
<jhaile@fastmail.fm> wrote:
> Sorry for my rushed posting, as I was in a bit of a panic.
>
> We moved the pg_xlog directory over to a 70GB partition, and after
15- 20
> minutes the automatic recovery finished. Everything is working fine
> now.
>
> I would still appreciate a PG guru explaining how to estimate size
for a
> pg_xlog partition. It seems like it can vary considerably depending
on
> how intensive your current transactions are. Is there a way to
> determine a maximum?
>
> On Fri, 22 Dec 2006 11:06:46 - 0500, "Jeremy Haile"
<jhaile@fastmail.fm>
> said:
>> I created a 10GB partition for pg_xlog and ran out of disk space
today
>> during a long running update. My checkpoint_segments is set to 12,
but
>> there are 622 files in pg_xlog. What size should the pg_xlog
partition
>> be?
>>
>> Postmaster is currently not starting up (critical for my
organization)
>> and reports "FATAL: The database system is starting up" .
>>
>> The log reports:
>> 2006- 12- 22 10:50:09 LOG: checkpoint record is at 2E/87A323C8
>> 2006- 12- 22 10:50:09 LOG: redo record is at 2E/8729A6E8; undo
record is
>> at 0/0; shutdown FALSE
>> 2006- 12- 22 10:50:09 LOG: next transaction ID: 0/25144015; next
OID:
>> 140986
>> 2006- 12- 22 10:50:09 LOG: next MultiXactId: 12149; next
MultiXactOffset:
>> 24306
>> 2006- 12- 22 10:50:09 LOG: database system was not properly shut
down;
>> automatic recovery in progress
>> 2006- 12- 22 10:50:09 LOG: redo starts at 2E/8729A6E8
>>
>>
>> This has been running for 20 minutes. What can I do? Please help!
>>
>> --------------------------- (end of
broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>
> --------------------------- (end of
broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, jhaile@fastmail.fm
Subject: Re: URGENT: Out of disk space pg_xlog
In-Reply-To: <458BC058.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox