数据库 帮青云 Debug 了一个 autovacuum 不执行的大坑

hooopo · 2018年05月25日 · 最后由 hooopo 回复于 2018年05月28日 · 7722 次阅读

PgHero

最近使用 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:

Last Autovacuum Time

为了验证是不是 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 会导致表体积急速爆炸。

相关链接:

学习几个 pg 相关的词,autovacuum,dead tuple,感谢😁

我记得很久之前青云写了个优化 hbase 的 blog,第一条就是把 wal 关了......

需要 登录 后方可回复, 如果你还没有账号请 注册新账号