Re: Recovery will take 10 hours

Brendan,

strace ­p <pid> -c

Then do a ³CTRL-C² after a minute to get the stats of system calls.

- Luke

On 4/20/06 2:13 PM, "Brendan Duddridge" <brendan(at)clickspace(dot)com> wrote:

> 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(at)clickspace(dot)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(at)clickspace(dot)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
>> >
>
>
>
> ---------------------------(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
>
>