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 52FAA3A4BB6 for ; Mon, 13 Dec 2004 19:21:20 +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 37373-07 for ; Mon, 13 Dec 2004 19:21:09 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E304D3A637D for ; Mon, 13 Dec 2004 19:21:09 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iBDJL4HK027281; Mon, 13 Dec 2004 14:21:04 -0500 (EST) To: Josh Berkus Cc: pgsql-performance@postgresql.org, Vivek Khera Subject: Re: pg_restore taking 4 hours! In-reply-to: <200412131043.28465.josh@agliodbs.com> References: <41ADA82A.7070405@carvalhaes.net> <200412131025.54587.josh@agliodbs.com> <83210A57-4D35-11D9-9C22-000A9578CFCC@kcilink.com> <200412131043.28465.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Mon, 13 Dec 2004 10:43:28 -0800" Date: Mon, 13 Dec 2004 14:21:04 -0500 Message-ID: <27280.1102965664@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/169 X-Sequence-Number: 9544 Josh Berkus writes: > 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. Actually I think the problem is specifically that you get checkpoints too often if either checkpoint_timeout or checkpoint_segments is too small. A checkpoint is expensive both directly (the I/O it causes) and indirectly (because the first update of a particular data page after a checkpoint causes the whole page to be logged in WAL). So keeping them spread well apart is a Good Thing, as long as you understand that a wider checkpoint spacing implies a longer time to recover if you do suffer a crash. I think 8.0's bgwriter will considerably reduce the direct cost of a checkpoint (since not so many pages will be dirty when the checkpoint happens) but it won't do a thing for the indirect cost. regards, tom lane