Re: index file bloating still in 7.4 ?

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 |
+--------------------------------------------------------+