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 6B5B8D1B503 for ; Mon, 13 Oct 2003 14:05:58 +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 07554-01 for ; Mon, 13 Oct 2003 11:05:12 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 50287D1B4E5 for ; Mon, 13 Oct 2003 11:05:07 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h9DEBPcx011089 for ; Mon, 13 Oct 2003 19:41:25 +0530 Received: from daithan.intranet.pspl.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id h9DEBOwc011063; Mon, 13 Oct 2003 19:41:24 +0530 From: Shridhar Daithankar To: Seum-Lim Gan Subject: Re: Performance, vacuum and reclaiming space, fsm Date: Mon, 13 Oct 2003 19:37:14 +0530 User-Agent: KMail/1.5.2 References: <3F8A6F82.6030300@persistent.co.in> In-Reply-To: Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200310131937.14785.shridhar_daithankar@persistent.co.in> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/422 X-Sequence-Number: 4170 On Monday 13 October 2003 19:22, Seum-Lim Gan wrote: > I am not sure I can do the full vacuum. > If my system is doing updates in realtime and needs to be > ok 24 hours and 7 days a week non-stop, once I do > vacuum full, even on that table, that table will > get locked out and any quiery or updates that come in > will timeout. If you have 150MB type of data as you said last time, you could take a pg_dump of database, drop the database and recreate it. By all chances it will take less time than compacting a database from 2GB to 150MB. It does involve downtime but can't help it. Thats closet you can get. > Any suggestion on what to do besides shutting down to > do full vacuum ? Drop the indexes and recreate them. While creating the index, all the updates will be blocked anyways. > Peter Child also mentions there is indexing bugs. > Is this fixed in 7.3.4 ? I did notice after the database No. It is fixed in 7.4 and 7.4 is in beta still.. > grew in disk usage, its performance greatly decreases ! Obviously that is due to unnecessary IO it has to do. Thing is your database has reached a state that is really bad for it's operation. I strongly encourage you to recreate the database from backup, from scratch, tune postgresql properly and run autovacuum daemon from 7.4 source dir. Besides that you would need to reindex nightly or per 5-6 hour depending upon rate of insertion. Check http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html for performance tuning starter tips.. HTH Shridhar