X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 335CE11F6623 for ; Thu, 20 Apr 2006 18:21:35 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 1.383 X-Spam-Level: * X-Spam-Status: No, score=1.383 required=5 tests=[AWL=0.129, HTML_MESSAGE=0.001, RCVD_NUMERIC_HELO=1.253] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 1oT7Bq7kv9FN for ; Thu, 20 Apr 2006 18:21:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.mi8.com (d01gw02.mi8.com [63.240.6.46]) by postgresql.org (Postfix) with ESMTP id D2F9211F662D for ; Thu, 20 Apr 2006 18:21:22 -0300 (ADT) Received: from 172.16.1.110 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D2)); Thu, 20 Apr 2006 17:21:08 -0400 X-Server-Uuid: 7829E76E-BB9E-4995-8473-3C0929DF7DD1 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.160]) by D01SMTP01.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 20 Apr 2006 17:21:07 -0400 Received: from 67.103.45.218 ([67.103.45.218]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.106]) with Microsoft Exchange Server HTTP-DAV ; Thu, 20 Apr 2006 21:21:07 +0000 User-Agent: Microsoft-Entourage/11.2.3.060209 Date: Thu, 20 Apr 2006 14:21:05 -0700 Subject: Re: Recovery will take 10 hours From: "Luke Lonergan" To: "Brendan Duddridge" , "Tom Lane" cc: "PostgreSQL Performance" Message-ID: Thread-Topic: [PERFORM] Recovery will take 10 hours Thread-Index: AcZkv6DRK/IS0fnUST+mkuka7iLJsgAALQ7M In-Reply-To: <47C1F053-EC91-4AEB-9F0B-B538716145D6@clickspace.com> MIME-Version: 1.0 X-OriginalArrivalTime: 20 Apr 2006 21:21:07.0966 (UTC) FILETIME=[56D0C9E0:01C664C0] X-WSS-ID: 685924BF2XS49388389-15-01 Content-Type: multipart/alternative; boundary=B_3228387665_3443491 X-Archive-Number: 200604/474 X-Sequence-Number: 18558 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3228387665_3443491 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Brendan, strace =ADp -c Then do a =B3CTRL-C=B2 after a minute to get the stats of system calls. - Luke On 4/20/06 2:13 PM, "Brendan Duddridge" wrote: > Hi Tom, >=20 > Do you mean do a kill -QUIT on the postgres process in order to > generate a stack trace? >=20 > Will that affect the currently running process in any bad way? And > where would the output go? stdout? >=20 > Thanks, >=20 > ____________________________________________________________________ > Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com >=20 > ClickSpace Interactive Inc. > Suite L100, 239 - 10th Ave. SE > Calgary, AB T2G 0V9 >=20 > http://www.clickspace.com >=20 > On Apr 20, 2006, at 2:17 PM, Tom Lane wrote: >=20 >> > Brendan Duddridge 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 i= t >>> >> be possible to kill the postgres process, tweak some parameter >>> >> somewhere and then start it up again? Or would we have to restore ou= r >>> >> 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 >> > >=20 >=20 >=20 > ---------------------------(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 >=20 >=20 --B_3228387665_3443491 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Re: [PERFORM] Recovery will take 10 hours Brend= an,

  strace –p <pid> -c

Then do a “CTRL-C” after a minute to get the stats of system ca= lls.

- Luke

On 4/20/06 2:13 PM, "Brendan Duddridge" <brendan@clickspace.co= m> 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@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 file= s
>> 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<= BR> > think.  Try strace'ing the recovery process to see what it's doin= g.
>
>> 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<= BR> > pretty sure you've fixed the performance issue.  Right at the mom= ent,
> I think this is a golden opportunity to study the performance of WAL > recovery --- it's not something we've tried to optimize particularly.<= BR> >
>            &nbs= p;          regards, tom l= ane
>
> ---------------------------(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 major= domo@postgresql.org so that your
       message can get through to the ma= iling list cleanly



--B_3228387665_3443491--