|
Hi Forum, Should autovacuum reclaim most of the free space of a
database? We are trying to configure our database and running
pg_autovacuum to streamline our database. We have increased the
max_fsm_pages to a value larger than the total pages needed (see the output
from a full vacuum bellow “LAST FEW LINES OF FULL VACUUM”)
and turned on pg_autovacuum. Nevertheless, it seems that a full vacuum that we run at
night finds a lot of free space (see “EXCERPT FROM THE FULL VACUUM TO SHOW THE
VACUUM OF ONE TABLE”). I would have expected
that with the configuration of our database and with autovacuum working during
the day, the amount of space that a full vacuum would find would be minimal. We are running pg_autovacuum with its defaults
parameters. I can see that autovacuum is working because the CPU
utilization for the autovacuum PID goes up every five minutes or so and then it
goes down to almost nothing. LAST FEW
LINES OF FULL VACUUM INFO: free space map: 483 relations, 219546 pages
stored; 153104 total pages needed DETAIL: Allocated FSM size: 1000 relations + 170000
pages = 1057 kB shared memory. EXCERPT
FROM THE FULL VACUUM TO SHOW THE VACUUM OF ONE TABLE INFO: "en0029": found 66035 removable,
1310162 nonremovable row versions in 417 87 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 233 to 1165 bytes long. There were 1746 unused item pointers. Total free space (including removable row versions) is
20825932 bytes. 1453 pages are or will become empty, including 0 at the end
of the table. 2345 pages containing 16260040 free bytes are potential move
destinations. CPU 2.20s/0.22u sec elapsed 62.59 sec. |
