7.3.1 takes long time to vacuum table?

Hi everyone,

Does anyone know of any issues with 7.3.1 which would cause it to take a
long time to do a vacuum full? We have a resource table consisting of
about 70M records and we have recently performed an update query on the
entire table and now I'm trying to do a vacuum full to reclaim back the
disk space. So far on a dual PIII 1.4GHz machine with hardware RAID5 and
2GB RAM, the vacuum full verbose is still running after 30 hours!

The reason I am concerned is that before performing the update, the
vacuum full would normally take about 12 hours and that was with a
trigger and 5 indexes on the table. This time, before initiating the
vacuum, all the indexes were dropped, and a single update performed on 1
field over the entire table. I understand that postgres has to compact
the valid tuples down to the front of the file after removing the
previous ones, but should it really take this long on such a powerful
machine? Or have I made a gross error somewhere in the configuration?

When I first configured the database, vacuuming the table took nearly 20
hours, but thanks to a post in the archives, I found some suggestions to
increase vacuum mem & FSM size and reduce the number of buffers which
got it down to its normal time of about 12 hours :) Here are the
settings I changed from the defaults based on that post:

Max_fsm_relations = 50000
Max_fsm_pages = 5000000
Vacuum_mem = 65535
Fsync = false

I have also set shmmax to 800Mb just to give things some breathing
space. One thing I have noticed is that the postmaster process running
the vacuum has now reached 1Gb of memory and looks like it is beginning
to touch swap(!) which is going to slow things even more. Can anyone
help me out and reduce the time it takes to do this vacuum?


Cheers,

Mark.


---

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.