Обсуждение: Autovacuum settings between systems
I am using 8.4.2 on centos 5.4 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
I have autovacuum processes that appear to have been running most of the day.
There aren't any messages in the log, but there must be something wrong for it to take this long?
datname | relname | mode | granted | usename | substr | query_start | | procpid
+ |+ |+ |+ |+ |+ |+ |+ age |+
database | table_pkey | AccessShareLock | t | postgres | autovacuum: VACUUM ANALYZE database.table | 2010-09-28 10:38:23.217668-04 | 04:55:14.134574 | 13494
database | t8040_monthly_price_min_max | ShareUpdateExclusiveLock | t | postgres | autovacuum: VACUUM ANALYZE database.t8040_monthly_price_min_ | 2010-09-28 10:38:23.217668-04 | 04:55:14.134574 | 13494
database | | ExclusiveLock | t | postgres | autovacuum: VACUUM ANALYZE database.table | 2010-09-28 10:38:23.217668-04 | 04:55:14.134574 | 13494
This table has this many rows.
count
-------
67083
(1 row)
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
autovacuum = on # Enable autovacuum subprocess? 'on'
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
#autovacuum_vacuum_cost_delay = 20 # 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
vacuum_freeze_min_age = 1000000000
I have autovacuum processes that appear to have been running most of the day.
There aren't any messages in the log, but there must be something wrong for it to take this long?
datname | relname | mode | granted | usename | substr | query_start | | procpid
+ |+ |+ |+ |+ |+ |+ |+ age |+
database | table_pkey | AccessShareLock | t | postgres | autovacuum: VACUUM ANALYZE database.table | 2010-09-28 10:38:23.217668-04 | 04:55:14.134574 | 13494
database | t8040_monthly_price_min_max | ShareUpdateExclusiveLock | t | postgres | autovacuum: VACUUM ANALYZE database.t8040_monthly_price_min_ | 2010-09-28 10:38:23.217668-04 | 04:55:14.134574 | 13494
database | | ExclusiveLock | t | postgres | autovacuum: VACUUM ANALYZE database.table | 2010-09-28 10:38:23.217668-04 | 04:55:14.134574 | 13494
This table has this many rows.
count
-------
67083
(1 row)
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
autovacuum = on # Enable autovacuum subprocess? 'on'
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
#autovacuum_vacuum_cost_delay = 20 # 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
vacuum_freeze_min_age = 1000000000