"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> before the system crash? The scenario we've seen in the past is
>>
>> * data partition out of space, so writes fail
>> * each time Postgres attempts a checkpoint, writes fail, so the
>> checkpoint fails. No data loss at this point, the dirty buffers
>> just stay in memory.
>> * pg_xlog bloats because we can't truncate away old segments

> So, at this point, if space is freed on the data partition somehow,
> Postgres recovers with no problems? (i.e.,, the database is still
> running and no requests have been terminated abnormally due to the space
> problems?)

Right, no committed transactions have been lost. Depending on what you
are doing, you might see individual transactions fail due to
out-of-space --- an INSERT/UPDATE that couldn't find free space within
its table would probably fail while trying to extend the table, and
anything requiring a large temp file would fail.

> Just to confirm what I would assume at this point -- non-committed
> transactions should roll back cleanly; it is reasonable to assume no
> corruption at this point?

Yeah, I would expect no problems.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Fri, 22 Dec 2006, Tom Lane wrote:

> Date: Fri, 22 Dec 2006 13:14:18 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
> Cc: Jeremy Haile <jhaile@fastmail.fm>, pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] URGENT: Out of disk space pg_xlog
>
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > As I understand it, the log space accumulates for the oldest transaction
> > which is still running, and all transactions which started after it.
>
> No, pg_xlog can be truncated as soon as a checkpoint occurs.

Even for currently running transactions ?

My understanding was that checkpoint was syncing data files for commited
transactions.

What happens to pg_xlogs when a transaction updates M of rows/tables and
runs for hours?
[snip]
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
regards
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

ohp@pyrenet.fr writes:
> On Fri, 22 Dec 2006, Tom Lane wrote:
>> No, pg_xlog can be truncated as soon as a checkpoint occurs.

> Even for currently running transactions ?

Yes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

ohp@pyrenet.fr wrote:
> On Fri, 22 Dec 2006, Tom Lane wrote:
>
> > Date: Fri, 22 Dec 2006 13:14:18 -0500
> > From: Tom Lane <tgl@sss.pgh.pa.us>
> > To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
> > Cc: Jeremy Haile <jhaile@fastmail.fm>, pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] URGENT: Out of disk space pg_xlog
> >
> > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > > As I understand it, the log space accumulates for the oldest transaction
> > > which is still running, and all transactions which started after it.
> >
> > No, pg_xlog can be truncated as soon as a checkpoint occurs.
>
> Even for currently running transactions ?
>
> My understanding was that checkpoint was syncing data files for commited
> transactions.

No, it syncs data files for all transactions, even those currently
running.

> What happens to pg_xlogs when a transaction updates M of rows/tables and
> runs for hours?

They get recycled as the update goes.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, Dec 22, 2006 at 07:47:05PM +0100, ohp@pyrenet.fr wrote:
>> No, pg_xlog can be truncated as soon as a checkpoint occurs.
> Even for currently running transactions ?

Isn't that the entire point of having checkpoints in the first place? :-)

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > As I understand it, the log space accumulates for the oldest transaction
> > which is still running, and all transactions which started after it.
>
> No, pg_xlog can be truncated as soon as a checkpoint occurs. If Jeremy
> wasn't using archive_command then the only possible explanation for
> bloated pg_xlog is that checkpoints were failing. Which is not unlikely
> if the *data* partition runs out of space. Were there gripes in the log
> before the system crash? The scenario we've seen in the past is
>
> * data partition out of space, so writes fail
> * each time Postgres attempts a checkpoint, writes fail, so the
> checkpoint fails. No data loss at this point, the dirty buffers
> just stay in memory.
> * pg_xlog bloats because we can't truncate away old segments
> * eventually pg_xlog runs out of space, at which point we PANIC
> and can't continue running the database
>
> Once you free some space on the data partition and restart, you should
> be good to go --- there will be no loss of committed transactions, since
> all the operations are in pg_xlog. Might take a little while to replay
> all that log though :-(

Amazing that all works. What I did not see is confirmation from the
user that the data directory filled up _before_ pg_xlog filled up.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thanks for your reply.

I learnt something again!
On Fri, 22 Dec 2006, Alvaro Herrera wrote:

> Date: Fri, 22 Dec 2006 17:25:43 -0300
> From: Alvaro Herrera <alvherre@commandprompt.com>
> To: ohp@pyrenet.fr
> Cc: Tom Lane <tgl@sss.pgh.pa.us>,
> Kevin Grittner <Kevin.Grittner@wicourts.gov>,
> Jeremy Haile <jhaile@fastmail.fm>, pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] URGENT: Out of disk space pg_xlog
>
> ohp@pyrenet.fr wrote:
> > On Fri, 22 Dec 2006, Tom Lane wrote:
> >
> > > Date: Fri, 22 Dec 2006 13:14:18 -0500
> > > From: Tom Lane <tgl@sss.pgh.pa.us>
> > > To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
> > > Cc: Jeremy Haile <jhaile@fastmail.fm>, pgsql-performance@postgresql.org
> > > Subject: Re: [PERFORM] URGENT: Out of disk space pg_xlog
> > >
> > > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > > > As I understand it, the log space accumulates for the oldest transaction
> > > > which is still running, and all transactions which started after it.
> > >
> > > No, pg_xlog can be truncated as soon as a checkpoint occurs.
> >
> > Even for currently running transactions ?
> >
> > My understanding was that checkpoint was syncing data files for commited
> > transactions.
>
> No, it syncs data files for all transactions, even those currently
> running.
>
> > What happens to pg_xlogs when a transaction updates M of rows/tables and
> > runs for hours?
>
> They get recycled as the update goes.
>
>

