From: | "G(dot) Thomas Yagel, Jr(dot)" <tyagel(at)yahoo(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Vacuum related question |
Date: | 2004-09-16 16:17:43 |
Message-ID: | EOEFLPAIAEDLPGGLIIGNEEEACIAA.tyagel@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a postgresql database into which I inserted about 40 million records
over a 12 hour period. I did not perform any updates or deleletes on the
records.
After 12 hours I stopped inserting recods and started a "vacuum verbose".
The vacuuming has taken almost 2 hours now and is not even finished. What I
don't understand is why there is so much vacuuming to be done on some tables
that are only being inserted into. As an example, I have pasted the output
from the vacuuming of the pg_largeobject table.
Can someone look at this output and tell me why vacuum seems to churn
through that table so much and how there could have been 18 million
removeable row versions (that number is consistent with how many large
objects should be in that table)? Does this have to do with how the
indexing algorithm works?
Thanks,
Thomas
INFO: vacuuming "pg_catalog.pg_largeobject"
INFO: index "pg_largeobject_loid_pn_index" now contains 38246053 row
versions in 153134 pages
DETAIL: 1397769 index row versions were removed.
256 index pages have been deleted, 3 are currently reusable.
CPU 8.57s/13.76u sec elapsed 255.06 sec.
INFO: "pg_largeobject": removed 1397769 row versions in 167552 pages
DETAIL: CPU 10.46s/13.97u sec elapsed 133.29 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 36848275 row
versions in 153134 pages
DETAIL: 1397778 index row versions were removed.
967 index pages have been deleted, 3 are currently reusable.
CPU 7.40s/12.99u sec elapsed 169.96 sec.
INFO: "pg_largeobject": removed 1397778 row versions in 143887 pages
DETAIL: CPU 8.80s/11.92u sec elapsed 103.85 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 35450514 row
versions in 153134 pages
DETAIL: 1397761 index row versions were removed.
1125 index pages have been deleted, 3 are currently reusable.
CPU 7.10s/12.73u sec elapsed 148.63 sec.
INFO: "pg_largeobject": removed 1397761 row versions in 156819 pages
DETAIL: CPU 8.99s/13.32u sec elapsed 104.23 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 34052746 row
versions in 153134 pages
DETAIL: 1397768 index row versions were removed.
1226 index pages have been deleted, 3 are currently reusable.
CPU 6.99s/12.44u sec elapsed 143.04 sec.
INFO: "pg_largeobject": removed 1397768 row versions in 158270 pages
DETAIL: CPU 8.78s/13.46u sec elapsed 107.82 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 32654985 row
versions in 153134 pages
DETAIL: 1397761 index row versions were removed.
1232 index pages have been deleted, 3 are currently reusable.
CPU 7.27s/11.77u sec elapsed 142.46 sec.
INFO: "pg_largeobject": removed 1397761 row versions in 160051 pages
DETAIL: CPU 9.43s/13.19u sec elapsed 116.89 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 31257224 row
versions in 153134 pages
DETAIL: 1397761 index row versions were removed.
1244 index pages have been deleted, 3 are currently reusable.
CPU 7.80s/11.55u sec elapsed 143.49 sec.
INFO: "pg_largeobject": removed 1397761 row versions in 159893 pages
DETAIL: CPU 9.05s/13.31u sec elapsed 110.53 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 29859458 row
versions in 153134 pages
DETAIL: 1397766 index row versions were removed.
1297 index pages have been deleted, 3 are currently reusable.
CPU 7.29s/11.29u sec elapsed 184.59 sec.
INFO: "pg_largeobject": removed 1397766 row versions in 158989 pages
DETAIL: CPU 9.07s/13.15u sec elapsed 111.66 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 28461693 row
versions in 153134 pages
DETAIL: 1397765 index row versions were removed.
1320 index pages have been deleted, 3 are currently reusable.
CPU 7.03s/11.01u sec elapsed 153.20 sec.
INFO: "pg_largeobject": removed 1397765 row versions in 159645 pages
DETAIL: CPU 9.24s/13.24u sec elapsed 116.00 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 27063928 row
versions in 153134 pages
DETAIL: 1397765 index row versions were removed.
1333 index pages have been deleted, 3 are currently reusable.
CPU 7.69s/10.17u sec elapsed 186.98 sec.
INFO: "pg_largeobject": removed 1397765 row versions in 159885 pages
DETAIL: CPU 9.27s/12.97u sec elapsed 116.60 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 25666165 row
versions in 153134 pages
DETAIL: 1397763 index row versions were removed.
1338 index pages have been deleted, 3 are currently reusable.
CPU 7.19s/9.85u sec elapsed 181.75 sec.
INFO: "pg_largeobject": removed 1397763 row versions in 160120 pages
DETAIL: CPU 9.21s/13.14u sec elapsed 117.31 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 24268402 row
versions in 153134 pages
DETAIL: 1397763 index row versions were removed.
1357 index pages have been deleted, 3 are currently reusable.
CPU 7.52s/9.49u sec elapsed 145.47 sec.
INFO: "pg_largeobject": removed 1397763 row versions in 159804 pages
DETAIL: CPU 8.51s/13.00u sec elapsed 114.38 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 22870636 row
versions in 153134 pages
DETAIL: 1397766 index row versions were removed.
1398 index pages have been deleted, 3 are currently reusable.
CPU 7.72s/9.15u sec elapsed 133.68 sec.
INFO: "pg_largeobject": removed 1397766 row versions in 159370 pages
DETAIL: CPU 9.04s/13.13u sec elapsed 124.29 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 21472870 row
versions in 153134 pages
DETAIL: 1397766 index row versions were removed.
1426 index pages have been deleted, 3 are currently reusable.
CPU 7.13s/8.80u sec elapsed 168.96 sec.
INFO: "pg_largeobject": removed 1397766 row versions in 159594 pages
DETAIL: CPU 9.48s/13.57u sec elapsed 126.71 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 21326336 row
versions in 153134 pages
DETAIL: 146534 index row versions were removed.
1426 index pages have been deleted, 3 are currently reusable.
CPU 7.25s/5.32u sec elapsed 130.94 sec.
INFO: "pg_largeobject": removed 146534 row versions in 16784 pages
DETAIL: CPU 0.86s/1.32u sec elapsed 12.44 sec.
INFO: "pg_largeobject": found 18317486 removable, 21326336 nonremovable row
versions in 2119638 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 355487 unused item pointers.
0 pages are entirely empty.
CPU 301.74s/343.24u sec elapsed 4871.43 sec.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-09-16 16:22:27 | Re: plpgsql assigning RECORD := RECORD |
Previous Message | Marco Ferretti | 2004-09-16 16:10:13 | Strange UTF-8 behaviour |