From: Seum-Lim Gan <slgan@lucent.com>
To: Josh Berkus <josh@agliodbs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: index file bloating still in 7.4 ?
Date: Mon, 20 Oct 2003 16:14:09 -0500
Message-ID: <p0510030cbbb9ff79c606@[192.168.10.52]> (raw)
In-Reply-To: <p05100307bbb9b21e995f@[192.168.10.52]>
References: <p0510030cbbb7667a7f87@[192.168.10.52]>
<19692.1066578442@sss.pgh.pa.us>
<p05100318bbb86e62801a@[192.168.10.52]>
<200310191204.23888.josh@agliodbs.com>
<p05100307bbb9b21e995f@[192.168.10.52]>
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 <slgan@lucent.com> 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 |
+--------------------------------------------------------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: slgan@lucent.com, josh@agliodbs.com, tgl@sss.pgh.pa.us
Subject: Re: index file bloating still in 7.4 ?
In-Reply-To: <p0510030cbbb9ff79c606@[192.168.10.52]>
References: <p0510030cbbb7667a7f87@[192.168.10.52]> <19692.1066578442@sss.pgh.pa.us> <p05100318bbb86e62801a@[192.168.10.52]> <200310191204.23888.josh@agliodbs.com> <p05100307bbb9b21e995f@[192.168.10.52]> <p0510030cbbb9ff79c606@[192.168.10.52]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox