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