Running 8.2.4.
The following is in my postgresql.conf:
# - Query/Index Statistics Collector -
#stats_command_string = on
update_process_title = on
stats_start_collector = on # needed for block or row stats
# (change requires restart)
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off # (change requires restart)
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
autovacuum = on # enable autovacuum subprocess?
# 'on' requires
stats_start_collector
# and stats_row_level to also be on
autovacuum_naptime = 10min # time between autovacuum runs
#autovacuum_vacuum_threshold = 500 # min # of tuple updates before
autovacuum_vacuum_threshold = 200 # min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 125 # min # of tuple updates before
#autovacuum_analyze_threshold = 250 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
autovacuum_vacuum_scale_factor = 0.1 # fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.05 # fraction of rel size before
#autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
# analyze
autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
vacuum
# (change requires restart)
autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
How do I know if the autovacuum is actually running?
The reason I believe its not - the database in question is being hit
VERY HARD with both updates and queries. Its a forum, and there are
updates on essentially every access (user's IP address is updated, time
last "touched" the account is updated, etc)
Anyway, after anywhere from a few hours to a day or so, performance goes
straight in the toilet. The system starts thrashing the disk hard -
indicating that there's a major problem trying to keep the working set
in memory; if not caught quickly it deteriorates to the point that
access time rises so that the maximum connection limit is hit and then
users get "Dbms connection errors" (while the load average goes sky-high
as well and disk I/O is pinned).
A manual "Vacuum full analyze" fixes it immediately.
But... .shouldn't autovacuum prevent this? Is there some way to look in
a log somewhere and see if and when the autovacuum is being run - and on
what?
--
Karl Denninger (karl@xxxxxxxxxxxxx)
http://www.denninger.net
%SPAMBLOCK-SYS: Matched [@postgresql.org+], message ok
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
