PostgreSQL中VACUUM操作的优化、监控与故障排查技巧

1.前言

本文详细介绍了PostgreSQL中VACUUM操作的基本原理。它描述了监视和调优维护数据库实例运行状况的数据库引擎的机制。

PostgreSQL使用基于快照的并发协议，在修改数据时创建多个版本的数据行。这些数据行版本用于使用计算快照读取数据的可见版本，而无需获取数据行上的读锁。PostgreSQL维护每一行数据的事务id(插入和删除的事务id)，并使用事务id和计算快照来确定行的可见性。由于数据的旧版本导致数据不断增长，因此扫描数据(表扫描或索引扫描)所花费的时间会增加。为了优化扫描操作的响应时间并有效地使用空间，您需要回收用于维护版本的版本和元数据(例如，事务ID)。回收事务ID，冻结事务ID，避免达到事务ID空间的最大限制。冻结事务ID涉及在行标头中标记标志或将事务ID更改为最小值。该操作考虑所有数据行(活动数据行和旧版本的数据行)，并根据阈值事务ID frozenxid修改行头或事务ID。

VACUUM操作回收已删除的版本(垃圾收集)和事务ID(冻结事务ID)。VACUUM操作以不同的数据可用性级别以不同的模式对数据进行操作。冻结事务ID对数据库系统的健康至关重要，因为每当使用的事务ID空间进入保留空间时，系统就会阻塞写入进程。例如，请参阅我们从最近的一次严重宕机中学到的东西。您配置的自动清理作业不断尝试回收事务ID，但它们可能会失败。此失败可能是由于配置不足，也可能是因为事务id的创建率太高，以至于自动真空作业无法跟上工作负载。本文档的目的是展示如何使用操作以及机制来调优和监视操作的不同方面。

2.MVCC简介

PostgreSQL通过维护数据行的多个副本来实现基于快照的并发控制机制。这种机制也称为多版本并发控制(MVCC)。没有统一的方法来维护旧版本的数据行，每个数据库管理系统(Oracle、SQL Server和MySQL)实现不同的机制来满足他们的需求。

PostgreSQL存储管理器并不区分多个版本的数据行。PostgreSQL存储表的所有数据行和用于限定数据行的元数据。**此元数据包括创建数据行和(如果行被更新或删除)删除数据行的事务的事务id。**维护额外的标头信息以解析计算快照(事务级或语句级快照)的行可见性。该机制通过将现有数据行标记为已删除，强制对数据行进行UPDATE操作，以创建包含已修改列的新行。例如，假设有一个ID为100的事务。如果更新行X，系统将X行删除的事务ID标记为100，并创建插入的事务ID为100的新行X”。PostgreSQL维护一个从旧版本到新版本的前向指针。在这个例子中，指针是从X到X”。

使用乐观并发控制机制的数据库引擎在事务或语句的开头创建快照。该快照包括有关数据库引擎中事务状态的信息，例如，最小的事务ID。如果任何事务ID小于最小的事务ID，则提交或回滚给定的事务ID。PostgreSQL快照包含以下信息:

• 最小的事务ID。任何低于此数字的事务ID都将提交或回滚。
• 最大的事务ID。任何高于此数字的事务ID都被视为未提交的事务。
• 开放事务列表。例如，在语句或事务开头活动的事务id列表。

PostgreSQL使用数据行中存储的快照信息、提交日志信息和事务id来确定给定的行是否对语句或事务可见。为了简化行可见性过程，PostgreSQL缓存事务的提交状态，这是从行头中的提交日志(CLOG)中获得的。尝试解析该行可见性的后续进程使用此缓存。

这种事务和快照系统将PostgreSQL与其他MVCC实现区分开来，原因如下:

1. 事务COMMIT和事务ROLLBACK是O(1)操作，因为它们在提交日志(2位事务状态)中设置标志位。此操作避免重放日志条目以撤消回滚的事务。

2. 读取旧版本的行不涉及使用undo日志段构造行的额外成本。通过使用常规堆访问方法访问所有行(包括行的旧版本和新版本)。当页面出现膨胀时，这种表示可能会导致在读取数据时产生更多的I/O，例如当有许多更新或删除的行时。

3. 截断失效元组或版本不是任何常规数据操作语言(DML)或读取操作的一部分。一个例外是UPDATE操作无法在页面上找到空间。在分配新页之前，该操作尝试删除死行(类似于删除死行的VACUUM操作)，以便在同一页上容纳该行的新版本。

3.Vacuum操作简介

