Is Autovacuum running?

From: Brad White <b55white(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Is Autovacuum running?
Date: 2023-02-20 19:42:15
Message-ID: CAA_1=92TNOLUZcR2-7PnJiBB1e0QTsSagr8j8=bMzVgzwEUhPg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm concerned that Autovacuum may not be running based on the results of
this query.

SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
gives 211 rows like this...

*relname | last_vacuum |
last_autovacuum*BusinessIncidentCategories
| null | null
Valid Use | null | null
Serial Pool Part Types | null | null
BusinessIncidentLog | null | null
Rate Categories | null | null

I don't see any process with 'auto' or 'vacuum' in the name in TaskManager.
I don't see anything similar set up in Services to run in the background.
I do see pg_ctl running for each instance of the server running, 9.4 and 14.

The settings look ok as far as I can tell.

SELECT name, setting FROM pg_settings WHERE name='autovacuum';
name | setting
autovacuum | on

SELECT name, setting FROM pg_settings WHERE name='track_counts';
name | setting
track_counts | on

SELECT relname, reloptions FROM pg_class;
These are not turned OFF, and I assume the default is ON.
*relname | reloptions*
pg_statistic | null
pg_type | null
Activity Codes | null
Activity Codes_ID_seq | null
......

SELECT * from pg_settings where category like 'Autovacuum';
autovacuum on Autovacuum Starts the autovacuum subprocess. sighup bool
default on on
autovacuum_analyze_scale_factor 0.1 Autovacuum Number of tuple inserts,
updates, or deletes prior to analyze as a fraction of reltuples. sighup real
default 0 100 0.1 0.1
autovacuum_analyze_threshold 50 Autovacuum Minimum number of tuple inserts,
updates, or deletes prior to analyze. sighup integer default 0 2.15E+09 50
50
autovacuum_freeze_max_age 2E+08 Autovacuum Age at which to autovacuum a
table to prevent transaction ID wraparound. postmaster integer default 1E+08
2E+09 2E+08 2E+08
autovacuum_max_workers 3 Autovacuum Sets the maximum number of
simultaneously running autovacuum worker processes. postmaster integer
default 1 8388607 3 3
autovacuum_multixact_freeze_max_age 4E+08 Autovacuum Multixact age at which
to autovacuum a table to prevent multixact wraparound. postmaster integer
default 10000000 2E+09 4E+08 4E+08
autovacuum_naptime 60 s Autovacuum Time to sleep between autovacuum runs.
sighup integer default 1 2147483 60 60
autovacuum_vacuum_cost_delay 20 ms Autovacuum Vacuum cost delay in
milliseconds, for autovacuum. sighup integer default -1 100 20 20
autovacuum_vacuum_cost_limit -1 Autovacuum Vacuum cost amount available
before napping, for autovacuum. sighup integer default -1 10000 -1 -1
autovacuum_vacuum_scale_factor 0.2 Autovacuum Number of tuple updates or
deletes prior to vacuum as a fraction of reltuples. sighup real default 0
100 0.2 0.2
autovacuum_vacuum_threshold 50 Autovacuum Minimum number of tuple updates
or deletes prior to vacuum. sighup integer default 0 2.15E+09 50 50

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bryn Llewellyn 2023-02-20 19:57:56 Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
Previous Message pf 2023-02-20 19:36:39 Re: pg_dump'ed file contains "DROP DATABASE"