Skip site navigation (1) Skip section navigation (2)

Re: URGENT: Out of disk space pg_xlog

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Jeremy Haile" <jhaile(at)fastmail(dot)fm>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: URGENT: Out of disk space pg_xlog
Date: 2006-12-22 17:24:08
Message-ID: 458BC058.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: 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(dot)10592(dot)281708161(at)webmail(dot)messagingengine(dot)com>, "Jeremy
Haile"
<jhaile(at)fastmail(dot)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(at)fastmail(dot)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


In response to

Responses

pgsql-performance by date

Next:From: Jeremy HaileDate: 2006-12-22 17:30:25
Subject: Re: URGENT: Out of disk space pg_xlog
Previous:From: Simon RiggsDate: 2006-12-22 17:02:43
Subject: Re: URGENT: Out of disk space pg_xlog

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group