How to stop autovacuum silently - Mailing list pgsql-hackers
| From | Maxim Orlov |
|---|---|
| Subject | How to stop autovacuum silently |
| Date | |
| Msg-id | CACG=ezbWWHyGjsed5zuBKWNPZ=NXLEk2p9-5q5Q6+CwE6VA7eg@mail.gmail.com Whole thread Raw |
| Responses |
Re: How to stop autovacuum silently
|
| List | pgsql-hackers |
Hi!
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
=== 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
--
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.
Attachment
pgsql-hackers by date: