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 CFE2FD1B518 for ; Mon, 20 Oct 2003 16:04:11 +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 48240-05 for ; Mon, 20 Oct 2003 13:03:41 -0300 (ADT) Received: from ihemail1.firewall.lucent.com (ihemail1.lucent.com [192.11.222.161]) by svr1.postgresql.org (Postfix) with ESMTP id A0669D1B51E for ; Mon, 20 Oct 2003 13:03:35 -0300 (ADT) Received: from ihmail.ih.lucent.com (h135-1-218-70.lucent.com [135.1.218.70]) by ihemail1.firewall.lucent.com (Switch-2.2.8/Switch-2.2.0) with ESMTP id h9KG2oP22694; Mon, 20 Oct 2003 11:02:59 -0500 (CDT) Received: from [192.168.10.52] (USS-Excelsior.ih.lucent.com [135.185.171.70]) by ihmail.ih.lucent.com (8.11.7+Sun/EMS-1.5 sol2) id h9KG2mt07102; Mon, 20 Oct 2003 11:02:49 -0500 (CDT) Mime-Version: 1.0 X-Sender: slgan@135.1.218.70 Message-Id: In-Reply-To: <200310191204.23888.josh@agliodbs.com> References: <19692.1066578442@sss.pgh.pa.us> <200310191204.23888.josh@agliodbs.com> Date: Mon, 20 Oct 2003 11:04:43 -0500 To: Josh Berkus , Tom Lane From: Seum-Lim Gan Subject: Re: index file bloating still in 7.4 ? Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset="us-ascii" ; format="flowed" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/509 X-Sequence-Number: 4257 Hi Josh, Tom, OK. As I understand it, vacuum does not release the space used by the index file. However, it should be able to reuse the space for indexing. I have observed that during initial updates of the table, the index file did not grow and was steady but it did not last long and keeps growing afterwards. Vacuum/vacuum analyze did not help. In all the update testing, vacuum analyze was done every 1 minute. Tom, something caught your attention the last time. Any insight so far ? Is it a bug ? Thanks. Gan Tom Lane wrote: Seum-Lim Gan writes: > vacuum verbose analyze dsperf_rda_or_key; > INFO: vacuuming "scncraft.dsperf_rda_or_key" > INFO: index "dsperf242_1105" now contains 300000 row versions in 12387 pages > DETAIL: 3097702 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. Hm, interesting that you deleted 90% of the entries and still had no empty index pages at all. What was the pattern of your deletes and/or updates with respect to this index's key? > However, when I check the disk space usage, it has not changed. It won't in any case. Plain VACUUM is designed for maintaining a steady-state level of free space in tables and indexes, not for returning major amounts of space to the OS. For that you need more-invasive operations like VACUUM FULL or REINDEX. regards, tom lane At 12:04 pm -0700 2003/10/19, Josh Berkus wrote: >Gan, > >> Oh, so in order to reclaim the disk space, we must run >> reindex or vacuum full ? >> This will lock out the table and we won't be able to do anything. >> Looks like this is a problem. It means we cannot use it for >> 24x7 operations without having to stop the process and do the vacuum full >> and reindex. Is there anything down the road that these operations >> will not lock out the table ? > >I doubt it; the amount of page-shuffling required to reclaim 90% of the space >in an index for a table that has been mostly cleared is substantial, and >would prevent concurrent access. > >Also, you seem to have set up an impossible situation for VACUUM. If I'm >reading your statistics right, you have a large number of threads accessing >most of the data 100% of the time, preventing VACUUM from cleaning up the >pages. This is not, in my experience, a realistic test case ... there are >peak and idle periods for all databases, even webservers that have been >slashdotted. > >-- >Josh Berkus >Aglio Database Solutions >San Francisco > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- +--------------------------------------------------------+ | Seum-Lim GAN email : slgan@lucent.com | | Lucent Technologies | | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA. fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | +--------------------------------------------------------+