Hi,
the time needed for a daily VACUUM on a table with about 28 mio records
increases from day to day. What's the best way to avoid this? A full
vacuum will probably take too much time, are there other ways to keep
vacuum performant?
The database was updated to postgres-8.0 on Jun 04 this year.
Between Jun 07 and Jun 30 the time vacuum needed increased from 683
seconds up to 1,663 seconds, the output is posted below. E.g. the time
for vacuuming the index of a text-field (i_ids_user) raised from 123 sec
to 668 secs. The increase happens each day so this is not a problem of
the last run. The number of records in the table in the same time only
increased from 27.5 mio to 28.9 mio, the number of records updated daily
is about 700,000 to 1,000,000.
Regards
Martin
================================================================
| Tue Jun 7 04:07:17 CEST 2005 Starting
| SET VACUUM_MEM=250000; VACUUM ANALYZE VERBOSE t_ids
----------------------------------------------------------------
INFO: vacuuming "public.t_ids"
INFO: index "i_ids_score" now contains 4323671 row versions in 12414 pages
DETAIL: 493855 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.76s/5.44u sec elapsed 33.22 sec.
INFO: index "i_ids_id" now contains 27500002 row versions in 61515 pages
DETAIL: 960203 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 8.09s/24.93u sec elapsed 108.43 sec.
INFO: index "i_ids_user" now contains 27500002 row versions in 103172 pages
DETAIL: 960203 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 14.00s/39.65u sec elapsed 123.47 sec.
INFO: "t_ids": removed 960203 row versions in 203369 pages
DETAIL: CPU 22.88s/21.72u sec elapsed 294.22 sec.
INFO: "t_ids": found 960203 removable, 27500002 nonremovable row versions in 208912 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 214149 unused item pointers.
0 pages are entirely empty.
CPU 53.02s/93.76u sec elapsed 643.46 sec.
INFO: vacuuming "pg_toast.pg_toast_224670"
INFO: index "pg_toast_224670_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.01 sec.
INFO: "pg_toast_224670": 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.01 sec.
INFO: analyzing "public.t_ids"
INFO: "t_ids": scanned 90000 of 208912 pages, containing 11846838 live rows and 0 dead rows; 90000 rows in sample,
27499407estimated total rows
VACUUM
----------------------------------------------------------------
| Tue Jun 7 04:18:40 CEST 2005 Job finished after 683 seconds
================================================================
================================================================
| Thu Jun 30 01:23:33 CEST 2005 Starting
| SET VACUUM_MEM=250000; VACUUM ANALYZE VERBOSE t_ids
----------------------------------------------------------------
INFO: vacuuming "public.t_ids"
INFO: index "i_ids_score" now contains 4460326 row versions in 29867 pages
DETAIL: 419232 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 4.58s/7.72u sec elapsed 368.13 sec.
INFO: index "i_ids_id" now contains 28948643 row versions in 68832 pages
DETAIL: 795700 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 9.08s/25.29u sec elapsed 151.38 sec.
INFO: index "i_ids_user" now contains 28948938 row versions in 131683 pages
DETAIL: 795700 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 20.10s/43.27u sec elapsed 668.00 sec.
INFO: "t_ids": removed 795700 row versions in 206828 pages
DETAIL: CPU 23.35s/23.50u sec elapsed 309.19 sec.
INFO: "t_ids": found 795700 removable, 28948290 nonremovable row versions in 223145 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 906106 unused item pointers.
0 pages are entirely empty.
CPU 63.10s/101.96u sec elapsed 1592.00 sec.
INFO: vacuuming "pg_toast.pg_toast_224670"
INFO: index "pg_toast_224670_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_224670": 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.02 sec.
INFO: analyzing "public.t_ids"
INFO: "t_ids": scanned 90000 of 223146 pages, containing 11675055 live rows and 288 dead rows; 90000 rows in sample,
28947131estimated total rows
VACUUM
----------------------------------------------------------------
| Thu Jun 30 01:51:16 CEST 2005 Job finished after 1663 seconds
================================================================
