Re: 7.3.1 takes long time to vacuum table? - Mailing list pgsql-general

Hi Tom, Martijn, Shridhar,

> Yeah.  Also, I don't suppose you made that a VACUUM VERBOSE and kept
the
> output?  It'd be interesting to see which stages took the most time.
>
>             regards, tom lane

I've got the complete set of timings for the table on our dev box. I'm
afraid the vacuum wasn't run in verbose mode so I can't give you any
more information :(. This information is based on the 600Mb table on our
dev system.

Time to vacuum full:
    Total:                            100m

Time to restore by copying to another table:
    SELECT INTO
40s
    CREATE INDEX 1 (bigint) using btree            59s
    CREATE INDEX 2 (int) using btree            27s
    CREATE INDEX 3 (bigint) using btree            32s
    CREATE INDEX 4 (bigint) using btree            24s
    CREATE INDEX 5 (varchar) using btree        3m 2s
    CREATE INDEX 6 (txtidx) using gist            12m 58s
    CREATE INDEX 7 (txtidx) using gist            31m 20s

    Total:                            51m 22s


OK, so my initial estimate of rebuilding in 10mins was way out because I
forgot about the 2 massive gist fti indexes I had on the table - doh.
However, I feel that the times are still meaningful in that I now have a
replica of the table at 400Mb (down by 200Mb) in just over half the time
that the vacuum full took to do the same job.

We stopped our vacuum on our live system at the 55hr stage whilst it was
still going - it took about another hour from sending the cancel request
until the vacuum stopped. We then deleted several indexes to claim back
enough Gb to hold a second copy of the table and we've just completed
the SELECT INTO into a new table.

And the result? It has taken a total of 1h 45m to generate a copy! Given
that we are rebuilding the table *WITHOUT* the large gist indexes on our
dev version, I guess that it would only be a matter of several hours
before we can rebuild the indexes back up on the table and be using it
again.

I hope that this goes some way to showing that implementing a new type
of vacuum feature, perhaps similar in working to the one suggested
earlier in the thread, would be greatly appreciated by people with
databases even more than 0.5G in size. I would gladly support/help out
anyone who felt they could implement such a feature in this way.


Cheers,

Sparks.


---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.