Thread: Question about VACUUM

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).

My questions are,

> Is it possible to estimate how long VACUUM on a table might take?

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

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

> maintenance_work_mem is at 64MB and shared_buffers at 2GB. Should I dedicate more memory to maintenance_work_mem to speedup VACUUM?

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

Thanks in advance,
Steve

Joey K. wrote:
>> Is it possible to estimate how long VACUUM on a table might take?
>
>> The table size is growing as "VACUUM" is being performed. I assume  I need
> reindex after VACUUM is complete.
>
>> I run VACUUM from psql and I Ctrl-C it to turn it off is this acceptable?
>
>> maintenance_work_mem is at 64MB and shared_buffers at 2GB. Should I
> dedicate more memory to maintenance_work_mem to speedup VACUUM?
>
> The server is a Intel Xeon 3.0GHz with 4GB RAM and RAID-5 (Yes I know).
>
> Thanks in advance,
> Steve

Hello Joey,

an alternative to vacuum might be to use cluster on the big tables. The
documentation on that feature is here:
http://www.postgresql.org/docs/8.3/static/sql-cluster.html

To my knowledge vacuum full might add to index bloat, I'm not sure about
plain vacuum. I'd increase maintenance_work_mem to at least 256 MB and
lower shared_buffers a bit.

It should be OK to Ctrl-C a vacuum task in psql.


--
Best regards,
Hannes Dorbath

On Sat, Oct 25, 2008 at 9:21 AM, Joey K. <pguser@gmail.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.