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 E374AD1B4E5 for ; Wed, 10 Sep 2003 18:32:26 +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 50643-02 for ; Wed, 10 Sep 2003 15:31:57 -0300 (ADT) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by svr1.postgresql.org (Postfix) with ESMTP id A1CF1D1B4E8 for ; Wed, 10 Sep 2003 15:31:55 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 3.35 #1) id 19x9kx-000N7V-0Z; Wed, 10 Sep 2003 19:31:55 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id BAA2A16375; Wed, 10 Sep 2003 19:31:54 +0100 (BST) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 292841596B; Wed, 10 Sep 2003 19:31:54 +0100 (BST) From: Richard Huxton To: aturner@neteconomist.com, pgsql-performance@postgresql.org Subject: Re: Upgrade Woes Date: Wed, 10 Sep 2003 19:31:53 +0100 User-Agent: KMail/1.5 References: <20030910175340.GC5506@neteconomist.com> In-Reply-To: <20030910175340.GC5506@neteconomist.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200309101931.53746.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/174 X-Sequence-Number: 3540 On Wednesday 10 September 2003 18:53, aturner@neteconomist.com wrote: > Hi, > > My name is Alex Turner and I work for a small Tech company in Pottstown PA. > We run Postgresql on a number of systems for a variety of different > applications, and it has been a joy to deal with all around, working fast > and reliably for over 2 years. > > We recently upgraded from RedHat 7.2 to RedHat 9.0, and we are running > Postgres 7.3.2 on our Proliant ML370 (Raid 1 2x18 10k, and Raid 5 3x36 10k, > 2x866 PIII, 2GB RAM). [snip] > I have noticed that whilst inserts seem to be slower than before, the > vacuum full doesn't seem to take as long overall. > > postgresql.conf is pretty virgin, and we run postmaster with -B512 -N256 > -i. /var/lib/pgsql/data is a symlink to /eda/data, /eda being the mount > point for the Raid 5 array. First things first then, go to: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php and read the item on Performance Tuning and the commented postgresql.conf > the database isn't huge, storing about 30000 properties, and the largest > table is 2.1 Million rows for property features. The dump file is only > 221MB. Alas, I did not design the schema, but I have made several 'tweaks' > to it to greatly improve read performance allowing us to be the fastest > provider in the Tristate area. Unfortunately the Job starts at 01:05 > (thats the earliest the dump is available) and runs until completion > finishing with a vacuum full. The vacuum full locks areas of the database > long enough that our service is temporarily down. At the worst point, the > vacuum full was starting after 09:00, which our customers didn't > appreciate. You might be able to avoid a vacuum full by tweaking the *fsm* settings to be able to cope with activity. > I'm wondering if there is anything I can do with postgres to allieviate > this problem. Either upgrading to 7.3.4 (although I tried 7.3.3 for > another app, and we had to roll back to 7.3.2 because of performance > problems), Hmm - can't think what would have changed radically between 7.3.2 and 7.3.3, upgrading to .4 is probably sensible. [snip] > Any help/suggestions would be grealy appreciated, You say that each insert/update is a separate transaction. I don't know how much "bad" data you get in the dump, but you might be able to do something like: 1. Set batch size to 128 items 2. Read batch-size rows from the dump 3. Try to insert/update the batch. If it works, move along by the size of the batch and back to #1 4. If batch-size=1, record error, move along one row and back to #1 5. If batch-size>1, halve batch-size and go back to #3 Your initial batch-size will depend on how many errors there are (but obviously use a power of 2). You could also run an ordinary vacuum every 1000 rows or so (number depends on your *fsm* settings as mentioned above). You might also want to try a REINDEX once a night/week too. -- Richard Huxton Archonet Ltd