pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brendan Duddridge <brendan@clickspace.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Recovery will take 10 hours
Date: Thu, 20 Apr 2006 15:13:31 -0600
Message-ID: <47C1F053-EC91-4AEB-9F0B-B538716145D6@clickspace.com> (raw)
In-Reply-To: <29439.1145564220@sss.pgh.pa.us>
References: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com>
	<29439.1145564220@sss.pgh.pa.us>

Hi Tom,

Do you mean do a kill -QUIT on the postgres process in order to  
generate a stack trace?

Will that affect the currently running process in any bad way? And  
where would the output go? stdout?

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com

On Apr 20, 2006, at 2:17 PM, Tom Lane wrote:

> Brendan Duddridge <brendan@clickspace.com> writes:
>> We had a database issue today that caused us to have to restore to
>> our most recent backup. We are using PITR so we have 3120 WAL files
>> that need to be applied to the database.
>> After 45 minutes, it has restored only 230 WAL files. At this rate,
>> it's going to take about 10 hours to restore our database.
>> Most of the time, the server is not using very much CPU time or I/O
>> time. So I'm wondering what can be done to speed up the process?
>
> That seems a bit odd --- should be eating one or the other, one would
> think.  Try strace'ing the recovery process to see what it's doing.
>
>> If there were something we could do to speed up the process, would it
>> be possible to kill the postgres process, tweak some parameter
>> somewhere and then start it up again? Or would we have to restore our
>> base backup again and start over?
>
> You could start it up again, but it'd want to read through all the WAL
> it's already looked at, so I'd not recommend this until/unless you're
> pretty sure you've fixed the performance issue.  Right at the moment,
> I think this is a golden opportunity to study the performance of WAL
> recovery --- it's not something we've tried to optimize particularly.
>
> 			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
>





view thread (21+ messages)  latest in thread

Message-ID: <47C1F053-EC91-4AEB-9F0B-B538716145D6@clickspace.com>
Permalink:  ../47C1F053-EC91-4AEB-9F0B-B538716145D6@clickspace.com/
Also on:    postgresql.org/message-id/47C1F053-EC91-4AEB-9F0B-B538716145D6@clickspace.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: brendan@clickspace.com, tgl@sss.pgh.pa.us
  Subject: Re: Recovery will take 10 hours
  In-Reply-To: <47C1F053-EC91-4AEB-9F0B-B538716145D6@clickspace.com>
  References: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com> <29439.1145564220@sss.pgh.pa.us> <47C1F053-EC91-4AEB-9F0B-B538716145D6@clickspace.com>

* 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