获取事务 ID (TXID) 封装

本页面介绍当数据库遭遇或即将遭遇 PostgreSQL 中的事务 ID 封装保护时,您可以执行的操作。该保护措施显示为错误消息,如下所示:

database is not accepting commands to avoid wraparound data loss in database
dbname.

Stop the postmaster and vacuum that database in single-user mode.

You might also need to commit or roll back old prepared transactions, or drop
stale replication slots.

或警告消息,如下所示:

database dbname must be vacuumed within 10985967 transactions.

To avoid a database shutdown, execute a database-wide VACUUM in that database.

步骤概述

  • 检查是否存在任何使 AUTOVACUUM(例如卡住 TransactionID)中断的情况
  • 测量 AUTOVACUUM 的速度,然后选择性地加快速度
  • 如果需要,手动运行更多 VACUUM 命令

许多关于标志值的建议是故意不精确的,因为它们取决于许多数据库参数。请阅读本页末尾链接的文档,以更深入地了解此主题。

检查是否存在卡住事务 ID

系统可能会用完事务 ID 的一个可能原因是,由于多版本并发控制 (MVCC) 规则,PostgreSQL 无法冻结(即,标记为对所有事务可见)在当前最早运行的事务启动后创建的任何事务 ID。在极端情况下,此类事务可能会变得非常旧,以致于 VACUUM 在总体 20 亿个事务 ID 封装的限制下无法清理这些旧事务,并且会导致整个系统停止接受新的 DML。通常,您会在日志文件中看到警告,显示 WARNING: oldest xmin is far in the past

只有在补救事务 ID 可以补救后,才应移至优化阶段。

