Vacuum-full very slow

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Vacuum-full very slow
Date: 2007-04-25 15:55:12
Message-ID: 462F79E0.2040105@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2007-04-25 15:56:41 Re: FOREIGN KEY CONSTRAINT AND INHERITANCE
Previous Message Ben 2007-04-25 15:50:11 Re: reasonable limit to number of schemas in a database?

Browse pgsql-hackers by date

  From Date Subject
Next Message Neil Conway 2007-04-25 15:58:21 Re: temporal variants of generate_series()
Previous Message Andrew Dunstan 2007-04-25 15:30:13 Re: ECPG failure on BF member Vaquita (Windows Vista)