VACUUM操作回收删除或死(删除的行，在数据库实例中不可见)版本，并将插入的行标记为可见行(冻结插入的事务ID)。它以不同的模式操作表数据，具有不同的数据可用性级别。下面几节重点介绍VACUUM操作的关键操作以及该命令的用法。

语法

使用下面的VACUUM命令调用VACUUM操作:

VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns
[, ...] ]

其中Options可以使用下边的这些值：

FULL [ boolean ]
FREEZE [ boolean ]
VERBOSE [ boolean ]
ANALYZE [ boolean ]
DISABLE_PAGE_SKIPPING [ boolean ]
SKIP_LOCKED [ boolean ]
INDEX_CLEANUP [ boolean ]
TRUNCATE [ boolean ]

table_and_columns 采用的是下边的格式：

table_name [ ( column_name [, ...] ) ]

VACUUM操作具有不同的模式和不同的锁定级别。锁语义将在另一节中解释。如果在FULL模式下执行VACUUM操作，则大多数选项都不适用，因为它会为表和索引创建新的页面。下面的列表提供了VACUUM命令的选项概述:

1.FULL
	该选项主要做以下工作：
		■将活动元组复制到新表以创建新页面。
		■在新创建的数据页上重新创建新的索引。
		■切换存储，并交换出pg_class.relfilenode目录以指向新存储。
	该操作类似于CLUSTER命令的操作，只是它不使用任何索引。当它重新创建新的数据和索引层时，它需要两倍于表和索引的大小。如果存在副本，则该操作将在WAL中写入新创建的页面。VACUUM FULL选项可以使用两倍的空间量(旧副本、新副本和WAL记录中的页面映像)。
2.FREEZE
	此扩展冻结事务id并从索引中删除死行。
3.ANALYZE
	更新查询优化器使用的表或列的统计信息。该命令是独立的，但是为了方便起见，可以作为VACUUM操作的一部分运行。
4.DISABLE_PAGE_SKIPPING
	VACUUM操作跳过对可见页的处理(通过使用与表关联的可见性映射来确定)，因为它们可能不包含死行或未冻结的数据。此选项覆盖此行为并考虑所有页面。
5.INDEX_CLEANUP
	它的标志是在PostgreSQL 12中引入的，索引不作为操作的一部分被清除。当该标志设置为FALSE时，此命令将跳过索引项的清理。
6.SKIP_LOCKED
	VACUUM操作通过获取页上的排他锁来处理每个页。如果页面繁忙，VACUUM操作必须等待锁定。此选项避免考虑繁忙的页面。

下边的示例就采用了上边的一些选项：

vacuum (INDEX_CLEANUP OFF) new_orders;
vacuum (ANALYZE, SKIP_LOCKED OFF, INDEX_CLEANUP OFF) orders;
vacuum FULL history;
vacuum ANALYZE stock;

回收空间

VACUUM操作在以下阶段回收与死行相关的空间:

1. 只修剪堆元组(HOT)链: DML UPDATE操作对非键列进行操作，这会导致数据行链化。VACUUM操作从链中删除死行，并指向链中的第一个有效版本。如果链中的所有行都是死的(例如，当所有行都被删除并且没有进程可以在系统中看到这些行时)，那么整个链将被丢弃。
2. 清除索引:删除与死行或已删除行相关联的索引项，以清除索引项。这个阶段可以通过删除与死行相关的条目来降低索引的高度。
3. 删除死行:识别死的数据行，并回收与这些数据行相关的空间。
4. 整理数据页:重新排列块上的行，以保持或保持连续的空闲空间。

冻结事务ID

如前所述，PostgreSQL使用数据行的事务id来查找行的可见性。每个事务ID是一个32位的值，PostgreSQL在整个数据库实例中只使用20亿个事务ID。一个32位的事务ID包含40亿个事务ID，但是为了支持事务ID的封装，PostgreSQL使用20亿个作为最大空间。有关更多信息，请参见PostgreSQL事务ID的官方文档。

冻结事务ID涉及使用各自的标志更新元组标头(插入的事务ID被冻结)。在PostgreSQL 9.2版本之前，元组中的事务ID(插入的事务ID)被替换为最小事务ID(称为冻结的事务ID)。

