Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A75EF2E018B for ; Thu, 21 Feb 2008 14:11:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 02123-01-8 for ; Thu, 21 Feb 2008 14:11:06 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 263B42E00DA for ; Thu, 21 Feb 2008 14:09:35 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1LI9WS3013060; Thu, 21 Feb 2008 13:09:32 -0500 (EST) To: Guillaume Cottenceau cc: pgsql-performance@postgresql.org Subject: Re: 7 hrs for a pg_restore? In-reply-to: <87ablu5icl.fsf@mnc.ch> References: <200802191303.58840.doug@hunley.homeip.net> <47BB1C51.6010203@archonet.com> <19241.1203445378@sss.pgh.pa.us> <87ablu5icl.fsf@mnc.ch> Comments: In-reply-to Guillaume Cottenceau message dated "Thu, 21 Feb 2008 18:28:58 +0100" Date: Thu, 21 Feb 2008 13:09:32 -0500 Message-ID: <13059.1203617372@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/268 X-Sequence-Number: 28810 Guillaume Cottenceau writes: > I have made a comparison restoring a production dump with default > and large maintenance_work_mem. The speedup improvement here is > only of 5% (12'30 => 11'50). > Apprently, on the restored database, data is 1337 MB[1] and > indexes 644 MB[2][2]. Pg is 8.2.3, checkpoint_segments 3, > maintenance_work_mem default (16MB) then 512MB, shared_buffers > 384MB. It is rather slow disks (Dell's LSI Logic RAID1), hdparm > reports 82 MB/sec for reads. The main thing that jumps out at me is that boosting checkpoint_segments would probably help. I tend to set it to 30 or so (note that this corresponds to about 1GB taken up by pg_xlog). regards, tom lane