X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3182B9DC86C for ; Mon, 13 Mar 2006 20:48:36 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01292-06 for ; Mon, 13 Mar 2006 20:48:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from linuxworld.com.au (unknown [203.34.46.50]) by postgresql.org (Postfix) with ESMTP id 680759DCC73 for ; Mon, 13 Mar 2006 20:48:32 -0400 (AST) Received: from linuxworld.com.au (IDENT:swm@localhost.localdomain [127.0.0.1]) by linuxworld.com.au (8.13.2/8.13.2) with ESMTP id k2E0mToE016345; Tue, 14 Mar 2006 11:48:29 +1100 Received: from localhost (swm@localhost) by linuxworld.com.au (8.13.2/8.13.2/Submit) with ESMTP id k2E0mS1q016342; Tue, 14 Mar 2006 11:48:28 +1100 X-Authentication-Warning: linuxworld.com.au: swm owned process doing -bs Date: Tue, 14 Mar 2006 11:48:28 +1100 (EST) From: Gavin Sherry X-X-Sender: swm@linuxworld.com.au To: Chris cc: pgsql-performance@postgresql.org Subject: Re: import performance In-Reply-To: <441610EB.7070001@gmail.com> Message-ID: References: <441610EB.7070001@gmail.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.02 required=5 tests=[AWL=0.020] X-Spam-Score: 0.02 X-Spam-Level: X-Archive-Number: 200603/174 X-Sequence-Number: 17611 On Tue, 14 Mar 2006, Chris wrote: > Hi all, > > I'm trying to work out why my 8.1 system is slower than my 7.4 system > for importing data. > > The import is a lot of "insert into" commands - it's a converted > database from another system so I can't change it to copy commands. > > > My uncommented config options: > > > autovacuum = off > > bgwriter_all_maxpages = 15 > bgwriter_all_percent = 10.0 The above is a bit high. > bgwriter_delay = 2000 This too. > bgwriter_lru_maxpages = 10 > bgwriter_lru_percent = 5.0 > > checkpoint_segments = 10 > > commit_delay = 100000 > commit_siblings = 500 Way too high > > temp_buffers = 500 > > wal_buffers = 16 Make this at least 64. > > max_connections = 16 > > shared_buffers = 256 Make this higher too. If this is a dedicated machine with 512 MB of ram, set it to something like 125000. You may need to adjust shared memory settings for your operating system. See the manual for details. Thanks, Gavin