Re: index file bloating still in 7.4 ?

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(at)lucent(dot)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(at)postgresql(dot)org
>
>
>--
>+--------------------------------------------------------+
>| Seum-Lim GAN email : slgan(at)lucent(dot)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(at)lucent(dot)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 |
+--------------------------------------------------------+