以下是可能出现事务 ID 卡住问题的原因以及其中的每一项原因:

  • 长时间运行的事务:识别这些事务,然后取消或终止后端,以取消清空。
  • 孤立事务准备:回滚孤立的事务准备。
  • 已放弃的复制槽:舍弃放弃的槽。
  • 对副本执行长时间运行的事务(使用 hot_standby_feedback = on:识别这些事务,然后取消或终止后端,以解除对清空的封锁。

以下查询会返回上述每种情况中最早的事务的存在时间,以及在封装前剩余事务的数量:

WITH q AS (
SELECT
  (SELECT max(age(backend_xmin))
      FROM pg_stat_activity  WHERE state != 'idle' )       AS oldest_running_xact_age,
  (SELECT max(age(transaction)) FROM pg_prepared_xacts)    AS oldest_prepared_xact_age,
  (SELECT max(age(xmin)) FROM pg_replication_slots)        AS oldest_replication_slot_age,
  (SELECT max(age(backend_xmin)) FROM pg_stat_replication) AS oldest_replica_xact_age
)
SELECT *,
       2^31 - oldest_running_xact_age AS oldest_running_xact_left,
       2^31 - oldest_prepared_xact_age AS oldest_prepared_xact_left,
       2^31 - oldest_replication_slot_age AS oldest_replication_slot_left,
       2^31 - oldest_replica_xact_age AS oldest_replica_xact_left
FROM q;

如果此方法返回报告的接近或小于 100 万个维度的 *_left 值,则当 PostgreSQL 停止接受新的写入命令时,100 万条是封装保护限制 - 您首先应阅读如何移除 VACUUM 拦截器部分,否则请转到微调 VACUUM 部分。

例如,如果上述查询返回:

┌─[ RECORD 1 ]─────────────────┬────────────┐
│ oldest_running_xact_age      │ 2146483655 │
│ oldest_prepared_xact_age     │ 2146483655 │
│ oldest_replication_slot_age  │ ¤          │
│ oldest_replica_xact_age      │ ¤          │
│ oldest_running_xact_left     │ 999993     │
│ oldest_prepared_xact_left    │ 999993     │
│ oldest_replication_slot_left │ ¤          │
│ oldest_replica_xact_left     │ ¤          │
└──────────────────────────────┴────────────┘

由于其中的 oldest_running_xact_left 和 oldest_prepared_xact_left 低于 100 万的封装保护限制,因此您必须先移除阻止 VACUUM 的拦截器,然后才能进行进一步的操作。

如何移除 VACUUM 拦截器

长时间运行的事务

在上面的查询中,如果 oldest_running_xact 等于 oldest_prepared_xact,然后转到孤立的准备事务部分,因为最近运行值还包括准备的事务。

您可能需要以 postgres 用户身份运行以下命令,上述命令才会变得可用:

GRANT pg_signal_backend TO postgres;

如果违规事务属于任何系统用户(以 cloudsql... 开头的用户),您将无法直接取消该事务。您必须重启数据库才能将其取消。

如需识别长时间运行的查询,并取消或终止其以终止清空,请先选择一些最早的查询(LIMIT 10 很便利,这样结果就适合屏幕大小,您可能需要在解析最早的正在运行的查询后重复此步骤):

SELECT pid,
       age(backend_xid) AS age_in_xids,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       state,
       query
 FROM pg_stat_activity
 WHERE state != 'idle'
 ORDER BY 2 DESC
 LIMIT 10;

如果 age_in_xids 返回为 NULL,则表示事务尚未分配永久事务 ID,您可放心地忽略该事务。

取消 xids_left_to_wraparound 接近 1M 的查询。

如果 stateactive,则可以使用 SELECT pg_cancel_backend(pid); 取消查询。否则,您需要使用 SELECT pg_terminate_backend(pid); 终止整个连接,其中 pid 是上一个查询的 pid

孤立事务准备

列出所有准备好的事务:

DB_NAME=> SELECT age(transaction),* FROM pg_prepared_xacts ;
┌─[ RECORD 1 ]┬───────────────────────────────┐
│ age         │ 2146483656                    │
│ transaction │ 2455493932                    │
│ gid         │ trx_id_pin                    │
│ prepared    │ 2021-03-03 16:54:07.923158+00 │
│ owner       │ postgres                      │
│ database    │ DB_NAME                       │
└─────────────┴───────────────────────────────┘

回滚或在确信的情况下使用上个查询中的 gid(在这里是 trx_id_pin)作为事务 ID 来提交最旧的孤立准备事务:

ROLLBACK PREPARED 'trx_id_pin';

COMMIT PREPARED 'trx_id_pin';

如需了解完整说明,请参阅 SQL ROLLBACK PREPARED 文档。

已放弃的复制槽

如果复制槽是因为现有副本已停止、暂停或今后导致某些问题而放弃,您可以从 gcloud 或 Google Cloud Console 删除副本。

首先检查副本是否未停用(如管理读取副本页面中所述)。如果副本已停用,请先将其再次启用。如果这解决了延迟问题,则无需采取任何其他措施。如果延迟仍然很高,则删除副本。

复制槽会显示在 pg_replication_slots 系统视图中。

以下查询会获取相关信息:

SELECT *, age(xmin) AS age FROM pg_replication_slots;
┌─[ RECORD 1 ]────────┬─────────────────────────────────────────────────┐
│ slot_name           │ cloudsql_1_355b114b_9ff4_4bc3_ac88_6a80199bd738 │
│ plugin              │ ¤                                               │
│ slot_type           │ physical                                        │
│ datoid              │ ¤                                               │
│ database            │ ¤                                               │
│ active              │ t                                               │
│ active_pid          │ 1126                                            │
│ xmin                │ 2453745071                                      │
│ catalog_xmin        │ ¤                                               │
│ restart_lsn         │ C0/BEF7C2D0                                     │
│ confirmed_flush_lsn │ ¤                                               │
│ age                 │ 59                                              │
└─────────────────────┴─────────────────────────────────────────────────┘

在本示例中,pg_replication_slots 运行状况良好(存在时间为 59),因此无需执行任何操作。如果存在时间接近 20 亿,则应删除槽。在查询返回多条记录的情况下,无法轻松确定每个确切的副本,因此请检查所有这些副本中是否有任何副本存在任何长时间运行的事务。

在副本上长时间运行事务,其中 hot_standby_feedback = on

检查副本中运行时间最长的事务,然后在副本上将其取消。

pg_stat_replication 视图中的 backend_xmin 列显示了副本所需的最早 TXID

如需将其向前移动,请停止将其保留在副本上的查询。

如需查找哪个查询再次存在,请使用长时间运行的事务部分中的查询,但这次在副本上。

另一种方法是重启副本

调节清空(简要版本)

设置以下两个标志:

  • autovacuum_vacuum_cost_delay = 0
  • autovacuum_work_mem = 1048576

第一个请求会停用 PostgreSQL 对清空运行的任何磁盘限制,以便 VACUUM 全速运行(默认情况下 autovacuum 受到限制,因此它不会在最缓慢的服务器上耗尽所有磁盘 IO)。

第二个标志 autovacuum_work_mem 会减少索引清理的次数。如果可能的话,其容量应足够大,以便能够存储 vacuum 将要清理的表中的空闲行的所有 ID。设置此值时,请考虑这是每个正在运行的 vacuum 命令可以分配的本地内存量上限,因此请确保您允许的内存量不能超过可用容量,请留出一些容量以备不时之需。如果您将数据库保持在只读模式,则还应考虑用于只读查询的本地内存。

也就是说,在大多数系统上,只需要使用最大值(1GB 或 1048576 kB - 如以下示例中所示)即可。这个大小适合多达大约 1.78 亿个空闲元组。如果有更多元组,则会导致多次索引扫描。

如需详细了解这些标志及其他标志,请参阅清空白皮书

设置这些标志后,重启数据库,使自动清空以新值开头。

您可以使用 pg_stat_progress_vacuum 视图来监控自动启动的清空的进度。请注意,此视图显示了在所有数据库中运行的清空,对于其他数据库中的表(关系),您无法使用视图列 relid 查找表名。

使用清空白皮书中的查询来确定下一步需要进行清空的数据库和表,如果服务器虚拟机足够强大并且具有比自动清空启动的并行清空处理更多的带宽,甚至可能需要进行一些手动清空。

调节清空(详细版本)

本部分介绍了如何检查清空速度,以及如何根据需要加快速度。

检查运行清空

运行 VACUUM 的所有后端都显示在系统视图 pg_stat_progress_vacuum 中。

如果当前阶段为 scanning heap,则可以通过观察 heap_blks_scanned 列中的变化来监控进度。遗憾的是,很难确定其他阶段的扫描速度。

估算清空扫描速度

如需估算扫描速度,您需要运行多个查询来获取一段时间内的变化:首先存储基值,然后计算一段时间内的变化并估算完成时间。首先,您需要使用以下快照查询将 heap_blks_scanned 的快照与时间戳一并保存:

SELECT set_config('save.ts', clock_timestamp()::text, false),
       set_config('save.heap_blks_scanned', heap_blks_scanned::text, false)
FROM pg_stat_progress_vacuum
WHERE datname = 'DB_NAME';

由于我们无法在表中保存任何内容,因此已进行封装,我们使用 set_config(flag, value) 将两个用户定义的标志 save.tssave.heap_blks_scanned 设置为 pg_stat_progress_vacuum 的当前值。

在下一个查询中,我们将使用这两个标志作为比较基准来确定速度并估算完成时间。

注意:如果 WHERE datname = 'DB_NAME',则系统会限制一次只能调查一个数据库;但是如果此数据库中只运行了一个 autovacuum,则此限制没有影响。对于每个数据库占多行的情况,则需要将额外的过滤条件 ('AND relid= …'') 添加到 WHERE,以定位单个 autovacuum 行,对于下一个查询也是如此。

保存基值后,可以运行以下查询:

with q as (
    SELECT datname,
           phase,
           heap_blks_total,
           heap_blks_scanned,
           clock_timestamp() - current_setting('save.ts')::timestamp AS ts_delta,
           heap_blks_scanned - current_setting('save.heap_blks_scanned')::bigint AS scanned_delta
     FROM pg_stat_progress_vacuum
     WHERE datname = 'DB_NAME'
), q2 AS (
SELECT *,
       scanned_delta / extract('epoch' FROM ts_delta) AS pages_per_second
  FROM q
)
SELECT *,
       (heap_blks_total - heap_blks_scanned) / pages_per_second AS remaining_time
  FROM q2
;
┌─[ RECORD 1 ]──────┬──────────────────┐
│ datname           │ DB_NAME          │
│ phase             │ scanning heap    │
│ heap_blks_total   │ 9497174          │
│ heap_blks_scanned │ 18016            │
│ ts_delta          │ 00:00:40.30126   │
│ as_scanned_delta  │ 11642            │
│ pages_per_second  │ 288.87434288655  │
│ remaining_time    │ 32814.1222418038 │
└───────────────────┴──────────────────┘

此查询会将当前值与保存的基值进行比较,并计算 pages_per_secondremaining_time,这让我们可以确定 vacuum 的运行速度是否足够快,亦或是否需要加快速度。请注意,remaining_time 仅适用于扫描堆阶段,但其他阶段也需要花费时间,有时需要花费的时间甚至更多。

您可以了解有关清空的详情,并且互联网上还有很多有关这篇博文的深度讨论话题。《清空》中的许多内容都非常复杂,因此估计执行和执行任何压缩都会导致本文档多次完成。remaining_time 仍然提供对清空数量的限制的实用估计,并提供了一种方法,用于检查是否改善了这种情况。

加速清空

加快 VACUUM 扫描速度的最快捷方法是设置 autovacuum_vacuum_cost_delay=0。这可以通过 Google Cloud Console 完成。

遗憾的是,这个值不是由已被运行的清空获取的,因此您可能需要重新启动数据库。

重启后,您可能会看到类似如下所示的结果:

┌─[ RECORD 1 ]──────┬──────────────────┐
│ datname           │ DB_NAME          │
│ phase             │ scanning heap    │
│ heap_blks_total   │ 9497174          │
│ heap_blks_scanned │ 222382           │
│ ts_delta          │ 00:00:21.422615  │
│ as_scanned_delta  │ 138235           │
│ pages_per_second  │ 6452.76031894332 │
│ remaining_time    │ 1437.33713040171 │
└───────────────────┴──────────────────┘

在此示例中,速度从每秒小于 300 页增加到每秒约 6500 页,堆扫描阶段的预计剩余时间从 9 小时减少到 23 分钟。

其他阶段的扫描速度不容易测量,但它们应该具有类似的加速。

在这种情况下,您需要执行的另一项操作是使 autovacuum_work_mem 尽可能大,以避免多次传递索引(每次内存中填充了空闲元组指针时都会进行一次索引传递)。

如果没有其他对象正在使用数据库,则可将 autovacuum_work_mem 设置为 shared_buffers 之后剩余空闲内存的大约 80%;但请记住,这是每个通过 autovacuum 启动的 vacuum 进程的内存量上限,如果您想继续运行只读工作负载,最好减少内存使用量。

后续步骤