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

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

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会导致表体积急速爆炸。

相关链接:

共收到 3 条回复

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

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

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