Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EB58F9F9DB7 for ; Tue, 21 Aug 2007 20:16:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76981-03 for ; Tue, 21 Aug 2007 20:16:02 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.187]) by postgresql.org (Postfix) with ESMTP id C8B349F9935 for ; Tue, 21 Aug 2007 20:16:01 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id l15so1275474rvb for ; Tue, 21 Aug 2007 16:16:00 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Xaw1SN0XN8yKwwD+2GWRHLteOF2mnvGetmCzIACmVqncDjGT7AJ0Mxo1IJpmO01SC9nwo6vl2OOCkAG9CUUcI9GcsIhbWowqH0PV5FqdPSwUyeF7uBiBf7sk2y9fFdCxfV8e1XBCTREVM8nwVl+3OLIPtVj1xtkZQ9/VYhDVGSM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=s8O/CtwTz3EVJVcGMtF/cdfGlCsiN0NTA991y5KbylP3/e5Hd1fJGPPpTu9FiMbzhcVaNUIZZqnMvL7Z26/14+KVmCYj5nlp+4rNbaHZ1nojKTBWoJgsyTjzh7USHFqiWDeMJqKwffAzAh63MFXbqBY2FeIdERnc8/kSDNro/gA= Received: by 10.141.210.5 with SMTP id m5mr3711427rvq.1187738160287; Tue, 21 Aug 2007 16:16:00 -0700 (PDT) Received: by 10.141.98.12 with HTTP; Tue, 21 Aug 2007 16:16:00 -0700 (PDT) Message-ID: Date: Tue, 21 Aug 2007 18:16:00 -0500 From: "Scott Marlowe" To: "Sachchida Ojha" Subject: Re: Autovacuum is running forever Cc: "Michael Glaesemann" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/226 X-Sequence-Number: 26356 On 8/21/07, Sachchida Ojha wrote: > Thanks to all of you. I have changed the settings and reloaded the > config. Let me run this system overnight. I will update this forum if > new settings works for me. I am also asking management to upgrade the > hardware. You need to run vacuum verbose on the database (not an individual table) and note the output at the end. It will tell you how bloated your current db is. If vacuums have been delayed for too long, you may need to vacuum full and / or reindex the bloated tables and indexes to reclaim the lost space. Assuming that there's not too much dead space, or that if there is you've used vacuum full / reindexdb to reclaim it, then vacuum running regularly and in the background should fix this issue... The output of vacuum verbose you're looking for is like this: DETAIL: A total of 2096 page slots are in use (including overhead). 2096 page slots are required to track all free space. Current limits are: 20000 page slots, 1000 relations, using 182 KB. If it comes back with some huge number for page slots (like in the millions) needed to track all the dead tuples you'll need that vacuum full / reindex. A certain amount of dead space is ok, even a good thing, since you don't have to extend your table / index files to insert. 10-30% dead space is normal. anything around 100% or heading up from there is bad. You'll also want to look through the rest of the vacuum verbose output for things like this: INFO: vacuuming "abc.zip_test" INFO: index "zip_test_pkey" now contains 1000000 row versions in 3076 pages DETAIL: 8589 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.37s/0.23u sec elapsed 28.23 sec. INFO: "zip_test": removed 8589 row versions in 55 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "zip_test": found 8589 removable, 1000000 nonremovable row versions in 6425 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 1.36s/0.34u sec elapsed 100.52 sec. If the number of rows removed and the pages they held were a large percentage of the table, then you'll likely need to reindex them to get the space back. Or cluster on an index.