X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8ACC1D1B542 for ; Fri, 17 Oct 2003 15:12:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 59011-05 for ; Fri, 17 Oct 2003 12:12:03 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 88608D1B532 for ; Fri, 17 Oct 2003 12:12:02 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h9HFJQmx014826 for ; Fri, 17 Oct 2003 20:49:26 +0530 Received: from persistent.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 h9HFJPPI014798; Fri, 17 Oct 2003 20:49:25 +0530 Message-ID: <3F9006BF.40906@persistent.co.in> Date: Fri, 17 Oct 2003 20:41:59 +0530 From: Shridhar Daithankar Organization: Persistent Systems Pvt. Ltd. User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5b) Gecko/20030917 Thunderbird/0.3a X-Accept-Language: en-us, en MIME-Version: 1.0 To: Rob Nagler Cc: pgsql-performance@postgresql.org Subject: Re: vacuum locking References: <16272.318.870000.93584@gargle.gargle.HOWL> In-Reply-To: <16272.318.870000.93584@gargle.gargle.HOWL> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/479 X-Sequence-Number: 4227 Rob Nagler wrote: > It seems a simple "vacuum" (not full or analyze) slows down the > database dramatically. I am running vacuum every 15 minutes, but it > takes about 5 minutes to run even after a fresh import. Even with > vacuuming every 15 minutes, I'm not sure vacuuming is working > properly. > > There are a lot of updates. The slowest relation is the primary key > index, which is composed of a sequence. I've appended a csv with the > parsed output from vacuum. The page counts are growing way too fast > imo. I believe this is caused by the updates, and index pages not > getting re-used. The index values aren't changing, but other values > in the table are. You should try 7.4 beta and pg_autovacuum which is a contrib module in CVS tip. It works with 7.3 as well. Major reason for 7.4 is, it fixes index growth in vacuum. So if your database is fit, it will stay that way with proper vacuuming. > > Any suggestions how to make vacuuming more effective and reducing the > time it takes to vacuum? I'd settle for less frequent vacuuming or > perhaps index rebuilding. The database can be re-imported in about an > hour. Make sure that you have FSM properly tuned. Bump it from defaults to suit your needs. I hope you have gone thr. this page for general purpose setting. http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > > Rob > ---------------------------------------------------------------- > Spacing every 15 minutes > Pages,Tuples,Deleted > 7974,1029258,1536 > 7979,1025951,4336 > 7979,1026129,52 > 7979,1025618,686 Assuming those were incremental figures, largest you have is ~8000 tuples per 15 minutes and 26 pages. I think with proper FSM/shared buffers/effective cache and a pg_autovacuum with 1 min. polling interval, you could end up in lot better shape. Let us know if it works. Shridhar