Hi All,
I have enabled autovacuum in our PostgreSQL cluster of databases. What I
have noticed is that the autovacuum process keeps selecting the same
database to perform autovacuums on and does not select any of the others
within the cluster. Is this normal behaviour or do I need to do
something more elaborate with my settings?
Our main concern is the "blueface-service" database. The sipaccounts
table has some high traffic, mainly updates. At the end of an average
day's run without autovacuum this table, which is normally around 20MB
gets bloated to around 2.2GB (now, imagine a busy day) at which point
our nightly "cluster" cleans it up. However, we would like the
autovacuum to be more stringent with this particular table and keep the
bloat to a minimum.
Our setup is as follows:
OS version: Solaris 10 Update 3
DB version: PostgreSQL 8.2.4
I have checked the pg_catalog.pg_stat_all_tables view in each database
and the autovacuum/autoanalyze field is null for all our databases
except the blueface-crm one.
The autovacuum does appear to be running, but only selecting one
database each time.
------------------------------
Log Excerpt
------------------------------
DEBUG: autovacuum: processing database "blueface-crm"
DEBUG: autovacuum: processing database "blueface-crm"
DEBUG: autovacuum: processing database "blueface-crm"
DEBUG: autovacuum: processing database "blueface-crm"
DEBUG: autovacuum: processing database "blueface-crm"
DEBUG: autovacuum: processing database "blueface-crm"
--------------------------------------------
Auto Vacuum Settings:
--------------------------------------------
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1
stats_command_string = on
update_process_title = on
stats_start_collector = on
stats_block_level = on
stats_row_level = on
stats_reset_on_server_start = off
vacuum_cost_delay = 0
vacuum_cost_limit = 200
log_min_messages = debug1
If you require any additional info I'd be happy to pass it along.
Thanks
Bruce
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
