X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A77633A6564 for ; Mon, 13 Dec 2004 18:44:03 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26042-02 for ; Mon, 13 Dec 2004 18:43:58 +0000 (GMT) Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by svr1.postgresql.org (Postfix) with ESMTP id 7FC403A6490 for ; Mon, 13 Dec 2004 18:43:58 +0000 (GMT) Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6781685; Mon, 13 Dec 2004 10:45:32 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: Re: pg_restore taking 4 hours! Date: Mon, 13 Dec 2004 10:43:28 -0800 User-Agent: KMail/1.6.2 Cc: Vivek Khera References: <41ADA82A.7070405@carvalhaes.net> <200412131025.54587.josh@agliodbs.com> <83210A57-4D35-11D9-9C22-000A9578CFCC@kcilink.com> In-Reply-To: <83210A57-4D35-11D9-9C22-000A9578CFCC@kcilink.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200412131043.28465.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/168 X-Sequence-Number: 9543 Vivek, > Do I need a correspondingly large checkpoint timeout then? Or does > that matter much? Yes, you do. > And does this advice apply if the pg_xlog is on the same RAID partition > (mine currently is not, but perhaps will be in the future) Not as much, but it's still a good idea to serialize the load. With too few segments, you get a pattern like: Fill up segments Write to database Recycle segments Fill up segments Write to database Recycle segments etc. Compared to doing it in one long run of a single cycle, considerble efficiency is lost. With a proper 2-array setup, the segments become like a write buffer for the database, and you want that buffer as large as you can afford in order to prevent buffer cycling from interrupting database writes. BTW, for members of the studio audience, checkpoint_segments of 256 is about 8GB. -- Josh Berkus Aglio Database Solutions San Francisco