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 4E70ED1B508 for ; Mon, 20 Oct 2003 21:13:23 +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 06336-05 for ; Mon, 20 Oct 2003 18:12:54 -0300 (ADT) Received: from hoemail1.firewall.lucent.com (hoemail1.lucent.com [192.11.226.161]) by svr1.postgresql.org (Postfix) with ESMTP id 4837DD1B51B for ; Mon, 20 Oct 2003 18:12:51 -0300 (ADT) Received: from ihmail.ih.lucent.com (h135-1-218-70.lucent.com [135.1.218.70]) by hoemail1.firewall.lucent.com (Switch-2.2.8/Switch-2.2.0) with ESMTP id h9KLCF424495; Mon, 20 Oct 2003 16:12:23 -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 h9KLCEt16966; Mon, 20 Oct 2003 16:12:14 -0500 (CDT) Mime-Version: 1.0 X-Sender: slgan@135.1.218.70 Message-Id: In-Reply-To: References: <19692.1066578442@sss.pgh.pa.us> <200310191204.23888.josh@agliodbs.com> Date: Mon, 20 Oct 2003 16:14:09 -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/515 X-Sequence-Number: 4263 Hi Tom, Josh, We tried one more thing: with the table not being updated at all and we did vacuum. Each time a vacuum is done, the index file becomes bigger. This is probably what is contributing to the index file growing as well. Thanks. Gan At 11:04 am -0500 2003/10/20, Seum-Lim Gan wrote: >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 | >+--------------------------------------------------------+ > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- +--------------------------------------------------------+ | 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 | +--------------------------------------------------------+