最近有个同事的一个查询 SQL 运行很慢，后来发现是有张表很久没有做过 vacuum 和 analyze 了，由于自从我将公司数据库版本升级到 9.1 以后，就不再通过 crontab 来执行 vacuum ，而是使用系统默认的 autovacuum 功能。先解释一下 autovacuum 的运行机制。
PostgreSQL 9.x 默认会开启 autovacuum，有一堆默认的参数，可以在 postgresql.conf 配置文件中修改，有关参数的含义可以参考官方文档。
autoanalyze 触发条件
~~~~~~~~~~~~~~~~~~~
如果从最后一次 autoanalyze 后， 插入，更新，删除的记录数之和大于阀值，将触发 autoanalyze，阀值计算公式如下
阀值 = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.reltuples
如果使用默认值，计算公式为 (50 + pg_class.reltuples * 0.1)
由于还没搞清楚系统如何统计最后一次 autoanalyze 以来变动的总记录数，所以暂时手动修改，见下面 SQL 语句的 a_n 字段注释。
autovacuum 触发条件
~~~~~~~~~~~~~~~~~~~
如果从最后一次 autovacuum 后， pg_stat_user_tables.n_dead_tup大于阀值，将触发 autovacuum，阀值计算公式如下
阀值 = autovacuum_vacuum_threshold +autovacuum_vacuum_scale_factor * pg_class.reltuples
如果使用默认值，计算公式为 (50 + pg_class.reltuples * 0.2)
一旦 autovacuum 执行，pg_stat_user_tables.n_dead_tup 会归零，所以不用像 a_n 字段那样处理。
测试
~~~
先建一张测试表。
CREATE TABLE test (
id serial NOT NULL PRIMARY KEY,
name VARCHAR(80)
); |
执行下面的 SQL 语句
SELECT
t1.relname,
-- 如果不是第一次运行 autoanalyze， 需要减去最后一次 autoanalyze 的 a_n 值
(t1.n_tup_ins + t1.n_tup_upd + t1.n_tup_del - 0) AS a_n,
(CAST(current_setting('autovacuum_analyze_threshold') AS BIGINT)
+ t2.reltuples * CAST(current_setting('autovacuum_analyze_scale_factor') AS NUMERIC)
) AS a_t, -- default = (50 + t2.reltuples * 0.1)
t1.n_dead_tup AS v_n,
(CAST(current_setting('autovacuum_vacuum_threshold') AS BIGINT)
+ t2.reltuples * CAST(current_setting('autovacuum_vacuum_scale_factor') AS NUMERIC)
) AS v_t, -- default = (50 + t2.reltuples * 0.2)
date_trunc('second', t1.last_autovacuum) AS last_av,
date_trunc('second', t1.last_autoanalyze) AS last_aa
FROM
pg_stat_user_tables t1,
pg_class t2
WHERE
t1.relname = t2.relname
AND t1.relname = 'test'; |
relname | a_n | a_t | v_n | v_t | last_av | last_aa
---------+-----+-----+-----+-----+---------+---------
test | 0 | 50 | 0 | 50 | |
(1 row)
字段解释
relname: 表名
a_n: analyze 的当前值，用来和analyze阀值比较
a_t: analyze 的阀值
v_n: vacuum 的当前值，用来和vacuum阀值比较
v_t: vacuum 的阀值
last_av: 最后一次 autovacuum 的时间
last_aa: 最后一次 autoanalyze 的时间 |
然后不断执行下面的插入语句，并观察上面 SQL 中各个值的变化, a_n 在增加
INSERT INTO test(name) VALUES(NULL); |
relname | a_n | a_t | v_n | v_t | last_av | last_aa
---------+-----+-----+-----+-----+---------+---------
test | 50 | 50 | 0 | 50 | |
(1 row) |
会发现一旦 a_n > a_t 时，会在 1 分钟内 (autovacuum_naptime 参数的默认配置) 触发 autoanalyze，这时 last_aa 会变化。
relname | a_n | a_t | v_n | v_t | last_av | last_aa
---------+-----+------+-----+------+---------+------------------------
test | 51 | 55.1 | 0 | 60.2 | | 2013-05-21 17:40:04+08
(1 row) |
修改上面的 SQL ，减去 autoanalyze 触发时的 a_n 的值（假如是 51）, 修改如下
....
(t1.n_tup_ins + t1.n_tup_upd + t1.n_tup_del - 0 - 51) AS a_n,
.... |
relname | a_n | a_t | v_n | v_t | last_av | last_aa
---------+-----+------+-----+------+---------+------------------------
test | 0 | 55.1 | 0 | 60.2 | | 2013-05-21 17:40:04+08
(1 row) |
继续执行下面语句，直到触发下一次 autoanalyze
INSERT INTO test(name) VALUES(NULL); |
relname | a_n | a_t | v_n | v_t | last_av | last_aa
---------+-----+------+-----+------+---------+------------------------
test | 56 | 60.7 | 0 | 71.4 | | 2013-05-21 17:43:04+08
(1 row) |
继续修改 SQL, 将其改为
....
(t1.n_tup_ins + t1.n_tup_upd + t1.n_tup_del - 0 - 51 - 56) AS a_n,
.... |
relname | a_n | a_t | v_n | v_t | last_av | last_aa
---------+-----+------+-----+------+---------+------------------------
test | 0 | 60.7 | 0 | 71.4 | | 2013-05-21 17:43:04+08
(1 row) |
下面测试如何触发 autovacuum, 不断执行下面的删除语句，并观察各值的变化
DELETE FROM test WHERE id = (SELECT MAX(id) FROM test) |
由于删除也会影响 a_n，所以会先触发 autoanalyze，当执行第 61 次删除时，等一分钟，让其触发 autoanalyze
relname | a_n | a_t | v_n | v_t | last_av | last_aa
---------+-----+------+-----+------+---------+------------------------
test | 61 | 54.6 | 61 | 59.2 | | 2013-05-21 17:49:05+08
(1 row) |
继续修改 SQL, 将其改为
....
(t1.n_tup_ins + t1.n_tup_upd + t1.n_tup_del - 0 - 51 - 56 - 61) AS a_n,
.... |
relname | a_n | a_t | v_n | v_t | last_av | last_aa
---------+-----+------+-----+------+------------------------+------------------------
test | 0 | 54.6 | 0 | 59.2 | 2013-05-21 17:50:05+08 | 2013-05-21 17:49:05+08
(1 row) |
这是会发现 last_av 已经有值了，说明 autovacuum 已经被触发了，观察上面的数据，当 autoanalyze 被触发时，
不但 a_t 降低到 54.6，v_t 也从 71.4 降到 59.2，小于 v_n (61)，所以被自动触发，同时 v_n 归零
参考文档
~~~~~~~
[1] http://www.network-theory.co.uk/docs/postgresql/vol3/Theautovacuumdaemon.html
[2] http://dba.stackexchange.com/questions/18664/are-regular-vacuum-analyze-stil-recommended-under-9-1
[3] http://serverfault.com/questions/53376/how-do-i-know-if-the-autovacuumer-in-postgres-8-3-is-actually-working