1、VACUUMVACUUM深度解析及最佳实践深度解析及最佳实践 唐成中启乘数唐成（网名osdba）中启乘数科技创始人，PostgreSQL修炼之道：从小工到专家的作者，PostgreSQL中国用户会副主席。历任过阿里巴巴高级数据库专家，从事过阿里巴巴Greenplum、PostgreSQL数据库的架构设计和运维。目前专注于高性能数据库解决方案及高性能存储技术。我们为什么要关心我们为什么要关心Vacuum?防止因事物ID回卷问题(Transaction ID Wraparound)而导致的宕机 表膨胀问题 性能问题.Transaction ID Wraparound 会发生什么？当还剩下1000万

2、可用的xid时，日志中出现如下告警：WARNING:database XXX must be vacuumed within 177009986 transactionsHINT:To avoid a database shutdown,execute a database-wide VACUUM in XXX.那么在xid被用到的剩余100万时，的log会报警并主动down库：If these warnings are ignored,the system will shut down and refuse to start any new transactions once there a

3、re fewer than 1 million transactions left until wraparound:ERROR:database is not accepting commands to avoid wraparound data loss in database XXXX表膨胀问题表膨胀问题 数据库的空间一直膨胀 表的空间一直膨胀 之前没有防治好，事后处理很麻烦 事后想缩减空间，如果通过vacuum full会锁表，无法在线处理 第三方工具来在线缩减空间，比较麻烦。风险问题 难受！Vacuum是什么？是什么？Vacuum:“真空吸尘器真空吸尘器”吸什么“尘”？垃圾数据？旧版

4、本数据？需要了解MVCC什么是什么是MVCC？Multiversion concurrency control(MCC or MVCC),is a concurrency control method commonly used by database management systems to provide concurrent access to the database and in programming languages to implement transactional memory.借助借助wiki上的解释：上的解释：两大功能读不阻塞写，写不阻塞读提供快照读（一致性读）的功

5、能MVCC如何实现如何实现 简单说，就是更新数据时，保留原先版本的数据，即一行数据存在多个版本。实现方法：回滚段的实现方法（Oracle、MySQL Innodb的实现方法）在原先的数据文件中的旧版本数据不删除，生成新版本的数据（PostgreSQL的实现方法）通常与事务的功能集成在一起：但MVCC时事物回滚或提交之后，旧版本的数据仍然需要保留一段时间，是延迟清除的。旧版本数据延迟清除的原因旧版本数据延迟清除的原因 就是为了提供快照读（一致性读）的功能 对于Read Commited隔离级别：需要保证当开始执行一个SQL之后，这个SQL是读一个不变的快照的数据，即使在这个SQL执行过程中有其它

6、的事物提交了，这些提交的数据对于这个SQL也是看不到了，这样保证了数据的一致性。对于Read Repeatable隔离级别：从这个事物开始之后，看到的数据就是一个不变的数据，执行相同的SQL总是可以看到相同的数据。所以叫“可重复读”快照读（一致性读）的用途快照读（一致性读）的用途 在一个繁忙的转帐的系统中，如果没有一致性读，此管理者中很难查询到一致的数据。用户A转账100元管理者数据库户名户名账户费用账户费用行号行号.用户A12202000.用户B7806000.我要查所有用户的账户费用的总和：select sum(money)from T;账户表T用户BVacuum就是为了清除旧版本数据就是

7、为了清除旧版本数据 Oracle数据库怎么没有听说有Vacuum?Oracle的旧版本数据是放在回滚段中的，也是需要回收的，只是这个过程完全是自动的。PostgreSQL的旧版本数据是放在数据文件中的。事物事物ID回卷问题是咋回事？回卷问题是咋回事？事物事物ID回卷问题也是与回卷问题也是与MVCC有关有关 PostgreSQL把行的版本信息是放在行上的。在行上加了两个字段xmin、xmax xmin和xmax是一个事物ID。PostgreSQL中的事物ID是一个32bit的数字。当每开始一个事物时，就分配一个新的事物ID，事物ID基本是递增的。这个事物最终是否提交还是回滚的状态是记录在comm

8、it log中的，即clog中。MVCC的原理的原理 更新操作：从把原先的行复制出一个新行，把新行上的xmin会填写当前操作的事务ID，而xmax填0，在旧行上的xmax填写当前的事务ID insert操作，把新插入行上的xmin会填写当前操作的事务ID即可，而xmax填0。delete操作：把要删除的行上的xmax会填写当前操作的事务ID即可事物事物ID回卷问题就是回卷问题就是ID用光了！用光了！32bit的事物ID总会用光 用光之后就需要，把旧的事物ID回收利用。如果没有及时回收利用，当还剩下之后100万的可用事物ID时，数据库为了安全起见，就会宕机。回收旧事物ID的工作也是有Vacuum

9、来完成的。这个动作是与清理旧版本数据一起完成的。Autovacuum vacuum是一件很重要的事，如果没有及时vacuum，轻则数据膨胀，重则数据库宕机。为了减轻DBA的压力，PostgreSQL设计的Autovacuum，即自动进行垃圾回收。默认情况下Autovacuum是打开的。Autovacuum参数的优化参数的优化 autovacuum_max_workers指定启动多少个autovacuum的进程进行垃圾回收。默认值是3，建议在写压力大的数据库中，调整成6或更大的值。autovacuum_naptime:autovacuum做完一轮的时间如果小于这个值，则会sleep到这个时间。指

