Re: Vacuum-full very slow

Steve Crawford wrote:
> 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.

You could try CLUSTER instead of VACUUM FULL, as I think it should be
faster. And the indexes will be devoid of any bloat, which will be a
nice side effect.

I wonder, though, if you set maintenance_work_mem too high and are
causing the OS to swap?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.