X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4466FDB808
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 14 Nov 2005 19:54:18 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 28683-07-6
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 14 Nov 2005 23:54:11 +0000 (GMT)
X-Greylist: delayed 02:12:39.802749 by SQLgrey-
Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25])
	by svr1.postgresql.org (Postfix) with ESMTP id 4483EDB7DF
	for <pgsql-performance@postgresql.org>;
	Mon, 14 Nov 2005 19:54:06 -0400 (AST)
Received: from toad.mtbrook.bozemanpass.com (toad.mtbrook.bozemanpass.com
	[69.145.82.195])
	by svr2.postgresql.org (Postfix) with ESMTP id E9CB9F0B6E
	for <pgsql-performance@postgresql.org>;
	Mon, 14 Nov 2005 21:41:27 +0000 (GMT)
Received: from [69.145.82.218] (unknown [69.145.82.218])
	by toad.mtbrook.bozemanpass.com (Postfix) with ESMTP id 45BBE1102E6;
	Mon, 14 Nov 2005 13:41:22 -0800 (PST)
Message-ID: <43790489.4030803@boreham.org>
Date: Mon, 14 Nov 2005 14:41:29 -0700
From: David Boreham <david_list@boreham.org>
Reply-To: david_list@boreham.org
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: "Piccarello, James (James)" <jpicc@avaya.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres recovery time
References: 
 <16F9BDD39536704DA7BFB3172BF191720355ED84@nj7460avexu2.global.avaya.com>
In-Reply-To: 
 <16F9BDD39536704DA7BFB3172BF191720355ED84@nj7460avexu2.global.avaya.com>
Content-Type: multipart/alternative;
	boundary="------------000108060204090205000808"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001]
X-Spam-Score: 0.001
X-Spam-Level: 
X-Archive-Number: 200511/233
X-Sequence-Number: 15490

This is a multi-part message in MIME format.
--------------000108060204090205000808
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Piccarello, James (James) wrote:

> Does anyone know what factors affect the recovery time of postgres if 
> it does not shutdown cleanly? With the same size database I've seen  
> times from a few seconds to a few minutes. The longest time was 33 
> minutes. The 33 minutes was after a complete system crash and reboot 
> so there are a lot of other things going on as well. 125 seconds was 
> the longest time I could reproduce by just doing a kill -9 on postmaster.
>
> Is it the size of the transaction log? The dead space in files?
>
I don't know much about postgresql, but typically WAL mechanisms
will exhibit recovery times that are bounded by the amount of log record
data written since the last checkpoint. The 'worst' case will be where
you have continuous writes to the database and a long checkpoint
interval. In that case many log records must be replayed into the
data files upon recovery. The 'best' case would be zero write transactions
since the last checkpoint. In that case recovery would be swift since
there are no live records to recover. In your tests you are probably
exercising this 'best' or near best case.



--------------000108060204090205000808
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Piccarello, James (James) wrote:
<blockquote
 cite="mid16F9BDD39536704DA7BFB3172BF191720355ED84@nj7460avexu2.global.avaya.com"
 type="cite">
  <meta http-equiv="Content-Type" content="text/html; ">
  <meta name="Generator" content="MS Exchange Server version 6.0.6603.0">
  <title>Postgres recovery time</title>
<!-- Converted from text/rtf format -->
  <p><font face="Arial" size="2">Does anyone know what factors affect
the recovery time of postgres if it does not shutdown cleanly? With the
same size database I've seen&nbsp; times from a few seconds to a few
minutes. The longest time was 33 minutes. The 33 minutes was after a
complete system crash and reboot so there are a lot of other things
going on as well. 125 seconds was the longest time I could reproduce by
just doing a kill -9 on postmaster. </font></p>
  <p><font face="Arial" size="2">Is it the size of the transaction log?
The dead space in files? </font>
  </p>
</blockquote>
<font size="2"><font face="Arial">I don't know much about postgresql,
but typically WAL mechanisms<br>
will exhibit recovery times that are bounded by the amount of log record<br>
data written since the last checkpoint. The 'worst' case will be where <br>
you have continuous writes to the database and a long checkpoint<br>
interval. In that case many log records must be replayed into the<br>
data files upon recovery. The 'best' case would be zero write
transactions<br>
since the last checkpoint. In that case recovery would be swift since<br>
there are no live records to recover. In your tests you are probably<br>
exercising this 'best' or near best case. <br>
<br>
<br>
</font></font>
</body>
</html>

--------------000108060204090205000808--