VACUUM 不会从表中删除死行的三个原因__VACUUM 不会从表中删除死行的三个原因: 世界上功能最强大的开源数据库...

### **作者简介**

#### Laurenz Albe cybertec公司工程师

### **译者简介**

#### 王志斌，从事数据库产品相关工作，主要致力于postgresql数据库高可用解决方案及云端产品化工作。

### **校对者简介**

#### 崔鹏,PostgreSQL爱好者。海能达PostgreSQL高级DBA。

### **为什么是VACUUM？**
#### 每当更新或删除PostgreSQL表中的行时，都会留下死元组。VACUUM摆脱了它们，以便空间可以重复使用。如果一个表没有被清理，它就会变得臃肿，这会浪费磁盘空间并减慢表的顺序扫描（以及索引扫描）。

#### VACUUM还负责冻结表行，以避免在事务ID计数器环绕时出现问题，但这是另一回事。

#### 通常，您不必处理所有这些，因为PostgreSQL中内置的autovacuum 守护程序会为您完成这些工作。

### **问题**

#### 如果你的表变得臃肿，首先你需要检查的是 autovacuum 是否已处理它们：
```
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
    / (n_live_tup
       * current_setting('autovacuum_vacuum_scale_factor')::float8
          + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC
LIMIT 10;
```
#### 如果你的臃肿的表没有出现在这里，n_dead_tup是0并且last_autovacuum是 NULL，你可能有统计信息收集器的问题。

#### 如果臃肿的表就在顶部，但last_autovacuum为NULL，则您可能需要将autovacuum 配置为更积极，以便完成对表的处理。

#### 但有时结果会如下所示：
```
schemaname |    relname   | n_live_tup | n_dead_tup |   last_autovacuum
------------+--------------+------------+------------+---------------------
 laurenz    | vacme        |      50000 |      50000 | 2018-02-22 13:20:16
 pg_catalog | pg_attribute |         42 |        165 |
 pg_catalog | pg_amop      |        871 |        162 |
 pg_catalog | pg_class     |          9 |         31 |
 pg_catalog | pg_type      |         17 |         27 |
 pg_catalog | pg_index     |          5 |         15 |
 pg_catalog | pg_depend    |       9162 |        471 |
 pg_catalog | pg_trigger   |          0 |         12 |
 pg_catalog | pg_proc      |        183 |         16 |
 pg_catalog | pg_shdepend  |          7 |          6 |
(10 rows)
```
#### autovacuum最近在这里运行，但它没有释放死元组！

#### 我们可以通过运行VACUUM (VERBOSE)来验证问：
```
test=> VACUUM (VERBOSE) vacme;
INFO:  vacuuming "laurenz.vacme"
INFO:  "vacme": found 0 removable, 100000 nonremovable row versions in
       443 out of 443 pages
DETAIL:  50000 dead row versions cannot be removed yet,
         oldest xmin: 22300
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
```
### **为什么VACUUM不能删除死元组？**
#### VACUUM只能删除不再需要的那些行版本（也称为“元组”）。如果删除事务的事务 ID（存储在xmax系统列中）比PostgreSQL数据库（或共享表的整个集群）中仍处于活动状态的最旧事务更旧，则不清理元组。

#### 这个值（VACUUM上面输出中的 22300）称为“xmin 水平”。

#### 在 PostgreSQL集群中，有三件事可以阻止这个xmin范围：

### **长时间运行的事务：**
#### 您可以使用以下查询找到它们及xmin的值：
```
SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
```
#### 可以使用 pg_terminate_backend() 函数来终止阻塞VACUUM的数据库会话。

### **废弃的复制槽：**
#### 复制槽是一种数据结构，可防止PostgreSQL服务器丢弃备用服务器仍需要的信息以赶上主服务器。

#### 如果复制延迟或备用服务器关闭，复制槽将阻止VACUUM 删除旧行。

#### 您可以使用以下查询找到所有复制槽及其 xmin 值：
```
SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;
```
#### 使用 pg_drop_replication_slot() 函数删除不再需要的复制槽。

#### 注意：如果 hot_standby_feedback = on，这只会发生在物理复制中。对于逻辑复制，也有类似的危险，但只有系统目录受到影响。在这种情况下检查列 catalog_xmin。

### **孤立的准备事务：**
#### 在两阶段提交期间，首先使用PREPARE语句准备分布式事务，然后使用 COMMIT PREPARED语句提交。

#### 一旦准备好事务，它就会一直“徘徊”，直到它被提交或中止。它甚至必须在服务器重启后幸存下来！通常，事务不会长时间保持准备状态，但有时会出现问题，管理员必须手动删除准备好的事务。

#### 您可以使用以下查询找到所有准备好的交易及其 xmin 值：
```
SELECT gid, prepared, owner, database, transaction AS xmin

FROM pg_prepared_xacts

ORDER BY age(transaction) DESC;
```
#### 使用ROLLBACK PREPAREDSQL语句删除准备好的事务。

### **PostgreSQL中文社区欢迎广大技术人员投稿**
### **投稿邮箱：press@postgres.cn**