pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brendan Duddridge <brendan@clickspace.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Recovery will take 10 hours
Date: Sun, 23 Apr 2006 22:46:35 -0600
Message-ID: <F823FC8E-70C4-41D0-A812-F2C7E2ECFF1E@clickspace.com> (raw)
In-Reply-To: <1145808368.3112.222.camel@localhost.localdomain>
References: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com>
	<1145808368.3112.222.camel@localhost.localdomain>

Hi Simon,

The backup with 3120 WAL files was a 2 day old base backup. We've moved
to a 1 day base backup now, but that'll still be 1600 WALs or so a day.
That will probably take 5 hours to restore I suspect. Unless we move to
2 or more base backups per day. That seems crazy though.

So how do you overlap the restore process with the retrieving of files?

Our restore command is:

restore_command = 'gunzip </wal_archive/%f.gz>%p'

If I change it to:

restore_command = 'gunzip </wal_archive/%f.gz>%p &'

to execute the restore command in the background, will that do the  
trick?

But I don't think the real problem was the retrieval of the files. It  
only
took maybe 1/2 a second to retrieve the file, but often took anywhere  
from
5 to 30 seconds to process the file. More so on the longer end of the  
scale.

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 23, 2006, at 10:06 AM, Simon Riggs wrote:

> On Thu, 2006-04-20 at 13:29 -0600, Brendan Duddridge wrote:
>
>> 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.
>
> How often are you taking base backups?
>
>> 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?
>
> You can improve the performance of a recovery by making your restore
> command overlap retrieval of files. The recovery process waits  
> while the
> restore command returns a file, so by doing an asynchronous  
> lookahead of
> one file you can be gunzipping the next file while the current one is
> being processed.
>
> I'll either document this better, or build an overlap into the restore
> command processing itself, so the script doesn't need to do this.
>
> -- 
>   Simon Riggs
>   EnterpriseDB          http://www.enterprisedb.com/
>
>
> ---------------------------(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
>





view thread (21+ messages)  latest in thread

Message-ID: <F823FC8E-70C4-41D0-A812-F2C7E2ECFF1E@clickspace.com>
Permalink:  ../F823FC8E-70C4-41D0-A812-F2C7E2ECFF1E@clickspace.com/
Also on:    postgresql.org/message-id/F823FC8E-70C4-41D0-A812-F2C7E2ECFF1E@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, simon@2ndquadrant.com
  Subject: Re: Recovery will take 10 hours
  In-Reply-To: <F823FC8E-70C4-41D0-A812-F2C7E2ECFF1E@clickspace.com>
  References: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com> <1145808368.3112.222.camel@localhost.localdomain> <F823FC8E-70C4-41D0-A812-F2C7E2ECFF1E@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