2

If the parameter autovacuum is on, and the parameter track_counts is on also - why it's look like the autovacuum has never worked?

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze 
from pg_stat_user_tables 
where last_vacuum is not null 
or last_autovacuum is not null;

no rows...

Which actions needed to make it run?

My version is : PostgreSQL 9.6

user2671057
  • 1,411
  • 2
  • 25
  • 43
  • 1
    Have you actually changed (updated, deleted, inserted) rows in those tables? If yes, more than the threshold for autovacuum? Do you have any connections that have the state "idle in transaction"? –  May 07 '18 at 08:12
  • yeah, create a table insert a few thousand rows (use generate_series()) delete (or update) some or all of those rows, then wait a bit. – Jasen May 07 '18 at 08:14
  • Did you change any autovacuum settings from the default values? Are there autovacuum workers running? Do you get any messages if you set `log_autovacuum_min_duration = 0`? – Laurenz Albe May 07 '18 at 08:17
  • I take 1 table as example - in the last week, more than 160K rows inserted.. the parameters : autovacuum_vacuum_threshold, autovacuum_analyze_threshold both set to 50.. I didn't change anything from the default. I don't know if someone else did.. – user2671057 May 07 '18 at 08:31
  • I assume you can post result of `select setting, name from pg_settings where source <> 'default' and name like '%vacuum%';` also do you have any rows after removing `where` at all?.. – Vao Tsun May 07 '18 at 09:24
  • results of query: 30000, log_autovacuum_min_duration. and yes I have there a lot of tables. – user2671057 May 07 '18 at 09:31
  • anybody? this is a production env.. changes in volumns happens all the time.. how can I ensure that the autovacuum will run?? – user2671057 May 09 '18 at 11:31

1 Answers1

0

From the docs:

Postgres AutoVacuum will run when num_obselete_tuples exceeds the vacuum_threshold.

vacuum_threshold = base_threshold + (scale_factor * num_tuples)

  • base_threshold = autovacuum_vacuum_threshold
  • scale_factor = autovacuum_vacuum_scale_factor
  • num_tuples can be found by querying pg_class
SELECT relname, reltuples FROM pg_class

You should be able to use the numbers above to determine how many dead_tuples are required before the autovacuum will start.

You can see the n_dead_tuples with:

SELECT n_dead_tup FROM pg_stat_user_tables where relname = ‘transaction’;

A UPDATE or DELETE statement will create a dead_tuple. You can check the autovacuum settings and plug numbers in to find the autovacuum threshold and then update that many rows in your table which ought to trigger the autovacuum daemon.

You can look for running autovacuum processes with:

SELECT query, xact_start, state FROM pg_stat_activity WHERE query LIKE 'autovacuum:%';
Dave McLean
  • 136
  • 8