X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0E917D1B4FE for ; Wed, 17 Sep 2003 20:16:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 94921-05 for ; Wed, 17 Sep 2003 17:15:53 -0300 (ADT) Received: from localhost.localdomain (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id 7867BD1B4EB for ; Wed, 17 Sep 2003 17:15:46 -0300 (ADT) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.8/8.12.8) with ESMTP id h8HJ7Dqd026386; Wed, 17 Sep 2003 15:07:13 -0400 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h8HKFkZ02372; Wed, 17 Sep 2003 16:15:46 -0400 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id Q65SMD2H; Wed, 17 Sep 2003 16:15:44 -0400 Subject: Re: restore time: sort_mem vs. checkpoing_segments From: Robert Treat To: Vivek Khera Cc: pgsql-performance@postgresql.org In-Reply-To: <16230.4066.565194.387700@yertle.int.kciLink.com> References: <16230.4066.565194.387700@yertle.int.kciLink.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 17 Sep 2003 16:15:46 -0400 Message-Id: <1063829746.25694.91.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/248 X-Sequence-Number: 3614 On Mon, 2003-09-15 at 15:15, Vivek Khera wrote: > And the winner is... checkpoint_segments. > > Restore of a significanly big database (~19.8GB restored) shows nearly > no time difference depending on sort_mem when checkpoint_segments is > large. There are quite a number of tables and indexes. The restore > was done from a pg_dump -Fc dump of one database. > > All tests with 16KB page size, 30k shared buffers, sort_mem=8192, PG > 7.4b2 on FreeBSD 4.8. hmm... i wonder what would happen if you pushed your sort_mem higher... on some of our development boxes and upgrade scripts, i push the sort_mem to 102400 and sometimes even higher depending on the box. this really speeds up my restores quit a bit (and is generally safe as i make sure there isn't any other activity going on at the time) another thing i like to do is turn of fsync, as if the system crashes in the middle of reload i'm pretty sure i'd be starting all over anyway... Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL