How to stop autovacuum silently
| От | Maxim Orlov |
|---|---|
| Тема | How to stop autovacuum silently |
| Дата | |
| Msg-id | CACG=ezbWWHyGjsed5zuBKWNPZ=NXLEk2p9-5q5Q6+CwE6VA7eg@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: How to stop autovacuum silently
(Peter Geoghegan <pg@bowt.ie>)
|
| Список | 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.
Вложения
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: initdb --no-locale=C doesn't work as specified when the environment is not C
Следующее
От: "Daniel Verite"Дата:
Сообщение: Re: [ psql - review request ] review request for \d+ tablename, \d+ indexname indenting