具有高只写工作负载的数据库实例可能会消耗整个20亿个事务ID空间。任何进一步的操作(例如事务ID 20亿+ 1)都可能由于事务ID的带符号比较而导致错误的结果。为了避免这个问题，PostgreSQL会在达到内部定义的阈值后阻塞写操作。最后100万个事务id被保留。当系统达到阈值时，分配新的事务id。该进程阻塞所有写操作，包括用户实例化的VACUUM操作。该系统可以在单用户模式下执行VACUUM操作来恢复。单用户模式下的VACUUM操作使用主动模式，并通过回收事务id来修复系统，事务id的更改对系统中的所有进程都是可见的。插入的行是可见的，删除的行是不可见的。从这些事务id中回收的空间将用于更多事务。这个过程称为冻结事务id。冻结事务ID对数据库系统的健康至关重要，因为每当使用的事务ID空间进入保留空间时，系统就会阻塞写入器。有关系统阻塞写入器的示例，请参阅我们从最近的宕机中断中学到的东西。（https://mailchimp.com/what-we-learned-from-the-recent-mandrill-outage/）

autovacuum进程

为了避免阻塞写事务和避免数据库空间膨胀，请注意冻结事务id并回收死行/元组。这些操作可以通过在候选表上执行VACUUM命令来处理。有关识别候选表的更多信息，请参阅监视和调优部分。这些命令读取表的所有页面，并在符合条件的页面上执行所需的清理。在表上花费的时间取决于表的大小和缓冲区缓存(shared_buffers)中脏页的数量。频繁地运行这些操作会导致大量的系统资源消耗，而不会处理任何页面，这可能是因为表的状态没有变化。但是，减少运行这些操作的频率会增加命令执行的持续时间，并可能降低系统的吞吐量。

autovacuum进程是一个内部进程，它根据数据库系统中维护的统计信息(每个表或数据库)对表执行VACUUM操作。此过程根据autovacuum设置考虑需要注意的表，并执行所需的VACUUM操作。该进程通过生成辅助进程并发地清理多个表。默认情况下，PostgreSQL会生成三个autovacuum worker。您可以根据工作负载修改此值。如果需要处理许多表，请尝试增加辅助进程。

PostgreSQL配置参数控制与进程相关联的工作进程的最大数量。此配置选项是一个静态选项，这意味着需要重新启动数据库实例才能获得配置的工作进程数。 使用autovacuum_max_worker参数的示例如下:

ALTER SYSTEM SET autovacuum_max_workers=8;

autovacuum进程执行常规VACUUM命令支持的所有操作，如回收死行、冻结事务ID、刷新表统计信息等。自动真空处理表在以下两种模式之一:

• 正常模式:一种不阻塞的操作，不会阻塞用户对表的操作。自动真空终止正常操作的执行，只要它阻止用户发起的操作。
• 激进模式或不可中断反回卷模式:一种阻塞操作，通过阻塞数据定义语言(DDL)操作运行到完成。

4.监控和调优VACUUM操作

大多数客户使用默认或次优的VACUUM或auto vacuum配置选项，这些选项不能反映他们的工作负载。这些默认配置值是几年前根据当时的硬件速度定义的。我们建议您根据当前可用的硬件资源和工作负载的性质更新VACUUM配置选项，以帮助您避免性能或停机问题。

VACUUM和自动真空操作有许多配置选项或数据库标志可用。以下部分描述了一些对于维护数据库实例的运行状况至关重要的选项。

内存

VACUUM或自动真空操作批量处理一个表;它收集一组元组或块，并在移动到下一组元组之前处理它们。对每批元组执行以下操作:

修剪HOT链，它通过从链元组中删除死行来合并链。

删除死行——与回滚事务或提交的已删除行相关的数据行，这些行上没有可见的快照。

冻结行以重用事务ID，以启用事务ID的重用。

通过扫描完整索引来删除与死行或已删除行关联的索引项。

重新排列块上的行以保持连续的空闲空间。

VACUUM和AUTOVACUUM操作所使用的内存量由maintenance_work_mem和auto vacuum_work_mem参数确定。它们的默认值为64 MB。例如，考虑一个TPCC表orders，它包含100亿行，有两个索引，10%的数据被更新(留下10亿死行)。VACUUM操作通过存储8字节(为了计算目的，假设页号和行号占用8字节)的信息来创建批量元组;64 MB可以容纳800万个条目。要应用VACUUM操作，订单表至少需要9次迭代。这个迭代导致9次完整的索引扫描(索引在100亿行上)，这会消耗大量的内存和CPU。此外，它还增加了完成VACUUM操作的时间。

为了避免这些问题，您需要按照以下方式配置内存相关选项:

●实例大小
●表上的索引数(特别是内存不适合的大索引)
●工作负载的特征。

对于AUTOVACUUM过程，这些选项在每个AUTOVACUUM工作进程中使用。每个进程使用配置的内存。

注意:这些配置选项不是动态配置参数，因此需要重新加载配置。

maintenance_work_mem选项示例如下:

