CODEWITHSUNDEEP
postgresqlamazon-auroravacuum
adnan kamili
adnan kamili

Reputation: 9455

Postgres - pg_statistic vacuum timeout

In my Aurora Postgres server I continuously see a vacuum timeout every minute for the following:

autovacuum: VACUUM pg_catalog.pg_statistic

I tried doing it manually and got following output:

INFO:  vacuuming "pg_catalog.pg_statistic"
INFO:  "pg_statistic": found 0 removable, 409109 nonremovable row versions in 19981 out of 19990 pages
DETAIL:  408390 dead row versions cannot be removed yet, oldest xmin: 4230263474
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 9 frozen pages.
0 pages are entirely empty.
CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.07 s.
INFO:  vacuuming "pg_toast.pg_toast_2619"
INFO:  "pg_toast_2619": found 0 removable, 272673 nonremovable row versions in 61558 out of 61558 pages
DETAIL:  219 dead row versions cannot be removed yet, oldest xmin: 4230263474
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.14 s, system: 0.00 s, elapsed: 0.14 s.
VACUUM

Query returned successfully in 5 secs 55 msec.

Anyone can point to the reason why this is happening?

enter image description here

Upvotes: 2

Views: 4392

Answers (1)

D-Shih
D-Shih

Reputation: 46219

I think your autovacuum might work as well, Timeout:VacuumDelay might only be a metric From AWS

A process is waiting in a cost-based vacuum delay point.

The most similar setting is autovacuum_vacuum_cost_delay which exists in PostgreSQL.

That want to slow down autovacuum. autovacuum will sleep for these many milliseconds when a cleanup reaching autovacuum_vacuum_cost_limit cost is done.

We can try to use pg_stat_user_tables to verify the latest time of autovacuum.

SELECT
  schemaname, relname,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables;

Upvotes: 3

Related Questions