Andreas Hartmann schrieb:
Mario Weilguni schrieb:
Andreas Hartmann schrieb:
[…]
I just verified that the autovacuum property is enabled.
[…]
Did you have:
stats_start_collector = on
stats_block_level = on
stats_row_level = on
Otherwise autovacuum won't run IMO.
Thanks for the hint! The section looks like this:
stats_start_collector = on
#stats_command_string = off
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off
I'll check the logs if the vacuum really runs - as soon as I find them :)
-- Andreas
You might want to use these entries in your config:
redirect_stderr = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
Fit those to your needs, then you will find log entries in $PGDATA/pg_log/
And BTW, I was wrong, you just need to have stats_row_level=On,
stats_block_level doesn't matter. But in fact it's simple, if you don't
have 24x7 requirements type VACUUM FULL ANALYZE; and check if your DB
becomes smaller, I really doubt you can have that much indizes that 27MB
dumps might use 2.3 GB on-disk.
You can check this too:
select relname, relpages, reltuples, relkind
from pg_class
where relkind in ('r', 'i')
order by relpages desc limit 20;
Will give you the top-20 tables and their sizes, 1 page is typically
8KB, so you can cross-check if relpages/reltuples is completly off, this
is a good indicator for table/index bloat.
Regards,
Mario
