AutoVacuum介绍和调优

为什么要做vacuum

PostgreSQL使用多版本并发控制（MVCC）来保证数据在高并发环境中保持一致和可访问性，每个事务在它开始的时间点都在其自己的数据库快照上运行，这就意味着无法立即删除过时的数据。当进行UPDATE和DELETE操作时，PostgreSQL数据库会为其他正在运行的事务保留旧版本的元组，修改数据库的每个语句都会生成一个事务ID（xid）；PostgreSQL UPDATE可以看成是先DELETE然后INSERT。这就会导致数据库出现两个主要问题：事务ID回卷和表膨胀。

事务ID回卷

PostgreSQL会为事务分配一个事务ID，这个事务ID并不是无限大的，PostgreSQL使用一个32位的整数作为事务ID的值，其使用1到2^31轮转的方式来处理无限多的事务，也就是说，当事务ID到达2^31（2147483648）的时候，它将为接下来的事务继续分配1到2^31的事务ID，这种轮转事务ID的方法就是事务ID回卷。

当前的事务ID只能看到其之前的事务ID提交信息，如果当前一个事务ID是100， 当该事务ID达到最大值2^31之后会进行轮转，这样从101到2^31对于当该事务ID来说都是之后的事务ID，此时该事务ID不能看到100以后的所有事务，这样就会导致数据库数据丢失，会造成数据库的不可信。

vacuum freeze可以用来防止事务ID回卷，其意味着覆盖事务ID到frozen事务ID，上面的例子，当前的事务ID100是经历过回卷的，但是使用vacuum freeze将表中对应元组的xmax的值设置为2，那这样所有的事务都能看到该元组。

vacuum freeze同时也会清理死元组。

表膨胀

由于MVCC机制，PostgreSQL的DELETE操作并不会真正删除元组，而是将其标记为已删除状态，这些被标记为删除的元组称为死元组（dead tuples），UPDATE也是同理，可以看成是先DELETE然后再INSERT；由于DELETE并不是真正的删除数据，死元组依旧占用磁盘空间，就会出现虽然查询表数据很少，但是磁盘占用空间很大，这样就会形成表膨胀。

vacuum可以用来解决表膨胀的问题，vacuum会清理死元组，但是并不会重新组织活元组在表上的存储位置，其会维护表的空闲空间映射（FSM）以供接下来的元组INSERT操作从而避免占用更多的空间。

vacuum full则更干脆一些，会对原表进行重建，但是vacuum full会对表进行锁定，在执行vacuum full期间无法对表进行读写操作。同时，如果表的数据量太大，在使用vacuum full时执行时间会很长，请在业务低峰期进行操作。

Autovacuum

Autovacuum是一个能够自动执行vacuum和analyze命令的守护进程，其能够检查数据库中臃肿的表，并回收空间用以重用。autovacuum守护进程被设计成两个进程，autovacuum launcher和autovacuum worker。

autovacuum launcher是在autovacuum参数设置为on时postmaster启动的进程，autovacuum launcher进程用来调度autovacuum worker进程对表进行vacuum操作；autovacuum worker是实际执行vacuum操作的进程，它按照launcher进程的计划连接到数据库并选择某个表执行vacuum操作。

触发autovacuum的条件

当死元组的数量超过一定的值的时候，即死元组数量达到阈值就会触发autovacuum，具体的阈值计算公式如下：

以触发vacuum为例，其中autovacuum_vacuum_scale_factor和autovacuum_vacuum_threshold是PostgreSQL的两个参数。

• number_of_live_tuples是pg_class系统表的“reltuples”字段，可以使用select reltuples from pg_class where relname = '$TBL_NAME'获取。
• 死元组number_of_dead_tuples可以使用select n_dead_tup from pg_stat_user_tables where relname = '$TBL_NAME'获取。
• n_mod_since_analyze是自从上一次auto analyze以来被修改过的元组数量，包括INSERT、UPDATE、DELETE等。

当autovacuum_vacuum_scale_factor为0.2，autovacuum_vacuum_threshold为50，这个表中存活元组数量为2000的时候，能够计算得到的阈值为2000*0.2 + 50 = 450，当number_of_dead_tuples超过450就会触发自动清理。

number_of_dead_tuples > autovacuum_vacuum_scale_factor * number_of_live_tuples + autovacuum_vacuum_threshold

触发auto analyze的条件则是：

n_mod_since_analyze > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * number_of_live_tuples

当autovacuum_analyze_scale_factor为0.2，autovacuum_analyze_threshold为50，表中存活元组数量为2000的时候，计算阈值为2000 * 0.2 + 5 = 450，当n_mod_since_analyze超过450就会触发auto analyze。

可以通过如下查询来看元组信息和上一次进行autovacuum的时间：

SELECT relname  AS tablename,
       n_live_tup AS livetuples,
       n_dead_tup AS deadtuples,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables;

系统视图pg_stat_progress_vacuum可以被用来跟踪vacuum的实时状态，由于vacuum full是重写表，所以不会被记录到这个视图中。关于这个视图的详细信息可以查阅官方文档。

当表的年龄大于autovacuum_freeze_max_age时（云上默认是4亿），autovacuum进程会自动对表进行freeze。

