点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！
大家都知道PostgreSQL数据库是有数据清理的，有人工执行清理，也有自动清理，但是这2种的清理方式对性能是有不同的影响，特别是OLTP环境中，每次不管是人工清理还是自动清理dead tuple，都会对数据库的IO有明显的影响，说明如下：PostgreSQL 的数据库系统中是需要进行autovacuum 进行表级别的数据清理的。需要理解为什么需要autovacuum？说明基于PostgreSQL 的原理每个表中的行会存在多个版本的数据，为了完成数据库的MVCC 多版本控制，以及数据的UNDO 的功能在这些过期版本的行被弃用后，会产生众多的死行dead tuple. 在这样的情况下，如果不及时的将这些dead tuple 进行清理，轻则影响磁盘空间的在利用，重则影响数据库的性能，在测试中发现，在每次进行百万级别的dead tuple清理，数据库会有7%-10%的性能下降，多次往复亦是如此，同时会导致本来一张表3G的表就能存储，但最终由于dead tuple可能达到80G甚至更大的空间。同时每个dead tuple 还牵扯这索引的问题，以及多版本比对时造成的性能损耗，所以一个表的 dead tuple 越多，数据查询的性能也就越来越低。 这也是postgresql 核心问题之一。
- 表示是否开起autovacuum。当需要冻结xid（事务ID）时，尽管此值为off，PG也会进行vacuum。
log_autovacuum_min_duration- 默认：log_autovacuum_min_duration = -1(数字型) 单位ms
- 在规定时长内未完成的vacuum予以记录日志，-1表示禁用，0表示所有的，大于0仅记录超过时间的。
- 默认：autovacuum_max_workers = 3(数字型)
- autovacuum最大工作清理进程数,CPU核多IO好的情况下，可以增加，但是注意内存消耗，有多少个worker就会有多少倍的autovacuum_work_mem内存使用，会消耗较多内存，重启数据库生效。
- 默认：autovacuum_naptime = 1min(分钟)
autovacuum_analyze_threshold- 默认：autovacuum_analyze_threshold = 50(数字型)
- 自动analyze操作的最小行数，有利于对SQL语句进行更精准匹配到最好的执行计划。
autovacuum_vacuum_scale_factor- 默认：autovacuum_vacuum_scale_factor = 0.2(数字型)
- autovacuum的vacuum操作所需的变更量阈值,这个表的update/delete的tuple总数大于(pg_class.reltuples*autovacuum_vacuum_scale_factor+autovacuum_vacuum_threshold)时, 触发vacuum操作,建议不要太高频率，否则会因为vacuum产生较多的XLOG。
autovacuum_analyze_scale_factor- 默认：autovacuum_analyze_scale_factor = 0.1(数字型)
- 表示autovacuum的analyze操作所需的变更量阈值,当这个表的INSERT/update/delete的tuple总数大于(pg_class.reltuples*autovacuum_analyze_scale_factor+autovacuum_analyze_threshold)时, 触发analyze操作。
autovacuum_freeze_max_age- 默认：autovacuum_freeze_max_age = 200000000(数字型)
- 某表的pg_class.relfrozenxid的最大值，如果超出此值则重置xid,冻结表的记录。
autovacuum_vacuum_cost_delay- 默认：autovacuum_vacuum_cost_delay = 20ms (数字型)
- 当autovacuum进程执行时，对vacuum执行cost进行评估，如果超过autovacuum_vacuum_cost_limit的值时，则延迟这么长的时间。
autovacuum_vacuum_cost_limit3.1 全局级别配置
参数autovacuum_vacuum_cost_limit和autovacuum_vacuum_cost_delay是控制autovacuum worker 的主要两个参数。autovacuum_max_workers控制一次清理工作中最大能使用的进程数量。默认情况下，autovacuum_vacuum_cost_limit将被禁用 (-1)，这意味着其他参数的值vacuum_cost_limit将生效。因此最重要的建议是设置一个值，autovacuum_vacuum_cost_limit以帮助我们单独控制每个工作进程所需要的工作量的大小。在测试中发现最常见错误是将autovacuum_max_workers其设置为非常高的值。想必这会使 autovacuum 运行得更快(错，错，错)。请务必明白和理解autovacuum_vacuum_cost_limit是在所有工作进程共分配的(也就是共享的)。所以工作进程的数量越高，相反每个工作进程的运行速度就越慢。如上描述，较慢的工作进程意味着表的清理不能在有效的时间之内完成，导致表内存有还未清理完成的dead tuple记录，在DML操作中会扫描大量无用的记录，无疑是增加了DML的执行时间和获取数据结果的时间，对性能来说有一部分损耗，同时每个清理的工作进程都可以占用最多maintenance_work_mem。默认值autovacuum_max_workers=3 就足够了。3.2 表级别配置
实例级别的调整设置可能对至少一些表不起作用。这些异常值需要特殊处理，并且在表级别调整设置可能变得不可避免。我将从那些过于频繁地成为 autovacuum 候选者的表开始。带有该设置的 PostgreSQL日志提供参数log_autovacuum_min_duration记录了清理表的详细信息，以及那些花费大量时间和精力的 autovacuum 运行的详细信息。也可以通过比较两个不同时间戳中的autovacuum_count来获得 autovacuum 运行的概要。pg_stat_all_tables我们需要考虑的是 HOT（仅堆元组）更新和fillfactor的n_tup_hot_upd可以使用同一视图 (pg_stat_all_tables)分析hot only tuple更新信息，对此进行调整可以大大降低dead tuple的清理。设置这些自动清理信息参数，可以调整特定的表级别设置。例如：alter table bmsql_order_line set
(autovacuum_vacuum_scale_factor=0.0,
autovacuum_vacuum_threshold=130000,
autovacuum_analyze_scale_factor=0.0,
autovacuum_analyze_threshold=630000,
autovacuum_enabled=true, fillfactor=82);
既然现在PostgreSQL有比较大的潜在的数据膨胀的问题，如何利用好PostgreSQL，避免这种机制引起的数据膨胀和相关的负面影响，让PostgreSQL更好地为业务系统服务，那么解决方法不外乎从两个方面入手：一方面需要尽量减少数据膨胀，具体方法就是设置合适的fillfactor这个参数，让每个数据块预留一定的空间用于记录更新；当有预留更新的空间，新记录会在预留的空间内更新，因为旧数据跟更新后的数据在同一个数据块中，所以索引本身不必更新；可以降低IO发生的次数，并且提高性能；一般来说设置为建议为80，这个值可以根据应用的特点进行调整，比如：如果没有update，只有insert/delete这样的操作，可以用默认的100；如果update的几率比较大，比如每一条数据都需要改，那么50是一个好的开始。另一方面是让被占用的磁盘空间尽快回收，具体方法就是设置好vacuum相关的参数，让数据库内的垃圾数据及时和有效地得到回收。中心思想就是需要让回收的动作在系统设置允许的时间内完成垃圾数据的回收清理，这里面涉及到一些具体的参数，需要强调一下，系统默认的参数autovacuum_vacuum_scale_factor=0.2，以及autovacuum_analyze_scale_factor=0.1，这两个系统默认设置对于大表明显太大，建议对于大表可以有针对性的设置。具体的方法，用户可以更加自己系统的硬件配置，负载特征以及表的实际情况，设置合适的值，主要是autovacuum*相关的设置。
