Hi! Recently, one of our customers had reported a not working autovacuum. After a minor investigation, I've found that autovacuum launcher did, actually, run vacuum as expected, but with no results. At the same time, no warnings or other anomies were present in the logs.
At first, I've thought may be statistics is broken, thus vacuum is not
working as expected. But in fact, something
more interesting is had happened.
The pg_class.relfrozenxid was set to some rubbish value from the future,
thus broken in template1 DB, so any new
database will have it's broken too. Then, we create "blocker" DB and then
in vac_update_datfrozenxid() we get "bogus" (from the future) value
of relfrozenxid and *silently* return. Any other new created DB will not
be autovacuumed.
Funny, but from the perspective of DBA, this looks like autovacuum is not
working any more for no reasons, although
all the criterion for its launch is clearly observed.
AFAICS, there are several solutions for this state:
- run vacuumdb for all DB's
- manually update broken pg_class.relfrozenxid
- lowering of autovacuum_freeze_max_age to trigger prevent of transaction
ID wraparound
I do understand, this behaviour hardly can be described as a bug of some
sort, but could we make, at least, a useful
message to help to clarify what is going on here?
=== REPRODUCE ===
$ cat <<EOF >> pgsql/data/postgresql.conf
autovacuum_naptime = 1s
autovacuum_freeze_max_age = 100000
EOF
$ ./pgsql/bin/pg_ctl -D pgsql/data -l pgsql/logfile start
waiting for server to start.... done
server started
$ ./pgsql/bin/psql postgres
psql (17devel)
Type "help" for help.
postgres=# \c template1
You are now connected to database "template1" as user "orlov".
template1=# update pg_class set relfrozenxid='200000' where oid = 1262;
UPDATE 1
template1=# do $$
begin
while 120000 - txid_current()::text::int8 > 0 loop
commit;
end loop;
end $$;
DO
template1=# create database blocker;
CREATE DATABASE
template1=# create database foo;
CREATE DATABASE
template1=# \c foo
You are now connected to database "foo" as user "orlov".
foo=# create table bar(baz int);
CREATE TABLE
foo=# insert into bar select bar from generate_series(1, 8192) bar;
INSERT 0 8192
foo=# update bar set baz=baz;
UPDATE 8192
foo=# select relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup,
n_dead_tup, last_vacuum, last_autovacuum, autovacuum_count
from
pg_stat_user_tables where relname = 'bar';
relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup |
last_vacuum | last_autovacuum | autovacuum_count
---------+-----------+-----------+-----------+------------+------------+-------------+-----------------+------------------
bar | 8192 | 8192 | 0 | 8192 | 8192 |
| | 0
(1 row)
foo=# update bar set baz=baz;
UPDATE 8192
foo=# select relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup,
n_dead_tup, last_vacuum, last_autovacuum, autovacuum_count
from
pg_stat_user_tables where relname = 'bar';
relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup |
last_vacuum | last_autovacuum | autovacuum_count
---------+-----------+-----------+-----------+------------+------------+-------------+-----------------+------------------
bar | 8192 | 16384 | 0 | 8192 | 16384 |
| | 0
(1 row)
... and so on
--
Best regards,
Maxim Orlov.
0001-Add-warning-if-datfrozenxid-or-datminmxid-is-not-set.patch
Description: Binary data
