最近使用 PgHero 做监控,发现其中几个表的大小增长的非常快,从 500M 到 15G,但记录条数其实并没有显著增加。
\dt+ data.
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------------------------+-------+--------+------------+------------------------
data | xxxxxxxxxxxxxxxxxxxxs | table | xxxxxx | 15G |
初步怀疑是 vacuum 没有执行导致,手动执行 vacuum full xxxtable 之后果然体积变为了 500M 左右。问题很奇怪,pg 会有 autovacuum 机制,为什么 autovacuum 没有触发,然后用 PgHero 查看每个表的最后 vacuum 时间,震惊,所有表都没有触发 autovacuum:
为了验证是不是 PgHero 显示的问题,直接到 Pg 去查询效果也是一样:
SELECT schemaname AS schema,
relname AS table,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY 1, 2
这时候以为是 pg 配置问题,没有配置开启 autovacuum,检查之后发现 autovacuum 配置了开启并且各项参数都正常,于是去青云提工单,青云客服提示要达到一个 threshold 才能触发
就是说 autovacuum 触发的条件是:pg_stat_all_tables.n_dead_tup >= threshold + pg_class.reltuples * scale_factor
但是不可能所有表都达不到这个 threshold 吧,这不科学啊。由于 threshold、scale factor 都能从 pg settings 里读到,我们只要查出 pg_class.reltuples 和 pg_stat_all_tables.n_dead_tup 的值就可以验证 autovacuum 是否达到了触发条件。
先来查 pg_class.reltuples,返回正常:
SELECT nspname AS schemaname,
relname as tablename,
reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND relkind='r'
ORDER BY reltuples DESC;
然后来查 n_dead_tup,结果居然发现都是 0,纳尼?
select relname,
n_dead_tup,
n_live_tup
from pg_stat_all_tables
where schemaname !~ 'pg_';
relname | n_dead_tup | n_live_tup
--------------------------------------+------------+------------
xxxxxxxxxxxxxxxxtable | 0 | 0
问题找到!把这个发给青云之后,他们回复在他们的监控里有 reset stats 操作,把这个表重置了^_^ 这个问题的后果其实还是很严重的,对于 UPDATE or DELETE 频繁的表,没开 autovacuum 会导致表体积急速爆炸。
相关链接: