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 A2B0E11F6770 for ; Thu, 20 Apr 2006 17:17:09 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.123 X-Spam-Level: X-Spam-Status: No, score=0.123 required=5 tests=[AWL=0.123] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id XRvgDnAeeMrI for ; Thu, 20 Apr 2006 17:17:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C332411F66DC for ; Thu, 20 Apr 2006 17:17:03 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3KKH0k7029440; Thu, 20 Apr 2006 16:17:01 -0400 (EDT) To: Brendan Duddridge cc: PostgreSQL Performance Subject: Re: Recovery will take 10 hours In-reply-to: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com> References: <85A15B2E-F59C-4FEF-82D4-61F48ABDCDE5@clickspace.com> Comments: In-reply-to Brendan Duddridge message dated "Thu, 20 Apr 2006 13:29:46 -0600" Date: Thu, 20 Apr 2006 16:17:00 -0400 Message-ID: <29439.1145564220@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/470 X-Sequence-Number: 18554 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