I'm in the process of archiving data on one of my PG machines. After
backing up the data, I delete the old records and then run a "vacuum
full" on each table.
I'm vacuuming the first table now and it is taking much longer than I
expected (I'm now past the 2-hour mark). Some info:
Version: 8.1.2
On-disk table size: ~1.9GB
Records deleted from the table: 10,290,892 (~60% of records)
Physical memory: 2GB
Connection maintenance_work_mem: 1GB
Table indexes: 7
CPU: Intel(R) Pentium(R) 4 CPU 3.00GHz
Disk: 2x200GB SATA as RAID-1 using 3-ware card
The vacuum full is the only significant load on the server at the moment
(PG or otherwise). IO is probably the bottleneck as CPU is running near
50% idle and 40% wait-state with PG using in the 5-15% range.
So....
What amount of time might I expect to wait for this process to complete?
Should I be changing settings other than maintenance_work_mem?
What is the effect of the indexes and would dropping and recreating them
help?
Other suggestions?
Cheers,
Steve