Re: URGENT: Out of disk space pg_xlog - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: URGENT: Out of disk space pg_xlog
Date
Msg-id 458BC058.EE98.0025.0@wicourts.gov
Whole thread Raw
In response to Re: URGENT: Out of disk space pg_xlog  ("Jeremy Haile" <jhaile@fastmail.fm>)
Responses Re: URGENT: Out of disk space pg_xlog  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: "Simon Riggs"
Date:
Subject: Re: URGENT: Out of disk space pg_xlog
Next
From: "Jeremy Haile"
Date:
Subject: Re: URGENT: Out of disk space pg_xlog