--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

> > Once you free some space on the data partition and restart, you should
> > be good to go --- there will be no loss of committed transactions, since
> > all the operations are in pg_xlog. Might take a little while to replay
> > all that log though :-(
>
> Amazing that all works. What I did not see is confirmation from the
> user that the data directory filled up _before_ pg_xlog filled up.

After I freed up space on the pg_xlog partition and restarted, it took
some time to replay all of the log (15-20 minutes) and everything
recovered with no data corruption! However, the theory about the data
partition filling up first didn't happen in my case. The data partition
was (and still is) less than 50% utilized. My pg_xlog files typically
run around 400MB, but with the long running update filled up the entire
10GB partition. (which is now a 70 GB partition)

So, I'm still not sure what caused the problem. When I get back to work
(or maybe sooner), I'll take a look in the PG logs and post anything
that looks suspicious here. Thanks for all of your comments and
suggestions. Even though I haven't figured out the root of the problem
yet, they've been very informative.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jeremy Haile wrote:
> > > Once you free some space on the data partition and restart, you should
> > > be good to go --- there will be no loss of committed transactions, since
> > > all the operations are in pg_xlog. Might take a little while to replay
> > > all that log though :-(
> >
> > Amazing that all works. What I did not see is confirmation from the
> > user that the data directory filled up _before_ pg_xlog filled up.
>
> After I freed up space on the pg_xlog partition and restarted, it took
> some time to replay all of the log (15-20 minutes) and everything
> recovered with no data corruption! However, the theory about the data
> partition filling up first didn't happen in my case. The data partition
> was (and still is) less than 50% utilized. My pg_xlog files typically
> run around 400MB, but with the long running update filled up the entire
> 10GB partition. (which is now a 70 GB partition)
>
> So, I'm still not sure what caused the problem. When I get back to work
> (or maybe sooner), I'll take a look in the PG logs and post anything
> that looks suspicious here. Thanks for all of your comments and
> suggestions. Even though I haven't figured out the root of the problem
> yet, they've been very informative.

The bottom line is that we know of now cases where a long-running
transaction would delay recycling of the WAL files, so there is
certainly something not understood here.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Sat, 2006-12-23 at 13:13 -0500, Bruce Momjian wrote:

> The bottom line is that we know of now cases where a long-running
> transaction would delay recycling of the WAL files, so there is
> certainly something not understood here.

We can see from all of this that a checkpoint definitely didn't occur.
Tom's causal chain was just one way that could have happened, there
could well be others.

I've noticed previously that a checkpoint can be starved out when trying
to acquire the CheckpointStartLock. I've witnessed a two minute delay
plus in obtaining the lock in the face of heavy transactions.

If wal_buffers is small enough, WAL write rate high enough and the
transaction rate high enough, a long queue can form for the
WALWriteLock, which ensures that the CheckpointStartLock would queue
indefinitely.

I've tried implementing a queueable shared lock for the
CheckpointStartLock. That helps the checkpoint, but it harms performance
of other transactions waiting to commit, so I let that idea go.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match