PostgreSQL Auto_Vacuum弄清问题,解决问题

（本文阅读预计时间：12分钟）

弄清楚PostgreSQL的vacuum对于维护好PostgreSQL和理解一些在基于PostgreSQL设计中的“点”是有必要性的。虽然数据库是有包容性的，但他有他自己的“脾气”，顺毛驴，如果你非要呛着他，踢你一脚也让你缓不过来。

今天的从PostgreSQL最重要的词汇vacuum开始，这也应该是运维PostgreSQL的人员必须要知道的一个词汇。说起这个问题其实就和PostgreSQL设计有关了，他并没有MySQL和Oracle的undo log，那么对于数据的回滚方面，必然要保留数据，在满足了mvcc的需求后，事务commit后，必然会在数据库中留下曾经的痕迹，而这些痕迹的抹除的工作就是vacuum需要进行的了。

vacuum回收的是在事务commit后因为保留回滚可能的数据行，将这些行重新标记成可以使用，释放空间，这点上还有一些连续性，这点和俄罗斯方块消消看的原理是一样的。

实际上频繁的更新和删除对于PostgreSQL并没有什么好处，所以归并一些update的操作对PostgreSQL是有利的。如果不加以控制则PostgreSQL会因为这样不恰当的操作导致空间的bloating，所以当有这样设计的应用的情况下，就必须有有利的回收这些空间的程序来进行这样的程序就是PostgreSQL称为autovacuum的进程。

那么到底回收是什么样的原理，回收操作对那些系统或者文件进行了操作，那么就牵扯到visibility map，Free space map，freeze tuple这几个问题了。visibility map是对数据页面文件的可读文件进行标记的文件，一个比特管理一个页面文件，标记为1则说明这个页面包含的tuples都是可见的有效的，如果这个bit设置为0，说明对于所有的事务，这个页面都是不可见的。

vm文件对于table和index都是有效的，一个数据文件对应一个vm文件，fm文件，记录数据表中的可用的空间记录

那么下面就会有一些问题提出了

问题1 

vacuum针对的是哪个级别的参数，是数据库级别的，还是表级别的，还是行级别的，vacuum针对的是当前数据库中的每个表，每个表都需要进行 

问题2 vacuum频率应该是什么时间来进行

在autovacuum_freeze_max_age达到限定值200000000两亿的事务执行的上限的时候，强制冻结事务在增加了，进行强行的vacuum当然如果达到这样的情况，那就不大妙了，这属于极限问题了，所以此时建议是停止业务，进行相关的回收的活动。

那么相关的autovacuum到底做了多少工作

1.清理由于update和delete所产生的dead tuples。

2.升级fsm文件保证fsm文件中的标记的空间和实际的表中占用的空间是对应的。

3.更新visibility map文件对那些已经commit后废弃的行进行标记，以及那些正在被事务占有的行进行标记。

4.定期的运行analyze保证表的状态定期及时的更新。

既然vacuum对于PostgreSQL是至关重要的，怎么能更有效的调整他并且在合理的范围频率中使用auto_vacuum就变得重要了。

下面我们就捋一捋，到底在出现表bloating的情况下，我们需要做什么

