From: | Brendan Duddridge <brendan(at)clickspace(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Recovery will take 10 hours |
Date: | 2006-04-24 04:46:35 |
Message-ID: | F823FC8E-70C4-41D0-A812-F2C7E2ECFF1E@clickspace.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)clickspace(dot)com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
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(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>
From | Date | Subject | |
---|---|---|---|
Next Message | Matteo Beccati | 2006-04-24 05:36:57 | Re: Inactive memory Grows unlimited |
Previous Message | Tom Lane | 2006-04-24 03:32:39 | Re: Slow deletes in 8.1 when FKs are involved |