11.PG之VACCUM操作

1.VACUUM 概述

（1）垃圾数据清理

磁盘清理（清理dead tuple）、更新统计信息、重组数据、解决事务ID回卷问题。
语法结构
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] 
说明：
vacuum ：    不要求获得排它锁，找到那些旧的“死”数据，标记为不可用状态，不进行空间合并
vacuum full：    就是除了vacuum，进行空间合并，它需要lock table
vacuum analyze：    更新统计信息，使得优化器能够选择更好的方案执行sql
vacuum freeze:      表记录冻结，可解决事务id回卷的问题

（2）VACUUM标准形式

vacuum操作会重新回收dead tuple的磁盘空间，但是不会交给os，而是为新tuple留着。
如果删除的记录位于表的末端，且没有tuple，其所占用的空间将会被物理释放并归还操作系统。
页内没有事务可见的tuple，即整个页都可以删除时，会做truncate操作，把尾部的这些页统一从文件中删除，文件大小和表所占空间随之减少。
标准形式VACUUM可以和生产数据库操作并行运行（SELECT、INSERT、UPDATE和DELETE等命令将继续正常工作，但在清理期间你无法使用ALTER TABLE等命令来更新表的定义）。
VACUUM会产生大量I/O流量，这将导致其他活动会话性能变差。

(3)VACUUM FULL

VACUUM FULL通过把死亡空间之外的内容写成一个完整的新版本表文件来主动紧缩表。
这将最小化表的尺寸，但是要花较长的时间。
其实质是将当前删除记录后面的数据进行移动，使得整体的记录连贯起来，降低了“高水位标记”。

vacuum full会把空间返回给os，但是会有一些弊端：
1) 排他的锁表，阻塞相关表的所有操作;
2) 会创建一个表的副本，所以会将使用的磁盘空间加倍，最大可能达到两倍，如果磁盘空间不足，不要执行;
(4)VACUUM ANALYZE

统计信息由ANALYZE命令收集，它除了直接被调用之外还可以作为VACUUM的一个可选步骤被调用。
如VACUUM ANAYLYZE table_name，该命令将会先执行VACUUM再执行ANALYZE。
自动清理守护进程如果被启用，当一个表的内容被改变得足够多时，它将自动发出ANALYZE命令。
与回收空间(VACUUM)一样，对数据更新频繁的表保持一定频度的ANALYZE，
从而使该表的统计信息始终处于相对较新的状态，对于更新并不频繁的数据表，则不需要执行该操作。  
可以为特定的表，甚至是表中特定的字段运行ANALYZE命令，可以只对更新比较频繁的部分信息执行ANALYZE操作，
不仅可以节省统计信息所占用的空间，也可以提高本次ANALYZE操作的执行效率。 
自动清理守护进程不会为外部表发出ANALYZE命令，因为无法确定一个合适的频度

可以通过下面的命令来调整指定字段的抽样率，如：  
ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200  
注意：该值的取值范围是0--1000，其中值越低采样比例就越低，分析结果的准确性也就越低，
但是ANALYZE命令执行的速度却更快。如果将该值设置为-1，那么该字段的采样比率将恢复到系统当前默认的采样值
 
通过下面的命令获取当前系统的缺省采样值。  
postgres=# show default_statistics_target;  
default_statistics_target  
---------------------------  
 100 
(1 row)  
从上面的结果可以看出，该数据库的缺省采样值为100(10%)

(5)VACUUM FREEZE

PostgreSQL 目前默认的存储引擎，事务可见性需要依赖行头的事务号，因为事务号是32位的，会循环使用；
事务ID由32位数保存，而事务ID递增，当事务ID用完时，会出现事务id回卷问题，可以通过VACUUM FREEZE来解决该问题；
在一条记录产生后，如果再次经历了20亿个事务，必须对其进行freeze，否则数据库会认为这条记录是未来事务产生的（可见性判断）

回卷问题：
其中需要注意的是，XID 是用32位无符号数来表示的，也就是说如果不引入特殊的处理，当PostgreSQL的XID 到达40亿，会造成溢出，从而新的XID 为0。
而按照PostgreSQL的MVCC 机制实现，之前的事务就可以看到这个新事务创建的元组，而新事务不能看到之前事务创建的元组，这违反了事务的可见性。

PostgreSQL有三个事务ID有特殊意义：
0代表invalid事务号
1代表bootstrap（辅助程序）事务号
2代表frozon事务
说明：
frozon transaction id比任何事务都要老，可用的有效最小事务ID为3；VACUUM时将所有已提交的事务ID均设置为2，即frozon，之后所有的事务都比frozon事务新，因此VACUUM之前的所有已提交的数据都对之后的事务可见，PostgreSQL通过这种方式实现了事务ID的循环利用 

(6)AUTOVACUUM

autovacuum 是 postgresql 里非常重要的一个服务端进程，在一定条件下自动触发执行
autovacuum参数控制 autovacuum 进程是否打开,默认为 “on”，主要作用包括：
清理“死元组 ”（ UPDATE或DELETE操作后留下的）并对表进行分析
更新可用空间映射(free space map)，以跟踪表块中的可用空间
更新仅索引扫描所需的可见性图(visibility map)
“冻结”(freeze)表行，以便事务ID计数器可以安全地环绕

autovacuum进程触发条件
autovacuum_naptime：autovacuum唤醒间隔时间（s）
触发 vacuum：表上(update,delete 记录) >= autovacuum_vacuum_scale_factor* reltuples(表上记录数) + autovacuum_vacuum_threshold
触发 vacuum analyze：表上(insert,update,delete 记录) >= autovacuum_analyze_scale_factor* reltuples(表上记录数) + autovacuum_analyze_threshold
触发 vacuum freeze：指定表上事务的最大年龄配置参数autovacuum_freeze_max_age，默认为2亿，达到这个阀值将触发 autovacuum进程，从而避免 wraparound

VACUUM 使用建议
开启全局自动vacuum：修改配置文件postgresql.conf ，设置参数 autovacuum=on
持续关注表中dead tuple的状况、表级计划性的执行vacuum
查询需要vacuum的表, 即表dead tuple的量或比例, 默认情况下可能有少于20%的dead tuple.
可通过以下sql命令查询表的空间使用情况:
select relname,n_live_tup,n_dead_tup from pg_stat_all_tables where n_dead_tup<>0 order by n_dead_tup desc;

可使用安装目录bin下的vacuumdb或者vacuumlo工具对数据进行手动清理
vacuumdb工具清理数据库并对数据库执行分析操作  
vacuumlo工具清理数据库中无效的大对象
适当调大参数maintenance_work_mem，可加快vacuum的执行速度 
PostgreSQL 9.5引入了一个新的参数：jobs参数，可以并行的运行VACUUM。
对于有大量update 的表，vacuum full是没有必要的，因为它的空间还会再次增长
定期监控数据量变化较大的表，确认其磁盘页面占有量接近临界值时,可考虑vacuum full
注意：
vacuum只会删除那些已经结束的事务所关联到的旧有的已经不用的数据，如果一个事务还在运行，autovacuum就不会处理这个事务相关的数据了，如果一个事务长时间运行而没有结束，
就会导致最终autovacuum停止在那里。
如果应用中大量使用了table lock，会导致autovacuum 没有机会执行。

(7)什么情况下做VACUUM？
不锁表回收空间，只能回收部分空间。
频率：对于有较多实时更新的表，每天做一次。如果更新是每天一次批量进行的，可以在每天批量更新后做一次。
对系统影响：不会锁表，表可以正常读写。会导致CPU、I/O使用率增加，可能影响查询的性能

什么情况下做VACUUM FULL？
锁表，通过重建表，回收所有空洞空间。对做了大量更新后的表，建议尽快执行VACUUM FULL。
频率：至少每周执行一次。如果每天会更新几乎所有数据，需要每天做一次。
对系统影响：会对正在进行vacuum full的表锁定，无法读写。会导致CPU、I/O使用率增加。建议在维护窗口进行操作。可选择pg_repack工具操作
 

检查n_dead_tup较高的表进行vacuum操作
postgres=# select n_live_tup,n_dead_tup from pg_stat_all_tables where relname = 'test';
 n_live_tup | n_dead_tup
------------+------------
    91966 |   702346
(1 row)

执行碎片清理并收集统计信息
postgres=# vacuum(verbose,analyze) test;

总结:

如上就是PG数据库的vacuum的基础理论知识。