10、调度的频繁程度 默认值是1min，可以保持不变。Autovacuum参数的优化参数的优化 autovacuum_vacuum_scale_factor、autovacuum_vacuum_threshold 指定表的数据量变化多少后就需要vacuum autovacuum_vacuum_scale_factor默认值是0.2，即20%autovacuum_vacuum_threshold默认值是50行 即当表的变更数据的行数超过：20%的行+50行时，vacuum就会开始操作这张表。对于大表来说,20%可能太大了，需要设置的小一些。alter table test01 set(autovacu

11、um_vacuum_scale_factor=0.05);事物事物ID的回收的回收 如果可分配的事物ID的范围（下图红色弧线部分)太小，如果autovacuum没有来的及清理，存在数据库宕机的风险 如果可保留到可分配的事物ID的范围太大，这需要频繁的回收目前数据库中的事物数据，产生太多的IO autovacuum_freeze_max_age，默认时2亿，即表示图中黄色部分的范围。0231当前分配的事务230231+230黄色表示目前数据库中的事务数据库中最旧的事务ID红色表示可分配的事务Id范围Autovacuum参数的优化参数的优化 autovacuum是尽量控制表的年龄在autovacu

12、um_freeze_max_age的范围内，但如果因各种原因（如autovacuum整理的过慢），还是有事物ID回卷问题导致数据库宕机的风险。所以需要监控数据库中表的年龄。方法是：select datname,age(datfrozenxid)from pg_database;即在pg_database视图中记录了数据库中最旧事物的年龄。这个值是vacuum时更新上去的（包括autovacuum）查看表的年龄查看表的年龄 pg_database只记录了这个数据库中最旧的年龄，没有记录是那张表 表的年龄是记录在pg_class的relfrozenxid字段中的 select relname,re

13、lfrozenxid,age(relfrozenxid)from pg_class where relfrozenxid 0 order by age(relfrozenxid)desc limit 10;lazy vacuum与与 aggressive vacuum vacuum如果每次全表扫描的进行垃圾回收，则太消耗IO资源了，所以lazy vacuum上场了。lazy vacuum不会全表扫描，之前做过垃圾回收的数据块被记录的，再次vacuum时会跳过这些块。注意lazy vacuum不会回收事物ID。如果表的年龄没有超过vacuum_freeze_table_age则会做lazy va

14、cuum vacuum_freeze_table_age默认是1亿5千万，autovacuum_freeze_max_age，默认是2亿，autovacuum_freeze_max_age vacuum_freeze_table_age降低降低vacuum对生产性能影响对生产性能影响 vacuum做一会儿工作，sleep一会儿，在做一些工作.vacuum_cost_delay是指sleep的时间，默认值是0，建议设置为1ms vacuum_cost_limit：当工作量到达一定是，就休眠一下，默认是200 vacuum_cost_page_hit=1 vacuum_cost_page_miss

15、=10 vacuum_cost_page_dirty=20 即内存中整理一个块工作量为1，磁盘上整理一个块代价是10，如果是脏块是20 vacuum_cost_limit在SSD下建议设置10000,Raid盘的机械硬盘为1000产生表膨胀的原因产生表膨胀的原因 弄清楚为什么vacuum为什么不清理那些旧版本数据，就明白了表膨胀的原因 pg_stat_all_tables.n_dead_tup查看具体的表有多少行旧数据没有清理 数据库上有长时间没有提交的事务 select*from pg_stat_activity，看状态是“Idle in transaction”的长时间事物 设置参数：id

16、le_in_transaction_session_timeout产生表膨胀的原因产生表膨胀的原因 未结束的2PC事务 SELECT gid,prepared,owner,database,transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction)DESC;ROLLBACK PREPARED transaction_id;COMMIT PREPARED transaction_id;产生表膨胀的原因产生表膨胀的原因 放弃的复制槽放弃的复制槽(replication slots)SELECT slot_name,slo

17、t_type,database,xmin FROM pg_replication_slots ORDER BY age(xmin)DESC;特别是逻辑复制 设置old_snapshot_threshold 强制回收旧版本数据 会导致类似Oracle的“ora-1555”错误产生表膨胀的原因产生表膨胀的原因 表太大，表太大，vacuum花了很久还没有整理完。花了很久还没有整理完。vacuum在单张表上无法并发 使用分区表，在PG11及以下版本，建议使用pg_pathman做分区 加大maintenance_work_mem或autovacuum_work_mem的值 vacuum_defer_c

18、leanup_age的值设置太大 备库上有长时间未结束的长查询 同时设置了hot_standby_feedback 这种情况出现的概率很低vacuum命令的一些有用选项命令的一些有用选项 FREEZE 如果表没有更新，建议加这个选项，这样执行完后，表上的旧xid都被回收了 DISABLE_PAGE_SKIPPING visibility map文件的内容可能有问题是，可以加这个选项。9.6版本及之后的功能 SKIP_LOCKED vacuum跳做一些冲突的锁。12版本的功能 INDEX_CLEANUP 清理索引。12版本的功能vacuum命令的一些有用选项命令的一些有用选项(续续)TRUNCA

19、TE 是否释放文件的空页空间给操作系统。12版本的功能 FULL 可以释放空间，但锁表，无业务的时候再执行。vacuum的一些参数可以设置到表上的一些参数可以设置到表上 让autovacuum不整理一些大表 可以在大表上关闭autovacuum参数,手工整理这些大表 alter table test01 set(autovacuum_enabled=off);copy命令加命令加freeze参数减少参数减少vacuum copy的freeze参数 直接在表上不放当前的事物ID 需要把create table或truncate table与copy命令在一个事务中执行 注意数据可以立即被其他session看到(违背了MVCC的可见性)