X-Original-To: pgsql-performance@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id AC6239259B3 for ; Wed, 28 May 2003 14:00:34 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 95072-08 for ; Wed, 28 May 2003 14:00:33 -0400 (EDT) Received: from utahisp.com (utahisp.com [66.239.12.3]) by developer.postgresql.org (Postfix) with ESMTP id 99D759259AA for ; Wed, 28 May 2003 14:00:31 -0400 (EDT) Received: from chad [63.230.8.76] by utahisp.com (SMTPD32-8.00) id A962132E0108; Wed, 28 May 2003 12:01:06 -0600 Message-ID: <02aa01c32542$ef61f730$32021aac@chad> From: "Chad Thompson" To: "Andrew Sullivan" , "pgsql-performance" References: <027901c32530$273839c0$32021aac@chad> <20030528162455.GG6637@libertyrms.info> Subject: Re: >24 hour restore Date: Wed, 28 May 2003 11:59:49 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Declude-Sender: chad@weblinkservices.com [63.230.8.76] X-Declude-Spoolname: Df962132e01081036.SMD X-Note: This E-mail was scanned by Declude JunkMail (www.declude.com) for spam. X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/239 X-Sequence-Number: 2018 > On Wed, May 28, 2003 at 09:12:23AM -0600, Chad Thompson wrote: > > > > It started off quick, but it got to the first table w/ any real data in it > > (only about 30k records) and acted like it was frozen. I left it running > > all night, it finished that table and started on others but it hasnt even > > gotten to the big tables (2 @ about 9 million records). At this pace it > > will take several days to finish the restore. > > This makes me think you have a trigger problem. You don't say what > version you're running, but my guess is that you need to disable all > your triggers, and remove all your indices, before you start loading > the data. Re-enable them afterwards. > > By building the schema first, then loading the data, you're spending > cycles running triggers &c. > This was my first thought. After about an hour of running, I stopped the process, edited the schema file to remove all the foreign keys and triggers. I then started it again. So there SHOULD be no triggers right now. UPDATE: I stopped the restore, before it was stopped, top showed postmaster using 17% CPU. After stopping I noticed that it DID fill my largest table (1.16 M tuples) over night. So I am editing the dump file to continue where it left off. ( vi is the only thing that is not choking on the 2.4 gig file) That is good news because that means it wont take 7-10 days to import, just 1-2. As for version (oops) my old version was 7.3.1 and I am moving to 7.3.2 Any other ideas? TIA Chad Oh, a bit off topic... I remember that I wanted to move the WAL files off of the raid but forgot to do it on start up. Can I do that now that the system is setup? Where would I find docs to tell me about that?