tpcc# SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
16GB
tpcc# SELECT pg_reload_conf();
pg_reload_conf
----------------
tpcc# SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
24GB
(1 row)

冻结事务ID

现代存储设备比旧的存储设备便宜。增加的存储空间可能会导致性能问题，但不会导致数据库系统中断。回收事务ID或冻结事务ID是VACUUM操作中最关键的操作。当事务ID达到预留空间时，PostgreSQL会阻塞所有写事务。PostgreSQL在使用最后1000万个事务id时写入警告消息。当出现该消息时，必须调度VACUUM操作，否则数据库系统可能会由于事务ID封装问题而变得不可用。

如果VACUUM或AUTOVACUUM配置选项不适应工作负载，则VACUUM或AUTOVACUUM操作可能无法冻结id。写事务速率过高。以下配置设置可以增加VACUUM的频率操作，它们在表上重复AUTOVACUUM操作，以便赶上写事务的速率。频繁的操作会产生更多的WAL记录，因为VACUUM操作会为数据或索引页中的所有更改写入WAL记录，这些更改会与运行时活动相冲突。请根据预期的WAL数据修改以下配置:

● vacuum_freeze_table_size: 
指定触发激进VACUUM操作时的域值。它会考虑表中所有的页（不仅仅包含死元组）。
此选项的值越高，则可以延长操作时间和可能导致事务回卷问题。
若要频繁处理该表，请根据系统中可用的连接数和cpu数配置此实例级值。

● vacuum_freeze_min_age:
在常规VACUUM操作过程中，执行冻结事务ID操作的事务ID的域值。如果该表的pg_class.relfrozentxid小于这个值，VACUUM操作则不会针对该表进行冻结操作。如果有足够的CPU能力，并且工作负载产生的写操作多于读操作，那么将此值设置为0会尽可能快地冻结元组。

● vacuum_freeze_max_age:
指定事务ID域值，针对pg_class.relfrozentxid，触发激进的VACUUM操作。此选项与vacuum_freeze_table_size选项相同，但为autovacuum进程使用此配置选项。这个选项可以在表级别设置，并提供了将HOT表(频繁的写操作)与热表或冷表区别对待的灵活性。

下边是有关VACUUM和AUTOVACUUM相关参数值设置的例子：

ALTER SYSTEM SET vacuum_freeze_table_age=10000000;
ALTER SYSTEM SET vacuum_freeze_min_age=0;

-- table level:
ALTER TABLE orders SET (autovacuum_freeze_max_age=10000000);

监控事务ID

随着工作负载的变化，优化适合系统硬件资源的数据库选项是一个具有挑战性的持续过程。为了找到数据库配置的最优值，您需要不断地监视系统，并根据监视的结果调整选项。

事务ID空间由系统(集群或运行数据库服务器)中的所有数据库共享。您需要在保存回收事务id的任何数据库中找到适当的表。只有在确保数据库系统中没有表包含事务ID的占用后，事务ID空间才会被重用。

PostgreSQL为每个表维护一个冻结的事务ID。表中包含事务ID小于或等于冻结ID的所有行对系统中的所有进程可见或不可见。可见行是插入和提交的数据行，而不可见行是删除的死元组。此事务ID在成功的VACUUM操作结束时更新。冻结的事务ID以及当前事务ID信息决定了表的年龄。这些表级冻结的事务ID可以计算数据库或数据库实例级冻结的事务ID。这些值有助于确定可以在事务ID空间中向前移动的正确数据库和表。

事务id的回收取决于表的年龄。如果对进程选择的表有任何关注，请使用以下查询来查找可以移动事务ID值的表。

下面的查询报告数据库系统级事务ID空间的使用情况:

WITH max_age AS (
SELECT 2000000000 as max_old_txid
, setting AS autovacuum_freeze_max_age
FROM pg_catalog.pg_settings
WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS (
SELECT datname
, m.max_old_txid::int
, m.autovacuum_freeze_max_age::int
, age(d.datfrozenxid) AS oldest_current_txid
FROM pg_catalog.pg_database d
JOIN max_age m ON (true)
WHERE d.datallowconn )
SELECT max(oldest_current_txid) AS oldest_current_txid
, max(ROUND(100*(oldest_current_txid/max_old_txid::float))) AS
consumed_txid_pct
, max(ROUND(100*(oldest_current_txid/autovacuum_freeze_max_age::float)))
AS consumed_autovac_max_age
FROM per_database_stats;

例如，考虑在承载TPCC数据库的高内存实例上的consumed_autovac_max_age查询的输出:

oldest_current_txid | consumed_txid_pct | consumed_autovac_max_age
---------------------+-------------------+--------------------------
208329838 |                10 |                      104

这些结果是在使用1024个TPCC客户机运行基准测试30分钟后获得的，其中包含以下信息:

1. oldest_current_txid是所有数据库当中最老的txid
2. consumed_txid_pct 表示事务ID空间在20亿个事务ID空间里已使用的百分比。较大的值意味着数据库系统可能会遇到事务封装问题。我们建议将该值保持在85%以下，以保持数据库运行并避免数据库系统中断。
3. consumed_autovac_max_age 表示触发主动操作的已消耗事务ID空间的百分比。这个百分比是基于autovacuum_freeze_max_age，如果该值大于100，则会根据最老的冻结事务ID的年龄触发主动操作。

查找包含最旧的事务ID的目标数据库

如果前面的查询报告consumed_txid_pct太高，那么下一步是识别包含最老事务ID的数据库。下面的查询报告的信息与前面的查询类似，但在数据库级别将其分解:

SELECT datname, age(datfrozenxid) AS frozen_xid_age
, ROUND(100*(age(datfrozenxid)/2000000000.0::float))
consumed_txid_pct
, current_setting('autovacuum_freeze_max_age')::int
- age(datfrozenxid) AS remaining_aggressive_vacuum
FROM pg_database;

例如，考虑前面的数据库级查询在示例TPCC数据库上的输出:

datname    | frozen_xid_age | consumed_txid_pct | remaining_aggressive_vacuum
---------------+----------------+-------------------+-----------------------------
cloudsqladmin |			50200078 	| 3								| 149799922
template0     |			200078 		| 0								| 199799922
postgres      |			50202602 	| 3								| 149797398
template1     |			200078 		| 0								| 199799922
tpcc          |			209401713 | 9								|-9401713

这些结果包含以下信息:

● datname列： 包含数据库的名称。
● frozen_xid_age： 表示数据库级冻结事务ID的年龄。值越大(例如，大于autovacuum_freeze_max_age)表示
数据库越需要注意。
● consumed_txid_pct表示事务ID对数据库的最大事务ID限制(20亿个事务ID)。
● 表示可用的事务ID空间在达到激进模式之前-数据库与autovacuum_freeze_max_age值的接近程度。负值表示有一些
数据库中由于pg_class.relfrozentxid的年龄而触发激进模式的VACUUM操作的表。

在确定目标数据库之后，您将确定数据库中作为VACUUM操作候选的表。下面的查询报告了需要执行VACUUM操作的前50个表。为了维护数据库系统的健康，我们建议在不影响I/O、CPU或WAL的情况下对报告的表调度操作。您可以对一批表执行操作，而不是一次对所有表调度VACUUM操作。

查询并定位需要执行vacuum操作的表

下面的例子列出了需要执行VACUUM操作的前50个表:

SELECT c.oid::regclass
, age(c.relfrozenxid)
, pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC LIMIT 50;

下边是一个示例输出结果：

oid           |    age    | pg_size_pretty
-------------------------+-----------+----------------
history                 | 210000101 | 12 GB
stock                   | 210000101 | 37 GB
customer                | 210000101 | 20 GB
order_line              | 210000101 | 176 GB
pg_statistic            | 128239305 | 736 kB
district                | 123162457 | 51 MB
new_order               | 112077299 | 2938 MB
warehouse               | 109644862 | 20 MB
pg_type                 |  90215950 | 192 kB

即使在提供了更好的监视基础设施之后，当前的工作负载可能会阻塞VACUUM操作，从而使VACUUM操作无法回收空间或冻结事务id。VACUUM操作可以通过以下方式阻止:

●长时间运行的事务或快照。识别这些事务并终止它们以解除VACUUM操作的阻塞。
●放弃复制槽位。丢弃废弃的插槽。
●孤儿准备事务。回滚孤立的准备事务。
●hot_standby_feedback创建的备节点上长时间运行的快照或事务。识别副节点上长时间运行的会话或事务，并终止阻塞会话。

下面的查询获取每个VACUUM操作阻塞的最老值:

SELECT
(SELECT max(age(backend_xmin)) FROM pg_stat_activity) as
oldest_running_xact,
(SELECT max(age(transaction)) FROM pg_prepared_xacts) as
oldest_prepared_xact,
(SELECT max(age(xmin)) FROM pg_replication_slots)     as
oldest_replication_slot
(SELECT max(age(backend_xmin)) FROM pg_stat_replication) as
oldest_replica_xact
;

理想情况下，auto vacuum进程在解决阻塞操作后拾取并处理适当的表。但是，识别具有最旧事务ID的表或数据库并在表上运行适当的VACUUM命令可能很有用。

回收存储空间

尽管增加与表和数据库相关的空间不会导致中断，但它可能会影响查询的性能。索引的效率取决于数据的聚类程度。如果存在大量的死元组，那么指向死行的索引记录会消耗空间并影响索引扫描的成本。我们建议您阅读尽可能少的页面来处理查询。否则，查询响应时间会受到I/O延迟的影响，并且可能需要比预期更多的I/O。同时，拥有更多的数据页或数据块可能导致执行错误的查询计划，这可能无法满足响应时间需求。

如概述部分所述，死行是删除或更新行的结果。下面是一些基于表中行数和死元组数控制VACUUM操作的数据库选项:

autovacuum_vacuum_threshold:  该值表示在考虑对表进行VACUUM操作之前所需的死元组的最小数量。默认值是50个元组。如果任何表的死元组少于50个，则autovacuum进程不会对表执行操作。

autovacuum_vacuum_scale_factor： 该值表示符合VACUUM操作条件的表大小的百分比， 它加autovacuum_vacuum_threshold, 用以确定是否要执行vacuum操作。该选项的默认值是表大小的0.2或20%。

autovacuum进程使用前面两个选项来限定表是否可以进行VACUUM操作。autovacuum过程使用下面的公式来限定表是否可以进行VACUUM操作:

#of dead tuples in a table (pg_stat_all_tables.n_dead_tup) >
(autovacuum_vacuum_threshold + pg_class.reltuples * autovacuum_vacuum_scale_factor)

跟踪表中的操作

因为工作负载的更改是基于不断变化的需求，所以您需要不断改进配置选项。为了优化配置选项，您需要跟踪系统中产生的失效元组的数量。

下面的查询报告已连接数据库中每个表在逻辑上插入、更新和删除的行数。这些值不代表可回收空间，因为可能存在一些会话，其中删除或更新的行由于MVCC仍然可见。

查询每张表中逻辑插入、更新、删除的行数

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_all_tables
ORDER BY n_dead_tup DESC LIMIT 20;

样例输出：

relname                  | n_tup_ins | n_tup_upd | n_tup_del
-------------------------+-----------+-----------+-----------
order_line              | 556742761 | 403246159 |         0
new_order               |  44921508 |         0 |  40196757
stock                   |  51200000 | 402743889 |         0
customer                |  15360000 |  78188896 |         0
orders                  |  55678256 |  40325550 |         0
district                |      5120 |  80649354 |         0
warehouse               |       512 |  40329213 |         0
pg_statistic            |        92 |      4942 |         0
pg_toast_2619           |      3931 |         0 |      3856

查询死元组

要回收空间，需要找到失效元组的确切数量。下面是查找死元组的示例查询:

SELECT relname, n_live_tup, n_dead_tup, n_tup_hot_upd
FROM pg_stat_all_tables
ORDER BY n_dead_tup DESC LIMIT 20;

可能得到类似如下的输出：

relname                  | n_live_tup | n_dead_tup | n_tup_hot_upd
-------------------------+------------+------------+---------------
order_line              |  559111721 |   92094372 |     404036839
new_order               |    4725336 |    4200727 |             0
stock                   |   51199928 |    3903734 |     403591655
customer                |   15362085 |     568888 |      78353381
orders                  |   55928160 |     399769 |      40411453
district                |       5120 |       1633 |      80749601
warehouse               |        512 |        193 |      40341263
pg_statistic            |        514 |         99 |          4415
pg_toast_2619           |         88 |         59 |             0
pg_amproc               |        447 |          0 |             0

输出信息如下:
N_live_tup:活动元组的数量。
N_dead_tup:失效元组的数量。如果死元组多于活元组，则应该评估空间回收。
N_tup_hot_upd: HOT更新元组的个数。此值表示非索引键更新—此更新不修改索引列。元组被链接起来，旧的元组最终被VACUUM操作删除。

控制autovacuum的处理

VACUUM操作是一个维护操作，它不应该影响应用程序的运行时性能。该操作包括读取页面、验证页面是否包含需要回收或冻结的元组、处理符合条件的行以及将页面标记为脏的。该操作还需要写入与该操作相关的WAL记录。整个过程消耗系统资源(如CPU、内存和I/O)，并更改数据库系统缓冲区缓存的状态。如果在不知道当前工作负载的情况下触发VACUUM操作(自动或手动)，则会影响应用程序吞吐量。

您可以根据工作负载延迟手动VACUUM命令，但需要特别注意控制自动真空过程。节流是控制自动真空操作操作的功能。节流是一种基于成本的机制，考虑了以下配置选项:

vacuum_cost_page_hit:  	执行VACUUM操作时在PostgreSQL shared_buffers缓存中找到一个页面时的成本。
vacuum_cost_page_miss:	如果在共享缓冲区缓存中找不到页，或者需要从磁盘或文件系统缓冲区缓存中读取该页，则VACUUM操作的成本。
vacuum_cost_page_dirty:	如果修改一个干净的页面并删除页面上的死元组或冻结的事务id，则VACUUM操作的估计成本。
vacuum_cost_limit: 批量处理时VACUUM操作的成本。当操作达到设置的值时，VACUUM进程进入睡眠状态
vacuum_cost_delay: VACUUM进程在达到vacuum_cost_limit以后，执行休眠的时间。

以下查询显示了VACUUM配置选项的默认值:

SELECT name, setting
FROM pg_settings WHERE name LIKE 'vacuum_cost%';

name   | setting
------------------------+---------
vacuum_cost_delay      | 0
vacuum_cost_limit      | 200
vacuum_cost_page_dirty | 20
vacuum_cost_page_hit   | 1
vacuum_cost_page_miss  | 10
(5 rows)

VACUUM操作的默认开销限制是200，并且它永远不会休眠，因为vacuum_cost_delay的值是0)。

