弄清楚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_autovacuumFROM pg_stat_all_tablesORDER BY n_dead_tup / (n_live_tup * current_setting('autovacuum_vacuum_scale_factor')::float8 current_setting('autovacuum_vacuum_threshold')::float8) DESCLIMIT 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);
