Vacuum full takes forever
Vacuum full takes forever
От:
Pieter-Jan Savat <pieterjan.savat@barclab.com>
Дата:
Hello, I'm facing the following problem. I have a postgres 8.0 DB with a table 'results' containing 6.000.000 records. This table has 16 indexes. Each one basically created to speed up different queries. Because of some glitch in the system there has never been a VACUUM FULL on this table. When I try to do a full vacuum (on a dual-processor, 2GB RAM, ...) it takes forever. I started the vacuum at 6pm and 15 hours later it was still going on. Just before starting vacuum full, I did a vacuum analyze (which took about 15 minutes). I also checked the amount of diskspace used for the indexes => 33% of 11 available GigaBytes. After killing the vacuum full my diskspace for the indexes has increased to 41% of the 11 available GB. So does anyone know what I can do to fully vacuum my table? Or to at least decrease the amount of diskspace used? cheers, pj ================================================================= here is the output of the analyze : /usr/local/pgsql/bin/vacuumdb --dbname Core --analyze --user postgres --verbose --table 'results' LOG: statement: VACUUM VERBOSE ANALYZE results; INFO: vacuuming "public.results" INFO: index "PRIM_RESULTS_id" now contains 5647429 row versions in 26572 pages DETAIL: 1992717 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 1.80s/5.76u sec elapsed 54.99 sec. INFO: index "CTR_U_RESULTS" now contains 5647429 row versions in 66516 pages DETAIL: 1992717 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 4.11s/6.78u sec elapsed 134.15 sec. INFO: index "IDX_RESULTS_accession_nr" now contains 5647429 row versions in 40281 pages DETAIL: 1992716 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 2.53s/5.85u sec elapsed 95.72 sec. INFO: index "IDX_RESULTS_blinding_flag" now contains 5647429 row versions in 33408 pages DETAIL: 1992716 index row versions were removed. 17242 index pages have been deleted, 10350 are currently reusable. CPU 1.62s/3.94u sec elapsed 56.00 sec. INFO: index "IDX_RESULTS_inv_code" now contains 5647429 row versions in 44918 pages DETAIL: 1992716 index row versions were removed. 18738 index pages have been deleted, 11307 are currently reusable. CPU 2.28s/4.44u sec elapsed 61.94 sec. INFO: index "IDX_RESULTS_lab_pat_nr" now contains 5647429 row versions in 41821 pages DETAIL: 1992716 index row versions were removed. 3102 index pages have been deleted, 1524 are currently reusable. CPU 2.21s/5.40u sec elapsed 93.97 sec. INFO: index "IDX_RESULTS_study_code" now contains 5647429 row versions in 33395 pages DETAIL: 1992716 index row versions were removed. 17087 index pages have been deleted, 10251 are currently reusable. CPU 1.61s/3.81u sec elapsed 56.14 sec. INFO: index "IDX_RESULTS_study_code_accession_nr" now contains 5647429 row versions in 49515 pages DETAIL: 1992716 index row versions were removed. 5 index pages have been deleted, 0 are currently reusable. CPU 3.03s/5.96u sec elapsed 105.38 sec. INFO: index "IDX_RESULTS_study_code_inv_code_site_code" now contains 5647429 row versions in 69415 pages DETAIL: 1992716 index row versions were removed. 29584 index pages have been deleted, 17798 are currently reusable. CPU 3.48s/5.36u sec elapsed 101.96 sec. INFO: index "IDX_RESULTS_study_code_test_code_visit_name" now contains 5647429 row versions in 65217 pages DETAIL: 1992716 index row versions were removed. 14998 index pages have been deleted, 8690 are currently reusable. CPU 3.33s/6.09u sec elapsed 118.88 sec. INFO: index "IDX_RESULTS_study_id" now contains 5647429 row versions in 33354 pages DETAIL: 1992716 index row versions were removed. 17059 index pages have been deleted, 10217 are currently reusable. CPU 1.65s/3.96u sec elapsed 55.23 sec. INFO: index "IDX_RESULTS_study_id_test_code_blinding_flag" now contains 5647429 row versions in 57022 pages DETAIL: 1992716 index row versions were removed. 22134 index pages have been deleted, 13258 are currently reusable. CPU 3.15s/4.84u sec elapsed 92.27 sec. INFO: index "IDX_RESULTS_catflag_one_test_code" now contains 5647429 row versions in 56836 pages DETAIL: 1992716 index row versions were removed. 22034 index pages have been deleted, 13249 are currently reusable. CPU 3.39s/5.36u sec elapsed 94.52 sec. INFO: index "IDX_RESULTS_catflag_two_test_code" now contains 5647429 row versions in 56714 pages DETAIL: 1992716 index row versions were removed. 21931 index pages have been deleted, 13120 are currently reusable. CPU 2.92s/5.40u sec elapsed 102.08 sec. INFO: index "IDX_RESULTS_catflag_three_test_code" now contains 5647429 row versions in 56704 pages DETAIL: 1992716 index row versions were removed. 21917 index pages have been deleted, 13198 are currently reusable. CPU 3.02s/5.15u sec elapsed 97.27 sec. INFO: index "IDX_RESULTS_catflag_four_test_code" now contains 5647429 row versions in 56667 pages DETAIL: 1992716 index row versions were removed. 21918 index pages have been deleted, 13144 are currently reusable. CPU 3.09s/5.47u sec elapsed 85.58 sec. INFO: index "IDX_RESULTS_catflag_five_test_code" now contains 5647429 row versions in 56722 pages DETAIL: 1992716 index row versions were removed. 21940 index pages have been deleted, 13110 are currently reusable. CPU 3.34s/5.52u sec elapsed 82.15 sec. INFO: index "IDX_RESULTS_study_code_specimen_name" now contains 5647429 row versions in 52147 pages DETAIL: 1992716 index row versions were removed. 22432 index pages have been deleted, 10901 are currently reusable. CPU 2.60s/4.85u sec elapsed 77.11 sec. INFO: "results": removed 1992717 row versions in 183179 pages DETAIL: CPU 9.66s/9.53u sec elapsed 129.92 sec. INFO: "results": found 1992717 removable, 5647429 nonremovable row versions in 628928 pages DETAIL: 0 dead row versions cannot be removed yet. There were 2791954 unused item pointers. 0 pages are entirely empty. CPU 75.02s/106.32u sec elapsed 1804.40 sec. INFO: vacuuming "pg_toast.pg_toast_567723" INFO: index "pg_toast_567723_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_567723": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.results" INFO: "results": scanned 3000 of 628928 pages, containing 27323 live rows and 0 dead rows; 3000 rows in sample, 5728067 estimated total rows VACUUM
Re: Vacuum full takes forever
От:
Chris Browne <cbbrowne@acm.org>
Дата:
pieterjan.savat@barclab.com (Pieter-Jan Savat) writes:
> I'm facing the following problem.
>
> I have a postgres 8.0 DB with a table 'results' containing 6.000.000
> records.
> This table has 16 indexes. Each one basically created to speed up
> different queries.
>
> Because of some glitch in the system there has never been a VACUUM
> FULL on this table.
> When I try to do a full vacuum (on a dual-processor, 2GB RAM, ...) it
> takes forever. I started the
> vacuum at 6pm and 15 hours later it was still going on.
> Just before starting vacuum full, I did a vacuum analyze (which took
> about 15 minutes). I also
> checked the amount of diskspace used for the indexes => 33% of 11
> available GigaBytes.
> After killing the vacuum full my diskspace for the indexes has
> increased to 41% of the 11 available GB.
>
> So does anyone know what I can do to fully vacuum my table? Or to at
> least decrease the amount of diskspace used?
Two choices offer themselves:
1. Drop all indices.
Then VACUUM FULL the table.
Then recreate the indices.
2. CLUSTER the table based on one of the indices.
None of this is going to be pretty; it'll take hours.
1. and 2. are nearly equivalent; the conspicuous difference is that
1. will give you feedback along the way as it completes one step or
another.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"