autovacuum进程可以有它自己的一些配置参数，如：autovacuum_vacuum_cost_limit（默认为20ms, 而vacuum_cost_limit使用的是-1).

autovacuum进程可以派生多个worker进程来处理不同的表。在这种情况下，autovacuum_vacuum_cost_limit就会分布到多个auto vacuum的worker进程中，每个worker进程会得到自己的cost limit值。

这些默认值不反映当前硬件资源的状态。例如，该操作使用默认的vacuum_cost_limit值每秒执行100次迭代，或者200次迭代意味着每秒20000次。这个迭代涵盖了:

20000 * 8kb块大小= 160mbps从缓冲缓存读取。如果shared buffer是GB, 那所有的有效页都会在缓存当中。(vacuum_cost_page_hit = 1)
从磁盘或文件系统中读，速度为16M/s (vacuum_cost_page_miss = 10)
8M/s的写速度，假定所有的页都是干净的，并且被VACUUM处理。 （vaccum_cost_page_dirty = 20)

大多数数据库系统使用千兆字节的shared_buffers和高级I/O设备。我们建议根据硬件和系统配置修改配置选项，以避免多个较小的VACUUM操作迭代成为一个大操作，从而减少WAL子系统的负担。

大多数当前数据库实例可以生成超过160 MB的脏页，并且VACUUM操作可能需要从磁盘读取这些页来处理它们。为了保持表数据始终干净，VACUUM操作需要跟上新数据生成的速度。

