突破事务 ID (TXID) 封装保护

本页面介绍了在数据库运行 PostgreSQL 中的事务 ID 环绕保护时可执行的操作。该保护措施显示为 ERROR 消息,如下所示:

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.

或者,系统可能会显示如下 WARNING 消息:

database dbname must be vacuumed within 10985967 transactions.

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

步骤概述

  • 找出哪些数据库和哪些表导致环绕。
  • 检查是否存在任何阻止 (AUTO) VACUUM 的内容(例如卡住的事务 ID)。
  • 测量 AUTOVACUUM 的速度。如果运行缓慢,则可以选择尝试加速。
  • 如果需要,手动运行更多 VACUUM 命令。
  • 调查其他可加快清空速度的方法。有时,最快的方法就是删除表或一些索引。

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

找到导致环绕的数据库和表

查找数据库

为了找出哪些数据库包含导致环绕的表,请运行以下查询:

SELECT datname, 
       age(datfrozenxid), 
       2^31-1000000-age(datfrozenxid) as remaining
  FROM pg_database
 ORDER BY 3

remaining 值接近 0 的数据库是导致问题的数据库。

查找表

连接到该数据库并运行以下查询:

SELECT c.relnamespace::regnamespace as schema_name,
       c.relname as table_name,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
       2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as remaining
  FROM pg_class c
  LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
 WHERE c.relkind IN ('r', 'm')
 ORDER BY 4;

此查询会返回导致问题的一个或多个表。

对于临时表

如果 schema_namepg_temp_ 开头,则解决此问题的唯一方法是删除表,因为 PostgreSQL 不允许您在其他会话中创建的 VACUUM 临时表。有时,如果该会话处于打开和可访问状态,您可以在该会话中清空该表,但通常情况并非如此。使用以下 SQL 语句来删除临时表:

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
DROP TABLE pg_temp_<N>.<tablename>;

如果这是唯一的阻止因素,则大约一分钟后,自动清空会选取此更改,并使得 datfrozenxidpg_database 中向前移动。这可以解决环绕保护只读状态。

普通表

对于普通(非临时)表,继续执行下面的后续步骤,查看是否有任何内容阻止了清理操作、VACUUM 运行速度是否足够快以及最重要的表是否被清空。

检查卡 ID 是否卡住

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

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

以下是可能导致事务 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(greatest(age(catalog_xmin),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 停止接受新写入命令时的环绕保护限制。在这种情况下,请参阅移除 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_leftoldest_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 返回为 NULLNULL,则表示事务尚未分配永久事务 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                       
└─────────────┴───────────────────────────────┘

使用最后一个查询(本例中为 trx_id_pin)中的 gid 作为事务 ID,回滚最早的孤立的准备事务:

ROLLBACK PREPARED trx_id_pin;

或者,请提交:

COMMIT PREPARED trx_id_pin;

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

已放弃的复制槽

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

首先,按照管理读取副本中的说明检查副本是否未停用。如果副本已停用,请再次启用。如果延迟仍然很高,则删除副本。

复制槽会显示在 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

如需将其向前移动,请停止将其保留在副本上的查询。 要了解哪个查询正在保留它,请使用长时间运行的事务中的查询,但这次在副本上运行该查询。

另一种方法是重启副本。

配置 VACUUM

设置以下两个标志:

  • autovacuum_vacuum_cost_delay = 0
  • autovacuum_work_mem = 1048576

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

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

在大多数系统上,请使用最大值(1 GB 或 1048576 kB,如示例中所示)。这个大小适合多达大约 1.78 亿个空闲元组。如果有更多元组,则会导致多次索引扫描。

在 PostgreSQL 中优化和监控 VACUUM 操作中详细介绍了这些标志和其他标志。

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

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

如要确定接下来需要清空的数据库和表,请使用 PostgreSQL 中的优化、监控和 VACUUM 操作问题排查查询。如果服务器虚拟机功能足够强大,并且具有比 autovacuum 启动的并行 VACUUM 进程更多的带宽,则可以启动一些手动清空。

检查 VACUUM 速度

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

检查运行清空

运行 pg_stat_progress_vacuumpg_stat_progress_vacuum 中。

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

估算 VACUUM 扫描速度

要估算扫描速度,您需要先存储基值,然后计算一段时间内的变化以估算完成时间。首先,您需要使用以下快照查询将 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 一次仅调查一个数据库。如果此数据库中仅运行一个自动清空,并且每个数据库具有多个行,则此数字就足够了。需要将额外的过滤条件 ('AND relid= …'') 添加到 WHERE 以指示单个自动清空行。对于下一个查询也是如此。

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

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 值仅适用于 scanning heap 阶段。其他阶段也需要时间,有时甚至更多。您可以详细了解清空并查看互联网上的博文,讨论一些清空的复杂方面。

加速清空

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

遗憾的是,已在运行的 VACUUM 不会获取此值,您可能需要重启数据库。

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

┌─[ 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% 的内存可用。这是每个自动清空启动的 VACUUM 进程的上限。如果您想继续运行只读工作负载,请减少内存。

提高速度的其他方法

避免清空索引

对于大型表,VACUUM 会花费大量时间来清理索引。

PostgreSQL 14 具有特殊优化功能,可在系统出现环绕风险时避免清理索引。

在 PostgreSQL 12 和 13 中,您可以手动运行以下语句:

VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;

在版本 11 及更早版本中,您可以在运行清空之前 DROP 索引,以后重新创建它。

当已经在针对表运行自动清空时,如果删除索引,则需要取消正在运行的清空操作,然后立即执行删除索引命令,之后自动清空才会再次对该表执行清空操作。

首先,运行以下语句以找出需要终止的 autovacuum 进程的 PID:

SELECT pid, query 
  FROM pg_stat_activity
 WHERE state != 'idle'
   AND query ilike '%vacuum%';

然后,运行以下语句以终止正在运行的 vacuum,并删除一个或多个索引:

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
SELECT pg_terminate_backend(<pid>);DROP INDEX <index1>;DROP INDEX <index2>; ...

丢弃引起冲突的表

在极少数情况下,您可以丢弃表。例如,如果表可以轻松从备份或其他数据库等其他来源恢复。

您仍需使用 cloudsql.enable_maintenance_mode = 'on',并且还可能终止针对该表的 VACUUM,如上一部分所示。

VACUUM FULL

在极少数情况下,当表只有一小部分实时元组时,运行 VACUUM FULL FREEZE 通常速度较快。这可以从 pg_stat_user_tables 视图进行检查(除非出现了崩溃,导致擦除了统计信息)。

VACUUM FULL 命令将实时元组复制到新文件,因此必须有足够的空间可供新空间及其索引使用。

后续步骤