postgresql autovacuum not working although process is running

Question

I have PostgreSQL 9.2 running on Ubuntu. The autovacuum process is running on the server.

The database has heavy load, 1000's of additions and 100's of deletions daily and yet it seems that autovacuum isn't firing up.

There is nothing relating to vacuum in the logs.

Doing ps -ef | grep -i vacuum

produces: postgres 1406 1130 0 Oct13 00:00:14 postgres: autovacuum launcher process

Running query SELECT last_autovacuum , last_autoanalyze FROM pg_stat_user_tables; produces no records in last autovacuum column, and 2 entrys in the suto_analyze column.

The pertinent section of postgresql.conf is:

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on         # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
log_autovacuum_min_duration = 250   # -1 disables, 0 logs all actions and
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
autovacuum_max_workers = 3      # max number of autovacuum subprocesses
                    # (change requires restart)
#autovacuum_naptime = 1min      # time between autovacuum runs
autovacuum_vacuum_threshold = 128   # min number of row updates before
                    # vacuum
autovacuum_analyze_threshold = 128  # min number of row updates before
                    # analyze
#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
                    # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                    # autovacuum, in milliseconds;
                    # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1  # default vacuum cost limit for
                    # autovacuum, -1 means use
                    # vacuum_cost_limit

Any thoughts?

Lenes

See postgresql.org/docs/9.2/static/monitoring-stats.html last_autovacuum - Last time at which this table was vacuumed by the autovacuum daemon. I guess it is the timestemp of finishing the autovaccuum. Just if daemon produces high load it't mostly possible running anyway. Try to run vacuum from the command line as an SQL command to see how long does vacuum run on each table. – Bobbee

"1000's of additions and 100's of deletions" is not all that much activity. Do you have bloat in the tables? Does n_dead_tup suggest vacuuming is actually due for any tables? Maybe vacuuming just isn't needed very often for you. Setting log_autovacuum_min_duration=0 should get more stuff written to the logs, although perhaps not enough to prove to you that it is working well. What is suto_analyze? – Nevins

Answer 1

The relevant entry in the quoted configuration is probably:

autovacuum_vacuum_scale_factor = 0.2

With this default, it's normal that autovacuum skips a table if less than 20% of its rows have been deleted or updated (or less than autovacuum_vacuum_threshold rows but at 128 that shouldn't matter here).

See its definition in the documentation:

autovacuum_vacuum_scale_factor (floating point)

Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

To have autovacuum process certain tables if you don't like the defaults, you may lower this parameter on a case by case basis. For example to set it at 1%:

ALTER TABLE tablename SET (autovacuum_vacuum_scale_factor=0.01);

Gasperoni