数据库年龄一旦超过20亿，数据库会宕机不可用，则不会再接受新事物写入。需要单用户模式执行vacuum full才能修复。

查看数据库年龄：

SELECT datname, age(datfrozenxid) FROM pg_database WHERE datname <> 'template1' AND datname <> 'template0' ORDER BY age DESC;

查看年龄大的5张表：

SELECT relname, relfrozenxid,  age(relfrozenxid) aa FROM pg_class WHERE relfrozenxid != 0 ORDER BY aa DESC LIMIT 5;

autovacuum的相关参数

可以通过查询pg_settings系统表来查看autovacuum参数：

select category, name,setting,unit,source,min_val,max_val from pg_settings where category = 'Autovacuum' ;
category  |                name                 |  setting  | unit | source  | min_val |  max_val   
------------+-------------------------------------+-----------+------+---------+---------+------------
 Autovacuum | autovacuum                          | on        |      | default |         | 
 Autovacuum | autovacuum_analyze_scale_factor     | 0.1       |      | default | 0       | 100
 Autovacuum | autovacuum_analyze_threshold        | 50        |      | default | 0       | 2147483647
 Autovacuum | autovacuum_freeze_max_age           | 200000000 |      | default | 100000  | 2000000000
 Autovacuum | autovacuum_max_workers              | 3         |      | default | 1       | 262143
 Autovacuum | autovacuum_multixact_freeze_max_age | 400000000 |      | default | 10000   | 2000000000
 Autovacuum | autovacuum_naptime                  | 60        | s    | default | 1       | 2147483
 Autovacuum | autovacuum_vacuum_cost_delay        | 2         | ms   | default | -1      | 100
 Autovacuum | autovacuum_vacuum_cost_limit        | -1        |      | default | -1      | 10000
 Autovacuum | autovacuum_vacuum_scale_factor      | 0.2       |      | default | 0       | 100
 Autovacuum | autovacuum_vacuum_threshold         | 50        |      | default | 0       | 2147483647

autovacuum的工作量取决于两个参数：

• autovacuum_vacuum_cost_limit是自动清理一次性完成的工作量。
• autovacuum_vacuum_cost_delay是自动清理在达到autovacuum_vacuum_cost_limit参数指定的开销后休眠的毫秒数。

autovacuum常见问题

• 长事务/事务未提交影响autovacuum正常工作

  由于MVCC机制，长事务/事务未提交不允许autovacuum清理死元组，如果当前事务在执行删除或更新操作之前拍摄的数据快照，那autovacuum会跳过这些死元组；出现autovacuum不能正常工作的时候应优先排查idle in transaction中的会话。

  可以通过如下SQL查询autovacuum进程的锁信息：

  select locktype, relation::regclass, pid, mode, granted from pg_locks where pid in($autovacuum_pid);

  可以通过如下SQL查询长事务以及等待事件信息：

  select pid,wait_event_type,wait_event,state,backend_start,xact_start,query_start,state_change, query from pg_stat_activity where state not in ('idle', 'active');

  确认后通过如下SQL杀死该事务：

  select  pg_terminate_backend(pid);

• 废弃的复制槽影响autovacuum正常工作

  复制槽中存储着备机追上主机需要的信息，如果备机宕机或者严重落后于主机，将无法清理在主节点上复制槽中的列。

  可以通过如下SQL查询包含旧事务的复制槽：

  SELECT slot_name, slot_type, database, xmin, catalog_xmin FROM pg_replication_slots ORDER BY age(xmin), age(catalog_xmin) DESC;

  使用pg_drop_replication_slot()删除废弃的复制槽。

• autovacuum时占用资源高，或者经常导致中断

  这时一般是由于autovacuum执行次数过于频繁，需要调整参数降低其执行次数：

  • 适当增大autovacuum_vacuum_cost_delay和减少autovacuum_vacuum_cost_limit的值。
  • 减少autovacuum_max_workers的数量。

  如果业务量比较大，可以做定时任务在业务低峰期进行清理，也可以设置定时任务为特殊的表单独设置autovacuum参数。

• 表膨胀的速度加快

  在autovacuum正常运行的情况下，如果业务量增加，可能在短时间内产生大量死元组，导致autovacuum跟不上节奏，会导致查询性能下降；可通过修改autovacuum的相关参数进行优化：

  • 适量降低autovacuum_vacuum_cost_delay，适量增加autovacuum_vacuum_cost_limit。
  • 适量增大autovacuum_max_workers增加并行。
• autovacuum执行卡住或执行很慢

  如果autovacuum执行卡住或执行很慢，尝试手动执行vacuum。

  1. 通过SQL查询autovacuum运行情况，找到autovacuum的PID：

     SELECT datname, usename, pid, now() - xact_start AS runtime, query
     FROM pg_stat_activity 
     WHERE upper(query) LIKE '%VACUUM%' 
     ORDER BY xact_start;

  2. 多次执行以下命令，判断autovacuum是否正常运行：

     SELECT pg_terminate_backend($PID);

     确认autovacuum正常运行后，对需要清理的表手动执行vacuum freeze终止autovacuum进程：

     verbose选项可选。

     vacuum freeze verbose $TBL_NAME;