is pg_autovacuum so effective ? - Mailing list pgsql-performance

Hi all,
I'm running since one week without use any vacuum full,
I'm using ony pg_autovacuum. I expect that disk usage will reach
a steady state but is not. PG engine: 7.4.5

Example:

The message table is touched by pg_autvacuum at least 2 time a day:

$ cat pg_autovacuum.log  | grep VACUUM | grep messages
[2005-02-15 16:41:00 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-16 03:31:47 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-16 12:44:18 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-16 23:26:09 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-17 09:25:41 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-17 19:57:11 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-18 05:38:46 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-18 14:28:55 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-19 02:22:20 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-19 13:43:02 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-20 02:05:40 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-20 14:06:33 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-20 23:54:32 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-21 08:57:20 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-21 19:24:53 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-22 05:25:03 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-22 15:20:39 CET] Performing: VACUUM ANALYZE "public"."messages"



this is what gave me the vacuum full on that table:


# vacuum full verbose messages;
INFO:  vacuuming "public.messages"
INFO:  "messages": found 77447 removable, 1606437 nonremovable row versions in 69504 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 97 to 2033 bytes long.
There were 633541 unused item pointers.
Total free space (including removable row versions) is 52819600 bytes.
1690 pages are or will become empty, including 0 at the end of the table.
22217 pages containing 51144248 free bytes are potential move destinations.
CPU 2.39s/0.55u sec elapsed 31.90 sec.
INFO:  index "idx_type_message" now contains 1606437 row versions in 7337 pages
DETAIL:  77447 index row versions were removed.
446 index pages have been deleted, 446 are currently reusable.
CPU 0.33s/0.75u sec elapsed 16.56 sec.
INFO:  index "messages_pkey" now contains 1606437 row versions in 5628 pages
DETAIL:  77447 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.15s/0.80u sec elapsed 4.22 sec.
INFO:  index "idx_service_message" now contains 1606437 row versions in 6867 pages
DETAIL:  77447 index row versions were removed.
499 index pages have been deleted, 499 are currently reusable.
CPU 0.67s/0.99u sec elapsed 8.85 sec.
INFO:  index "idx_service_message_expired" now contains 135313 row versions in 3308 pages
DETAIL:  77375 index row versions were removed.
512 index pages have been deleted, 512 are currently reusable.
CPU 0.21s/0.32u sec elapsed 6.88 sec.
INFO:  index "idx_expired_messages" now contains 1606437 row versions in 7070 pages
DETAIL:  77447 index row versions were removed.
448 index pages have been deleted, 448 are currently reusable.
CPU 0.34s/1.10u sec elapsed 29.77 sec.
INFO:  index "idx_messages_target" now contains 1606437 row versions in 14480 pages
DETAIL:  77447 index row versions were removed.
643 index pages have been deleted, 643 are currently reusable.
CPU 0.84s/1.61u sec elapsed 25.72 sec.
INFO:  index "idx_messages_source" now contains 1606437 row versions in 10635 pages
DETAIL:  77447 index row versions were removed.
190 index pages have been deleted, 190 are currently reusable.
CPU 0.68s/1.04u sec elapsed 31.96 sec.
INFO:  "messages": moved 55221 row versions, truncated 69504 to 63307 pages
DETAIL:  CPU 5.46s/25.14u sec elapsed 280.20 sec.
INFO:  index "idx_type_message" now contains 1606437 row versions in 7337 pages
DETAIL:  55221 index row versions were removed.
2304 index pages have been deleted, 2304 are currently reusable.
CPU 0.42s/0.49u sec elapsed 53.35 sec.
INFO:  index "messages_pkey" now contains 1606437 row versions in 5628 pages
DETAIL:  55221 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.31s/0.34u sec elapsed 13.27 sec.
INFO:  index "idx_service_message" now contains 1606437 row versions in 6867 pages
DETAIL:  55221 index row versions were removed.
2024 index pages have been deleted, 2024 are currently reusable.
CPU 0.51s/0.57u sec elapsed 16.60 sec.
INFO:  index "idx_service_message_expired" now contains 135313 row versions in 3308 pages
DETAIL:  41411 index row versions were removed.
1918 index pages have been deleted, 1918 are currently reusable.
CPU 0.30s/0.31u sec elapsed 36.01 sec.
INFO:  index "idx_expired_messages" now contains 1606437 row versions in 7064 pages
DETAIL:  55221 index row versions were removed.
2166 index pages have been deleted, 2166 are currently reusable.
CPU 0.94s/0.58u sec elapsed 34.97 sec.
INFO:  index "idx_messages_target" now contains 1606437 row versions in 14480 pages
DETAIL:  55221 index row versions were removed.
3404 index pages have been deleted, 3404 are currently reusable.
CPU 0.99s/1.03u sec elapsed 50.53 sec.
INFO:  index "idx_messages_source" now contains 1606437 row versions in 10635 pages
DETAIL:  55221 index row versions were removed.
1809 index pages have been deleted, 1809 are currently reusable.
CPU 0.84s/1.04u sec elapsed 35.44 sec.
INFO:  vacuuming "pg_toast.pg_toast_18376"
INFO:  "pg_toast_18376": found 0 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 1976 to 1976 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 6192 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 6192 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "pg_toast_18376_index" now contains 1 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  "pg_toast_18376": moved 0 row versions, truncated 1 to 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM




pg_class after the vacuum full for that table

 relfilenode | relname  | relpages |  reltuples
-------------+----------+----------+-------------
       18376 | messages |    63307 | 1.60644e+06


pg_class before the vacuum full for that table

 relfilenode | relname  | relpages |  reltuples
-------------+----------+----------+-------------
       18376 | messages |    69472 | 1.60644e+06



how was possible accumulate 6000 pages wasted on that table?

Between these two calls:
[2005-02-22 05:25:03 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-22 15:20:39 CET] Performing: VACUUM ANALYZE "public"."messages"

1768 rows where inserted, and I had 21578 updated for that rows ( each
row have a counter incremented for each update ) so that table is not
so heavy updated

I'm running autovacuum with these parameters:
pg_autovacuum -d 3 -v 300 -V 0.1 -S 0.8 -a 200 -A 0.1 -D


shall I run it in a more aggressive way ? May be I'm missing
something.



Regards
Gaetano Mendola