Databases
StackOverflow2013
Postsdbo
POpostgresql autovacuum not working although process is running
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POpostgresql autovacuum not working although process is running
    primarykey
    data
    text
    <p>I have PostgreSQL 9.2 running on Ubuntu. The autovacuum process is running on the server.</p> <p>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.</p> <p>There is nothing relating to vacuum in the logs.</p> <p>Doing <code>ps -ef | grep -i vacuum</code></p> <p>produces: <code>postgres 1406 1130 0 Oct13 00:00:14 postgres: autovacuum launcher process</code></p> <p>Running query <code>SELECT last_autovacuum , last_autoanalyze FROM pg_stat_user_tables;</code> produces no records in last autovacuum column, and 2 entrys in the suto_analyze column.</p> <p>The pertinent section of postgresql.conf is:</p> <pre><code>#------------------------------------------------------------------------------ # 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, &gt; 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 </code></pre> <p>Any thoughts? </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USuser1448820
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POpostgresql autovacuum not working although process is running
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSee http://www.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.
      singulars
      1. POpostgresql autovacuum not working although process is running
      1. USMartin Strejc
    2. CO"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?
      singulars
      1. POpostgresql autovacuum not working although process is running
      1. USjjanes
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload