Single table vacuum full different when vacuum full the whole database - Mailing list pgsql-admin

Hi all,

I'm experiencing different behaviour if a vacuum full a single
table or the whole DB:

SINGLE TABLE:


# vacuum full verbose ua_user_data_exp;
INFO:  vacuuming "public.ua_user_data_exp"
INFO:  "ua_user_data_exp": found 36 removable, 34519 nonremovable row versions in 22662 pages
DETAIL:  82 dead row versions cannot be removed yet.
Nonremovable row versions range from 444 to 924 bytes long.
There were 289482 unused item pointers.
Total free space (including removable row versions) is 164682076 bytes.
14385 pages are or will become empty, including 8 at the end of the table.
21687 pages containing 164397272 free bytes are potential move destinations.
CPU 1.04s/0.23u sec elapsed 6.79 sec.
INFO:  index "exp_id_provider" now contains 34519 row versions in 2537 pages
DETAIL:  36 index row versions were removed.
2260 index pages have been deleted, 2252 are currently reusable.
CPU 0.16s/0.03u sec elapsed 2.71 sec.
INFO:  index "exp_ci_login" now contains 34519 row versions in 1401 pages
DETAIL:  36 index row versions were removed.
103 index pages have been deleted, 103 are currently reusable.
CPU 0.11s/0.05u sec elapsed 0.66 sec.
INFO:  index "exp_country" now contains 34519 row versions in 1855 pages
DETAIL:  36 index row versions were removed.
1549 index pages have been deleted, 1541 are currently reusable.
CPU 0.06s/0.05u sec elapsed 1.45 sec.
INFO:  index "exp_os_type" now contains 34519 row versions in 3015 pages
DETAIL:  36 index row versions were removed.
2688 index pages have been deleted, 2672 are currently reusable.
CPU 0.08s/0.07u sec elapsed 2.11 sec.
INFO:  index "exp_card" now contains 34519 row versions in 3119 pages
DETAIL:  36 index row versions were removed.
2795 index pages have been deleted, 2783 are currently reusable.
CPU 0.09s/0.07u sec elapsed 1.99 sec.
INFO:  index "exp_status" now contains 34519 row versions in 2647 pages
DETAIL:  36 index row versions were removed.
2281 index pages have been deleted, 2259 are currently reusable.
CPU 0.12s/0.06u sec elapsed 1.85 sec.
INFO:  index "exp_email" now contains 34519 row versions in 2320 pages
DETAIL:  36 index row versions were removed.
1248 index pages have been deleted, 1242 are currently reusable.
CPU 0.05s/0.03u sec elapsed 0.87 sec.
INFO:  index "exp_ci_email" now contains 34519 row versions in 2357 pages
DETAIL:  36 index row versions were removed.
1279 index pages have been deleted, 1274 are currently reusable.
CPU 0.12s/0.06u sec elapsed 0.86 sec.
INFO:  index "exp_lastname" now contains 34519 row versions in 1448 pages
DETAIL:  36 index row versions were removed.
502 index pages have been deleted, 500 are currently reusable.
CPU 0.07s/0.03u sec elapsed 0.57 sec.
INFO:  index "exp_ci_lastname" now contains 34519 row versions in 1444 pages
DETAIL:  36 index row versions were removed.
501 index pages have been deleted, 499 are currently reusable.
CPU 0.05s/0.07u sec elapsed 0.55 sec.
INFO:  index "exp_orbital_ptns" now contains 34519 row versions in 3001 pages
DETAIL:  36 index row versions were removed.
2668 index pages have been deleted, 2651 are currently reusable.
CPU 0.13s/0.02u sec elapsed 1.65 sec.
INFO:  index "exp_stickers" now contains 34519 row versions in 2980 pages
DETAIL:  36 index row versions were removed.
2616 index pages have been deleted, 2606 are currently reusable.
CPU 0.07s/0.04u sec elapsed 1.63 sec.
INFO:  index "exp_pid" now contains 34519 row versions in 2169 pages
DETAIL:  36 index row versions were removed.
1930 index pages have been deleted, 1917 are currently reusable.
CPU 0.11s/0.03u sec elapsed 1.29 sec.
INFO:  index "exp_mac_address" now contains 34519 row versions in 2012 pages
DETAIL:  36 index row versions were removed.
413 index pages have been deleted, 413 are currently reusable.
CPU 0.01s/0.08u sec elapsed 0.28 sec.
INFO:  index "exp_mac_address_normal" now contains 34519 row versions in 2014 pages
DETAIL:  36 index row versions were removed.
421 index pages have been deleted, 421 are currently reusable.
CPU 0.05s/0.04u sec elapsed 0.33 sec.
INFO:  index "ua_user_data_exp_id_user_key" now contains 34519 row versions in 886 pages
DETAIL:  36 index row versions were removed.
3 index pages have been deleted, 3 are currently reusable.
CPU 0.02s/0.02u sec elapsed 0.12 sec.
INFO:  index "ua_user_data_exp_login_key" now contains 34519 row versions in 1432 pages
DETAIL:  36 index row versions were removed.
122 index pages have been deleted, 122 are currently reusable.
CPU 0.05s/0.05u sec elapsed 0.17 sec.
INFO:  "ua_user_data_exp": moved 1362 row versions, truncated 22662 to 17980 pages
DETAIL:  CPU 1.39s/2.30u sec elapsed 39.48 sec.
INFO:  index "exp_id_provider" now contains 34519 row versions in 2537 pages
DETAIL:  1362 index row versions were removed.
2328 index pages have been deleted, 2320 are currently reusable.
CPU 0.05s/0.01u sec elapsed 4.49 sec.
INFO:  index "exp_ci_login" now contains 34519 row versions in 1401 pages
DETAIL:  1362 index row versions were removed.
103 index pages have been deleted, 103 are currently reusable.
CPU 0.03s/0.09u sec elapsed 1.61 sec.
INFO:  index "exp_country" now contains 34519 row versions in 1855 pages
DETAIL:  1362 index row versions were removed.
1584 index pages have been deleted, 1576 are currently reusable.
CPU 0.05s/0.00u sec elapsed 2.02 sec.
INFO:  index "exp_os_type" now contains 34519 row versions in 3015 pages
DETAIL:  1362 index row versions were removed.
2773 index pages have been deleted, 2757 are currently reusable.
CPU 0.11s/0.04u sec elapsed 2.58 sec.
INFO:  index "exp_card" now contains 34519 row versions in 3119 pages
DETAIL:  1362 index row versions were removed.
2861 index pages have been deleted, 2849 are currently reusable.
CPU 0.07s/0.01u sec elapsed 2.31 sec.
INFO:  index "exp_status" now contains 34519 row versions in 2645 pages
DETAIL:  1362 index row versions were removed.
2388 index pages have been deleted, 2366 are currently reusable.
CPU 0.13s/0.02u sec elapsed 1.76 sec.
INFO:  index "exp_email" now contains 34519 row versions in 2320 pages
DETAIL:  1362 index row versions were removed.
1264 index pages have been deleted, 1258 are currently reusable.
CPU 0.07s/0.03u sec elapsed 1.29 sec.
INFO:  index "exp_ci_email" now contains 34519 row versions in 2357 pages
DETAIL:  1362 index row versions were removed.
1298 index pages have been deleted, 1293 are currently reusable.
CPU 0.05s/0.04u sec elapsed 1.26 sec.
INFO:  index "exp_lastname" now contains 34519 row versions in 1448 pages
DETAIL:  1362 index row versions were removed.
503 index pages have been deleted, 501 are currently reusable.
CPU 0.04s/0.09u sec elapsed 1.02 sec.
INFO:  index "exp_ci_lastname" now contains 34519 row versions in 1444 pages
DETAIL:  1362 index row versions were removed.
505 index pages have been deleted, 503 are currently reusable.
CPU 0.07s/0.03u sec elapsed 0.81 sec.
INFO:  index "exp_orbital_ptns" now contains 34519 row versions in 3001 pages
DETAIL:  1362 index row versions were removed.
2761 index pages have been deleted, 2744 are currently reusable.
CPU 0.04s/0.01u sec elapsed 1.65 sec.
INFO:  index "exp_stickers" now contains 34519 row versions in 2980 pages
DETAIL:  1362 index row versions were removed.
2673 index pages have been deleted, 2663 are currently reusable.
CPU 0.14s/0.04u sec elapsed 1.76 sec.
INFO:  index "exp_pid" now contains 34519 row versions in 2169 pages
DETAIL:  1362 index row versions were removed.
1985 index pages have been deleted, 1972 are currently reusable.
CPU 0.06s/0.04u sec elapsed 1.23 sec.
INFO:  index "exp_mac_address" now contains 34519 row versions in 2012 pages
DETAIL:  1362 index row versions were removed.
413 index pages have been deleted, 413 are currently reusable.
CPU 0.06s/0.09u sec elapsed 1.56 sec.
INFO:  index "exp_mac_address_normal" now contains 34519 row versions in 2014 pages
DETAIL:  1362 index row versions were removed.
421 index pages have been deleted, 421 are currently reusable.
CPU 0.12s/0.08u sec elapsed 1.43 sec.
INFO:  index "ua_user_data_exp_id_user_key" now contains 34519 row versions in 886 pages
DETAIL:  1362 index row versions were removed.
3 index pages have been deleted, 3 are currently reusable.
CPU 0.04s/0.04u sec elapsed 0.58 sec.
INFO:  index "ua_user_data_exp_login_key" now contains 34519 row versions in 1432 pages
DETAIL:  1362 index row versions were removed.
122 index pages have been deleted, 122 are currently reusable.
CPU 0.04s/0.06u sec elapsed 1.21 sec.
INFO:  vacuuming "pg_toast.pg_toast_18410"
INFO:  "pg_toast_18410": 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_18410_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