1.查看当前数据库的表的autovacuum的情况

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
     (n_live_tup
       * current_setting('autovacuum_vacuum_scale_factor')::float8
          + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC
LIMIT 10;
If your bloated table doe
复制

此时需要注意的是，n_dead_tup 到底有多少，以及最后一次last_autovacuum的状态。假设此时bloating的那个表没有出现在这个查询中，说明

另外在判断表bloating的情况下，主要的问题还要看是所有表都出现问题，还是极个别的表出现问题，如果是所有的表都出现问题的情况下。

那就要怀疑

1.auto_vacuum的进程工作没有

2.stat collector工作了没有

实际上postgres进程使用的是IPV6的地址进行相关进程的启动，所以必须保证IPV6在PostgreSQL的机器上是工作的，不能被禁用。

除此之外，大部分问题都来自于transaction运行的时间长，导致的我们的问题，所以下面的语句：

SELECT pid, datname, usename, state, backend_xmin,query,query_start
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
复制

查看当前的语句中有没有长时间无法运行完毕的，如果有就需要对这些语句进行相关的kill避免出现表bloating的问题。

除了这个问题之外，还有相关的复制槽的问题，查询当前数据库中是否有复制槽，并且工作情况如何，也是解决某些表bloating的问题，如果复制槽存在但没有数据库的目的地，那么复制槽会阻止表删除死行。

SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;
复制

最后两段式提交中，如果事务准备后，一直不提交也会出现无法进行对应表的清理死行的问题。我们通过rollback prepared的方式来将

SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
复制

在确认没有上面的问题的情况下，auto_vacuum是需要触发机制的，如何触发，在什么情况下触发是一个关键。

实际上vacuum是自动的，之前我们提及的autovacuum也是做这个事情的，那如何触发这个自动对表的vacuum

举例: 

vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
复制

相关的配置参数

在是否触发vacuum和analyze两个动作的情况下，有两个触发标准

表中的多少数据的百分比的问题，如果超过了某个百分比就触发vacuum和analyze同时这个百分比可能必须是超过多少行的情况下

这样设计的想法来自于，如果一个表的数据量小的情况下，变动几行可能就会触发factor类型的vacuum和analyze的操作实在是没有必要，而如果大表，可能要达到百分之几的数量级很难，这样的情况下，就会产生vacuum和analyze很难在正确的运行，所以对于大表要去修改相关的参数，让他们自己有自己的vacuum和analyze的正确的频率。

实际当中，如果有大量的数据输入的情况下，autovacuum_vacuum_scale_factor也是需要调整的

另外还可以对表来单独配置相关的信息，例如autoacuum_analyze_scale_factor的信息是可以针对表来进行配置，而表级别的配置比系统级别的配置权利要大，所以会先根据表内部的设置进行操作。

ALTER TABLE table SET (autovacuum_analyze_scale_factor = 0.02);
复制

或者如果表的大小限制了比例对于表正常进行autovacuum的情况下,我们

ALTER TABLE mytable SET (
   autovacuum_analyze_scale_factor = 0,
   autovacuum_analyze_threshold = 1000000
);
复制

通过定死多少行变动后，进行相关的auto_vacuum的工作。

最后我们在对配置文件中关于autovacuum的部分过一遍

autovacuum = on                 # Enable autovacuum subprocess 'on'
                                             # requires track_counts to also be on.


log_autovacuum_min_duration = -1      
                                               # -1 disables, 0 logs all actions and
                                               # their durations, > 0 logs only
                                               # actions running at least this number
                                               # of milliseconds.


autovacuum_max_workers = 3             # max number of autovacuum                                                                   subprocesses
                                                              # (change requires restart)
复制

记录相关的相关的最大课工作的autovacuum工作的进程

autovacuum_naptime = 1min              # time between autovacuum runs
autovacuum  工作中的间隔


autovacuum_vacuum_threshold = 50       # min number of row                                                                               updates before vacuum
在vacuum工作中最小的行变动(表级别)


autovacuum_analyze_threshold = 50      # min number of row updates                                                                     before analyze
在analyze 前最少的行变动
autovacuum_vacuum_scale_factor = 0.2   # fraction of table size                                                                                before   vacuum
在VACUUM 之前表整体变动的百分比
autovacuum_analyze_scale_factor = 0.1  # fraction of table size before                                                                       analyze
复制

在表分析前边变动的尺寸

同时对于一些不变动的表也可以关掉auto_vacuum，另外可以通过pg_class中的字段reloptions查看到底这个表的的auto_vacuum是打开的状态还是关闭的状态。

ALTER TABLE table_name SET (autovacuum_enabled = false);
复制

PostgreSQL 13.0 正式版发布通告

深度报告：开源协议那些事儿

从“非主流”到“潮流”，开源早已值得拥有

Oracle中国正在进行新一轮裁员，传 N+6 补偿

PostgreSQL与MySQL版权比较

PostgreSQL与Oracle：成本、易用性和功能上的差异

使用ora2pg完成从Oracle到Postgres的迁移

PostgreSQL活动篇

PostgreSQL培训认证篇

PostgreSQL技术干货

PostgreSQL热点文集

PostgreSQL新闻资讯

2020 PG亚洲大会珍藏

文章转载自公众号：AustinDatabases

作者：carol 11