表一级的限制(throttle)

VACUUM操作不能区分热表、热表或冷表。热表是具有更多写操作的表，需要以不同于其他表的方式处理。例如，与其他表相比，每次迭代VACUUM操作可能需要更多的时间。以下auto vacuum配置选项可应用于系统级和表级:

● autovacuum_vacuum_cost_limit: 它与vacuum_cost_limit类似，只不过，它用于autovaccum, 默认值为-1， 表示采用的是vacuum_cost_limit
● autovacuum_vacuum_cost_delay: 它与vacuum_cost_delay类似，默认值为-1，表示采用的是vacuum_cost_delay值

要修改这些配置选项，可以使用ALTER TABLE命令。 下面的示例修改orders表的值:

ALTER TABLE orders SET (autovacuum_vacuum_cost_delay=10);
ALTER TABLE orders SET (autovacuum_vacuum_cost_limit=10000)；

5.相关锁机制

VACUUM操作根据您使用的选项或由配置选项决定，使用不同级别的锁定语义。主动的VACUUM操作将基于该值执行。下面列出了该操作使用的锁定行为:

1. VACUUM FULL 会使用AccessExclusive锁，为目标表创建一个全新的数据和索引层锁。当它在执行时，没有其他进程能够访问该表
2. 该操作的另一个变体是CLUSTER命令。就像VACUUM FULL操作，该选项还创建新的数据和索引页不同之处在于数据是根据给定的索引键排序的。
3. vacuum操作的其他变化获得锁, 大都使用SharedUpdateExclusive锁,这个锁限制DDL和表上的其他操作，但允许所有常规操作。为了对数据的一致副本进行操作，VACUUM操作为每个页面获取cleanup_lock。这个cleanup_lock是该页上的排他锁，因此在该页上完成操作之前，不允许对该页进行任何操作。
4. 如果VACUUM操作使用SharedUpdateExclusive锁清理表，则不允许对表进行DDL操作。这可能导致在存在待处理的DDL请求时阻塞数据操作语言(DML)操作。请求DDL锁的后端等待VACUUM操作，在此DDL请求之后的任何DML都必须在DDL锁请求之后等待。这个过程导致系统在一个正在被VACUUM操作积极处理的表上的总停顿.
5.VACUUM和autovacuum操作的锁语义是相同的，除了autovacuum进程通过取消阻塞用户操作的操作来解除表上任何挂起的操作。如果autovacuum过程正在执行激进性冻结，那么它将运行到最终完成。

