Re: Question about VACUUM

On Sat, Oct 25, 2008 at 9:21 AM, Joey K. <pguser(at)gmail(dot)com> wrote:
> Hello all,
>
> I inherited a 8.1.x database of size 200GB. This database hasn't been
> maintained much (autovac is enabled though).
>
> I been trying to VACUUM this db for the past few days for a couple of
> hours/day. The server runs 24x7 so continuous maintenance is not possible
> (this is the last resort).

I'm not sure what you mean here. With the settings you have available
for vacuum costing, you can run vacuums almost continuously with no
serious impact on server performance.

>
> My questions are,
>
>> Is it possible to estimate how long VACUUM on a table might take?

Only through trial and error and a bit of looking at thesize of the
files and how much dead space and how many indexes you have. etc...

>> The table size is growing as "VACUUM" is being performed. I assume I need
>> reindex after VACUUM is complete.

Yes, vacuum reclaims no space in its own right. It marks pages as
available. Until its done, the pages aren't available, so the table
must grow while the first vacuum to free up space is being run.

>> I run VACUUM from psql and I Ctrl-C it to turn it off is this acceptable?

Yes. If I rememberate correctificantly, it will leave bloated indexes
behind if its a vacuum full.

> The server is a Intel Xeon 3.0GHz with 4GB RAM and RAID-5 (Yes I know).

That will limit your ability to vacuum, as a it costs a lot to write to.

I'd look at using a backup server to migrate to 8.3 and improve
maintenance procedures. Look at slony for this. It's an amazing live
cluster migration tool.