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 1A53111F6254 for ; Thu, 20 Apr 2006 18:13:51 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.06 X-Spam-Level: X-Spam-Status: No, score=0.06 required=5 tests=[AWL=0.060] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id vWR0Qy2Syt4P for ; Thu, 20 Apr 2006 18:13:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.clickspace.com (router2.clickspace.com [65.110.166.227]) by postgresql.org (Postfix) with ESMTP id 10B6B11F628F for ; Thu, 20 Apr 2006 18:13:36 -0300 (ADT) Received: from localhost ([127.0.0.1]) by mail.clickspace.com (using TLSv1/SSLv3 with cipher RC4-SHA (128 bits)); Thu, 20 Apr 2006 15:13:35 -0600 In-Reply-To: <29439.1145564220@sss.pgh.pa.us> References: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com> <29439.1145564220@sss.pgh.pa.us> Mime-Version: 1.0 (Apple Message framework v749.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <47C1F053-EC91-4AEB-9F0B-B538716145D6@clickspace.com> Cc: PostgreSQL Performance Content-Transfer-Encoding: 7bit From: Brendan Duddridge Subject: Re: Recovery will take 10 hours Date: Thu, 20 Apr 2006 15:13:31 -0600 To: Tom Lane X-Mailer: Apple Mail (2.749.3) X-Archive-Number: 200604/471 X-Sequence-Number: 18555 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 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 >