After this vacuum I did an VACUUM FULL on the whole DB and this time the
VACUUM acts different, now 4122 index row were removed even if this
vacuum was running after:

INFO:  vacuuming "public.ua_user_data_exp"
INFO:  "ua_user_data_exp": found 4122 removable, 34438 nonremovable row versions in 17980 pages
DETAIL:  1 dead row versions cannot be removed yet.
Nonremovable row versions range from 444 to 924 bytes long.
There were 219653 unused item pointers.
Total free space (including removable row versions) is 126732488 bytes.
10747 pages are or will become empty, including 1 at the end of the table.
16925 pages containing 126488436 free bytes are potential move destinations.
CPU 0.51s/0.09u sec elapsed 12.15 sec.
INFO:  index "exp_id_provider" now contains 34438 row versions in 2537 pages
DETAIL:  4122 index row versions were removed.
2307 index pages have been deleted, 2307 are currently reusable.
CPU 0.13s/0.05u sec elapsed 6.56 sec.
INFO:  index "exp_ci_login" now contains 34438 row versions in 1401 pages
DETAIL:  4122 index row versions were removed.
103 index pages have been deleted, 103 are currently reusable.
CPU 0.08s/0.06u sec elapsed 4.84 sec.
INFO:  index "exp_country" now contains 34438 row versions in 1855 pages
DETAIL:  4122 index row versions were removed.
1575 index pages have been deleted, 1575 are currently reusable.
CPU 0.04s/0.03u sec elapsed 1.93 sec.
INFO:  index "exp_os_type" now contains 34438 row versions in 3015 pages
DETAIL:  4122 index row versions were removed.
2742 index pages have been deleted, 2742 are currently reusable.
CPU 0.09s/0.07u sec elapsed 2.68 sec.
INFO:  index "exp_card" now contains 34438 row versions in 3119 pages
DETAIL:  4122 index row versions were removed.
2840 index pages have been deleted, 2840 are currently reusable.
CPU 0.10s/0.05u sec elapsed 2.85 sec.
INFO:  index "exp_status" now contains 34438 row versions in 2645 pages
DETAIL:  4122 index row versions were removed.
2349 index pages have been deleted, 2349 are currently reusable.
CPU 0.04s/0.05u sec elapsed 2.27 sec.
INFO:  index "exp_email" now contains 34438 row versions in 2320 pages
DETAIL:  4122 index row versions were removed.
1257 index pages have been deleted, 1257 are currently reusable.
CPU 0.03s/0.05u sec elapsed 3.57 sec.
INFO:  index "exp_ci_email" now contains 34438 row versions in 2357 pages
DETAIL:  4122 index row versions were removed.
1290 index pages have been deleted, 1290 are currently reusable.
CPU 0.06s/0.05u sec elapsed 3.08 sec.
INFO:  index "exp_lastname" now contains 34438 row versions in 1448 pages
DETAIL:  4122 index row versions were removed.
503 index pages have been deleted, 503 are currently reusable.
CPU 0.05s/0.05u sec elapsed 2.48 sec.
INFO:  index "exp_ci_lastname" now contains 34438 row versions in 1444 pages
DETAIL:  4122 index row versions were removed.
506 index pages have been deleted, 506 are currently reusable.
CPU 0.06s/0.02u sec elapsed 2.03 sec.
INFO:  index "exp_orbital_ptns" now contains 34438 row versions in 3001 pages
DETAIL:  4122 index row versions were removed.
2730 index pages have been deleted, 2730 are currently reusable.
CPU 0.10s/0.03u sec elapsed 2.22 sec.
INFO:  index "exp_stickers" now contains 34438 row versions in 2980 pages
DETAIL:  4122 index row versions were removed.
2652 index pages have been deleted, 2652 are currently reusable.
CPU 0.08s/0.05u sec elapsed 2.39 sec.
INFO:  index "exp_pid" now contains 34438 row versions in 2169 pages
DETAIL:  4122 index row versions were removed.
1969 index pages have been deleted, 1969 are currently reusable.
CPU 0.12s/0.01u sec elapsed 1.94 sec.
INFO:  index "exp_mac_address" now contains 34438 row versions in 2012 pages
DETAIL:  4122 index row versions were removed.
413 index pages have been deleted, 413 are currently reusable.
CPU 0.10s/0.09u sec elapsed 4.06 sec.
INFO:  index "exp_mac_address_normal" now contains 34438 row versions in 2014 pages
DETAIL:  4122 index row versions were removed.
421 index pages have been deleted, 421 are currently reusable.
CPU 0.08s/0.06u sec elapsed 3.25 sec.
INFO:  index "ua_user_data_exp_id_user_key" now contains 34438 row versions in 886 pages
DETAIL:  4122 index row versions were removed.
3 index pages have been deleted, 3 are currently reusable.
CPU 0.02s/0.03u sec elapsed 1.75 sec.
INFO:  index "ua_user_data_exp_login_key" now contains 34438 row versions in 1432 pages
DETAIL:  4122 index row versions were removed.
122 index pages have been deleted, 122 are currently reusable.
CPU 0.05s/0.02u sec elapsed 1.92 sec.
INFO:  "ua_user_data_exp": moved 27488 row versions, truncated 17980 to 2959 pages
DETAIL:  CPU 1.86s/16.15u sec elapsed 141.38 sec.
INFO:  index "exp_id_provider" now contains 34438 row versions in 2537 pages
DETAIL:  27488 index row versions were removed.
2340 index pages have been deleted, 2340 are currently reusable.
CPU 0.08s/0.08u sec elapsed 1.76 sec.
INFO:  index "exp_ci_login" now contains 34438 row versions in 1401 pages
DETAIL:  27488 index row versions were removed.
103 index pages have been deleted, 103 are currently reusable.
CPU 0.02s/0.04u sec elapsed 0.31 sec.
INFO:  index "exp_country" now contains 34438 row versions in 1855 pages
DETAIL:  27488 index row versions were removed.
1583 index pages have been deleted, 1583 are currently reusable.
CPU 0.05s/0.05u sec elapsed 1.32 sec.
INFO:  index "exp_os_type" now contains 34438 row versions in 3015 pages
DETAIL:  27488 index row versions were removed.
2784 index pages have been deleted, 2784 are currently reusable.
CPU 0.08s/0.08u sec elapsed 1.85 sec.
INFO:  index "exp_card" now contains 34438 row versions in 3119 pages
DETAIL:  27488 index row versions were removed.
2871 index pages have been deleted, 2871 are currently reusable.
CPU 0.07s/0.07u sec elapsed 1.96 sec.
INFO:  index "exp_status" now contains 34438 row versions in 2619 pages
DETAIL:  27488 index row versions were removed.
2361 index pages have been deleted, 2361 are currently reusable.
CPU 0.11s/0.08u sec elapsed 1.41 sec.
INFO:  index "exp_email" now contains 34438 row versions in 2320 pages
DETAIL:  27488 index row versions were removed.
1263 index pages have been deleted, 1263 are currently reusable.
CPU 0.11s/0.06u sec elapsed 0.90 sec.
INFO:  index "exp_ci_email" now contains 34438 row versions in 2357 pages
DETAIL:  27488 index row versions were removed.
1290 index pages have been deleted, 1290 are currently reusable.
CPU 0.07s/0.08u sec elapsed 0.91 sec.
INFO:  index "exp_lastname" now contains 34438 row versions in 1448 pages
DETAIL:  27488 index row versions were removed.
507 index pages have been deleted, 507 are currently reusable.
CPU 0.04s/0.07u sec elapsed 0.40 sec.
INFO:  index "exp_ci_lastname" now contains 34438 row versions in 1444 pages
DETAIL:  27488 index row versions were removed.
512 index pages have been deleted, 512 are currently reusable.
CPU 0.06s/0.03u sec elapsed 0.47 sec.
INFO:  index "exp_orbital_ptns" now contains 34438 row versions in 3001 pages
DETAIL:  27488 index row versions were removed.
2766 index pages have been deleted, 2766 are currently reusable.
CPU 0.12s/0.07u sec elapsed 1.58 sec.
INFO:  index "exp_stickers" now contains 34438 row versions in 2980 pages
DETAIL:  27488 index row versions were removed.
2683 index pages have been deleted, 2683 are currently reusable.
CPU 0.17s/0.07u sec elapsed 6.25 sec.
INFO:  index "exp_pid" now contains 34438 row versions in 2169 pages
DETAIL:  27488 index row versions were removed.
1989 index pages have been deleted, 1989 are currently reusable.
CPU 0.13s/0.06u sec elapsed 4.19 sec.
INFO:  index "exp_mac_address" now contains 34438 row versions in 2012 pages
DETAIL:  27488 index row versions were removed.
413 index pages have been deleted, 413 are currently reusable.
CPU 0.10s/0.13u sec elapsed 1.08 sec.
INFO:  index "exp_mac_address_normal" now contains 34438 row versions in 2014 pages
DETAIL:  27488 index row versions were removed.
416 index pages have been deleted, 416 are currently reusable.
CPU 0.06s/0.04u sec elapsed 1.20 sec.
INFO:  index "ua_user_data_exp_id_user_key" now contains 34438 row versions in 886 pages
DETAIL:  27488 index row versions were removed.
3 index pages have been deleted, 3 are currently reusable.
CPU 0.03s/0.06u sec elapsed 0.22 sec.
INFO:  index "ua_user_data_exp_login_key" now contains 34438 row versions in 1432 pages
DETAIL:  27488 index row versions were removed.
122 index pages have been deleted, 122 are currently reusable.
CPU 0.05s/0.05u sec elapsed 0.43 sec.
INFO:  vacuuming "pg_toast.pg_toast_18410"
INFO:  "pg_toast_18410": 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_18410_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:  vacuuming "public.current_connection"


it seems that a vacuum full on the whole DB is more aggressive.


My FSM setting are enough:

INFO:  free space map: 281 relations, 33804 pages stored; 27344 total pages needed
DETAIL:  Allocated FSM size: 1000 relations + 2000000 pages = 11780 kB shared memory.





Regards
Gaetano Mendola