POSTGRESQL AUTO_VACUUM 弄清问题,解决问题

弄清楚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 针对的是当前数据库中的每个表,每个表都需要进行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);