One vacuum full is not enough. - Mailing list pgsql-hackers

Hi all,
running a 7.4.5 it happen to me with another table
where a single vacuum full was not freeing enough pages,
here the verbose vacuum full, as you can see only at
the end:  truncated 8504 to 621 pages.

I use pg_autovacuum and it's not enough. I'll schedule
again a nightly vacuum full.

Regards
Gaetano Mendola


# vacuum full verbose url;
INFO:  vacuuming "public.url"
INFO:  "url": found 268392 removable, 21286 nonremovable row versions in 8563 pages
DETAIL:  22 dead row versions cannot be removed yet.
Nonremovable row versions range from 104 to 860 bytes long.
There were 13924 unused item pointers.
Total free space (including removable row versions) is 63818404 bytes.
4959 pages are or will become empty, including 7 at the end of the table.
8296 pages containing 63753840 free bytes are potential move destinations.
CPU 0.33s/0.12u sec elapsed 9.55 sec.
INFO:  index "url_pkey" now contains 21286 row versions in 2343 pages
DETAIL:  268392 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.11s/0.37u sec elapsed 2.68 sec.
INFO:  index "idx_url_url" now contains 297 row versions in 7412 pages
DETAIL:  268392 index row versions were removed.
6869 index pages have been deleted, 6869 are currently reusable.
CPU 1.02s/2.05u sec elapsed 59.89 sec.
INFO:  index "idx_url_name" now contains 297 row versions in 3277 pages
DETAIL:  268392 index row versions were removed.
2976 index pages have been deleted, 2976 are currently reusable.
CPU 0.40s/0.72u sec elapsed 27.05 sec.
INFO:  "url": moved 2 row versions, truncated 8563 to 8550 pages
DETAIL:  CPU 0.40s/0.52u sec elapsed 28.05 sec.
INFO:  index "url_pkey" now contains 21287 row versions in 2343 pages
DETAIL:  1 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.07s/0.04u sec elapsed 6.22 sec.
INFO:  index "idx_url_url" now contains 298 row versions in 7412 pages
DETAIL:  0 index row versions were removed.
6956 index pages have been deleted, 6956 are currently reusable.
CPU 0.37s/0.07u sec elapsed 14.30 sec.
INFO:  index "idx_url_name" now contains 298 row versions in 3277 pages
DETAIL:  0 index row versions were removed.
2979 index pages have been deleted, 2979 are currently reusable.
CPU 0.16s/0.04u sec elapsed 4.79 sec.
INFO:  vacuuming "pg_toast.pg_toast_16730637"
INFO:  "pg_toast_16730637": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_16730637_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.
VACUUM
empdb=# vacuum full verbose url;
INFO:  vacuuming "public.url"
INFO:  "url": found 42 removable, 21286 nonremovable row versions in 8550 pages
DETAIL:  22 dead row versions cannot be removed yet.
Nonremovable row versions range from 104 to 860 bytes long.
There were 281879 unused item pointers.
Total free space (including removable row versions) is 63713588 bytes.
4948 pages are or will become empty, including 0 at the end of the table.
8289 pages containing 63705340 free bytes are potential move destinations.
CPU 0.39s/0.15u sec elapsed 5.93 sec.
INFO:  index "url_pkey" now contains 21286 row versions in 2343 pages
DETAIL:  42 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.09s/0.03u sec elapsed 1.44 sec.
INFO:  index "idx_url_url" now contains 297 row versions in 7412 pages
DETAIL:  42 index row versions were removed.
6956 index pages have been deleted, 6956 are currently reusable.
CPU 0.18s/0.08u sec elapsed 7.30 sec.
INFO:  index "idx_url_name" now contains 297 row versions in 3277 pages
DETAIL:  42 index row versions were removed.
2979 index pages have been deleted, 2979 are currently reusable.
CPU 0.09s/0.02u sec elapsed 2.58 sec.
INFO:  "url": moved 0 row versions, truncated 8550 to 8550 pages
DETAIL:  CPU 0.37s/0.23u sec elapsed 4.39 sec.
INFO:  vacuuming "pg_toast.pg_toast_16730637"
INFO:  "pg_toast_16730637": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
INFO:  index "pg_toast_16730637_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.
VACUUM
empdb=# vacuum full verbose url;
INFO:  vacuuming "public.url"
INFO:  "url": found 74 removable, 21266 nonremovable row versions in 8550 pages
DETAIL:  2 dead row versions cannot be removed yet.
Nonremovable row versions range from 104 to 860 bytes long.
There were 281867 unused item pointers.
Total free space (including removable row versions) is 63718972 bytes.
4952 pages are or will become empty, including 0 at the end of the table.
8289 pages containing 63710724 free bytes are potential move destinations.
CPU 0.07s/0.03u sec elapsed 0.28 sec.
INFO:  index "url_pkey" now contains 21266 row versions in 2343 pages
DETAIL:  74 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.06s/0.05u sec elapsed 1.31 sec.
INFO:  index "idx_url_url" now contains 277 row versions in 7412 pages
DETAIL:  74 index row versions were removed.
6961 index pages have been deleted, 6961 are currently reusable.
CPU 0.16s/0.04u sec elapsed 6.80 sec.
INFO:  index "idx_url_name" now contains 277 row versions in 3277 pages
DETAIL:  74 index row versions were removed.
2982 index pages have been deleted, 2982 are currently reusable.
CPU 0.09s/0.02u sec elapsed 2.10 sec.
INFO:  "url": moved 11 row versions, truncated 8550 to 8504 pages
DETAIL:  CPU 0.29s/0.11u sec elapsed 3.26 sec.
INFO:  index "url_pkey" now contains 21266 row versions in 2343 pages
DETAIL:  11 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.06s/0.01u sec elapsed 2.39 sec.
INFO:  index "idx_url_url" now contains 277 row versions in 7412 pages
DETAIL:  11 index row versions were removed.
6964 index pages have been deleted, 6964 are currently reusable.
CPU 0.22s/0.06u sec elapsed 5.74 sec.
INFO:  index "idx_url_name" now contains 277 row versions in 3277 pages
DETAIL:  11 index row versions were removed.
2983 index pages have been deleted, 2983 are currently reusable.
CPU 0.12s/0.04u sec elapsed 2.19 sec.
INFO:  vacuuming "pg_toast.pg_toast_16730637"
INFO:  "pg_toast_16730637": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_16730637_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.
VACUUM
empdb=# vacuum full verbose url;
INFO:  vacuuming "public.url"
INFO:  "url": found 13 removable, 21264 nonremovable row versions in 8504 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 104 to 860 bytes long.
There were 280528 unused item pointers.
Total free space (including removable row versions) is 63349188 bytes.
4913 pages are or will become empty, including 0 at the end of the table.
8234 pages containing 63340628 free bytes are potential move destinations.
CPU 0.17s/0.04u sec elapsed 0.49 sec.
INFO:  index "url_pkey" now contains 21264 row versions in 2343 pages
DETAIL:  13 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.06s/0.03u sec elapsed 0.38 sec.
INFO:  index "idx_url_url" now contains 275 row versions in 7412 pages
DETAIL:  13 index row versions were removed.
6964 index pages have been deleted, 6964 are currently reusable.
CPU 0.10s/0.04u sec elapsed 0.69 sec.
INFO:  index "idx_url_name" now contains 275 row versions in 3277 pages
DETAIL:  13 index row versions were removed.
2983 index pages have been deleted, 2983 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO:  "url": moved 5666 row versions, truncated 8504 to 621 pages
DETAIL:  CPU 0.06s/0.31u sec elapsed 0.84 sec.
INFO:  index "url_pkey" now contains 21264 row versions in 2343 pages
DETAIL:  5666 index row versions were removed.
886 index pages have been deleted, 886 are currently reusable.
CPU 0.01s/0.01u sec elapsed 0.04 sec.
INFO:  index "idx_url_url" now contains 275 row versions in 7412 pages
DETAIL:  261 index row versions were removed.
6973 index pages have been deleted, 6973 are currently reusable.
CPU 0.01s/0.03u sec elapsed 0.04 sec.
INFO:  index "idx_url_name" now contains 275 row versions in 3277 pages
DETAIL:  261 index row versions were removed.
2990 index pages have been deleted, 2990 are currently reusable.
CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO:  vacuuming "pg_toast.pg_toast_16730637"
INFO:  "pg_toast_16730637": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_16730637_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.
VACUUM