6.VACUUM在系统表上的操作

在大多数情况下，对系统编目表的操作——向编目中插入新条目或删除条目——很少发生。频繁创建和删除临时表的应用程序可能会导致系统编目表膨胀。创建临时表会在目录表中产生新的元组，删除临时表将元组标记为已删除。如果目录过大，就会影响所有应用程序的性能，因为查找所需的目录数据会有延迟。如果VACUUM操作阻塞了目录页或表，则可能导致系统中断，因为没有目录数据，任何查询语句都无法完成)。这导致需要尽快清理目录。

一般来说，对表级配置选项进行了优化，以不同于其他表处理该表上的VACUUM操作。不幸的是，这对于系统编目来说是不可能的。系统编目上的VACUUM操作使用全局配置选项。如果全局选项没有优化，并且工作负载使用许多临时表，那么编目就会变得臃肿。由于目录表的大小比常规用户表小，因此我们建议您调度一个显式cron作业，该作业根据应用程序需求(可以每小时一次)对系统目录执行非阻塞VACUUM操作。

7.减少宕机时间

优化和监视数据库实例有助于减少或避免VACUUM操作相关的中断。如果没有根据监视数据或来自PostgreSQL消息采取任何操作，那么您可能会遇到停机。例如，所有写事务都被阻塞，直到事务ID空间被回收。以下是一些可以减少完成操作所需时间的建议:

●  增加系统内存以及autovacuum_maintenance_work_mem，在每次迭代中批处理更多的元组，并尽快完成工作。
●  VACUUM操作产生许多WAL记录。如果没有为此实例配置副本，则可以减少WAL记录，并尽可能快地完成操作。为了减少VACUUM操作产生的WAL，可以将full_page_writes设置为OFF。该选项只写入元组头信息和跳过写与数据相关的WAL记录。如果更改了与WAL相关的操作，作为预防措施，请在VACUUM操作前后运行CHECKPOINT命令。
●  如果目标表已经达到20亿个事务ID限制，因为没有任何元组被冻结，那么尝试减少在单用户模式下完成的工作量。一个可能的选项值是：vacuum_freeze_min_age=1,000,000,000. 这个新值将冻结的元组数量减少到2X。
●  PostgreSQL 12.0及以后的版本支持清理和VACUUM操作，而不需要清理索引项。这一点至关重要，因为清理索引需要完整的索引扫描，如果有多个索引，则总时间取决于索引大小，如下面的公式所示:
	全索引扫描的个数 = maintenance_work_mem定义的批次的个数*索引的个数
较大的索引会消耗索引扫描的大量时间，因此我们建议set INDEX_CLEANUP OFF来快速清理和冻结表数据。
●  12.0之前的PostgreSQL版本需要优化索引数。如果有的话然后，删除非关键索引可以帮助加快VACUUM操作。

8.减少副本上的宕机

如果存在具有副本的数据库实例，则副本的VACUUM操作将通过主实例生成的WAL记录进行。如果主实例优化了维护表和数据库健康的配置选项，则副本处于健康状态。副本配置了足够的资源，可以考虑VACUUM操作在主实例上生成的WAL记录。将这些记录应用于副本以删除死行或冻结元组。但是，如果主实例遇到事务ID封装问题，则副本不会获得与紧急情况相关的WAL记录。

在单用户模式下执行VACUUM操作，但必须在多用户模式下启动系统。根据单用户模式下生成的WAL的数量，副本的重放可能需要相当长的时间。为了减少赶上所需的时间，我们建议您重新创建一个新的副本，而不是等待当前副本赶上主实例上生成的WAL。如果VACUUM操作清理了一个关闭了full_page_writes配置选项的表，那么在应用与WAL记录相关的更改之前，副本需要从磁盘中获取预映像。这种配置成倍地增加了应答时间。

总结

这篇文章很长。最早来自Google的GCP工程师的相关文档。感觉蛮细致的，虽然有点啰嗦。还是有不少内容值得一读。

原文名： Optimizing, monitoring, and troubleshooting  VACUUM operations in PostgreSQL