如何在 Aurora PostgreSQL 相容版中疑難排解交易 ID 迴繞問題？

解決方法

監控指標和 CloudWatch 警示以防發生問題

若要監控最舊未執行 vacuum 的交易存續時間，請檢查 Amazon CloudWatch 中的 MaximumUsedTransactionIDs 指標。若要防止交易 ID 迴繞問題，請建立 CloudWatch 警示。如需更多資訊，請參閱在 Amazon Relational Database Service (Amazon RDS) for PostgreSQL 中實作交易 ID 迴繞的早期預警系統。

執行診斷查詢

使用 psql 或 pgAdmin 連線到 PostgreSQL 資料庫執行個體。

若要檢查資料庫存續時間，請執行以下命令：

SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY 2 DESC
LIMIT 20;

**注意：**如果輸出顯示 rdsadmin 或 template0 資料庫具有最舊的交易 ID 存續時間，請聯絡 AWS Support。

若要連線到存續時間最高的資料庫以識別資料表的潛在問題，請執行以下命令：

SELECT
c.relnamespace::regnamespace as schema_name,
c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
t.relkind, t.relpersistence
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 3 DESC
LIMIT 20;

若要檢視存續時間值較高資料表的 autovacuum 歷史記錄，請執行以下命令：

SELECT
  relname,
  n_live_tup, n_tup_upd, n_tup_del, n_dead_tup,
  last_vacuum, last_autovacuum,
  last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname='table_name';

**注意：**將 table_name 替換為您的資料表名稱。

若要監控正在進行的 vacuum 作業，請執行以下命令：

SELECT datname, usename, pid, state, wait_event,
current_timestamp - xact_start AS xact_runtime, query
FROM pg_stat_activity
WHERE upper(query) LIKE '%VACUUM%'
AND pid <> pg_backend_pid()
ORDER BY xact_start;

解決 autovacuum 程序阻礙問題

如果您使用以下其中一個 Aurora PostgreSQL 相容版本，請參閱識別並解決在 Aurora PostgreSQL 中的積極 vacuum 阻礙因素：

• 13.19 或更新版本
• 14.16 或更新版本
• 15.11 或更新版本
• 16.7 或更新版本
• 17.2 或更新版本

解決長時間執行的交易

長時間執行的交易或處於閒置狀態的轉換工作階段可能會阻礙 autovacuum 的運作。

您會在 PostgreSQL 錯誤日誌中看到以下警告訊息：

「WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems.」

若要識別阻礙 autovacuum 的交易，請執行以下命令：

SELECT pid,
    age(backend_xid),
    substr(query,1,20),
    extract(epoch from now()) - extract(epoch from xact_start) as duration,
    state
FROM pg_stat_activity
WHERE xact_start is not null
AND age(backend_xid) is not null
ORDER BY 2 DESC
LIMIT 10;

若要解決此問題，您可以取消作用中的查詢或終止連線。

若要取消作用中的查詢，請執行以下命令：

SELECT pg_cancel_backend(pid);

**注意：**將 pid 替換為查詢結果中的程序 ID。

若要終止連線，請執行以下命令：

SELECT pg_terminate_backend(pid);

**注意：**將 pid 替換為查詢結果中的程序 ID。

若要確保長時間執行的交易不會再次阻礙 autovacuum，請設定 statement_timeout、idle_in_transaction_session_timeout 和 log_min_duration_statement 參數。

解決未作用的邏輯複寫插槽

若要識別未作用的插槽，請執行以下命令：

SELECT slot_name, slot_type, database, xmin, catalog_xmin,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag,
    active
FROM pg_replication_slots
ORDER BY age(xmin), age(catalog_xmin) DESC;

**警告：**在您刪除複寫插槽之前，請確認該插槽沒有正在進行的複寫、處於未作用狀態，且已處於不可復原狀態。如果您刪除仍在複寫、作用中且可復原的插槽，可能會中斷複寫或造成資料遺失。

若要刪除未使用的插槽，請執行以下命令：

SELECT pg_drop_replication_slot('slot_name');

**注意：**將 slot_name 替換為您的複寫插槽名稱。

解決讀取器執行個體問題

若要識別 Aurora 叢集中可能阻礙熱待命回饋並阻礙 autovacuum 的讀取器，請執行以下查詢：

select server_id, feedback_epoch, feedback_xmin
from aurora_replica_status()
WHERE (feedback_xmin IS NOT NULL AND feedback_xmin > 0)

對於全域資料庫叢集中阻礙熱待命回饋的讀取器，請執行以下查詢：

select server_id,aws_region,feedback_epoch,feedback_xmin
from aurora_global_db_instance_status ()
WHERE (feedback_xmin IS NOT NULL AND feedback_xmin > 0);

在 feedback_epoch 和 feedback_xmin 合併值最低的讀取器執行個體中執行以下查詢：

SELECT pid,
    age(backend_xid),
    substr(query,1,20),
    extract(epoch from now()) - extract(epoch from xact_start) as duration,
    state
FROM pg_stat_activity WHERE backend_xmin::text::bigint =feedback_xmin

**注意：**將 feedback_xmin 替換為上一個查詢結果中的 feedback_xmin 值。

如果可以安全執行，請使用 pg_terminate_backend(pid) 終止長時間執行的交易。

解決未提交的預備交易

若要識別預備交易，請執行以下命令：

SELECT database, gid, prepared, owner, database, transaction::text::bigint as xid,
now() - prepared AS time_since_prepared
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

若要解決預備交易，您可以提交該預備交易或回復該交易。

若要提交預備交易，請執行以下命令：

COMMIT PREPARED 'gid'

**注意：**將 gid 替換為查詢結果中的全域交易 ID。

**警告：**在回復預備交易之前，請確認該交易不是分散式交易序列的一部分。

若要回復預備交易，請執行以下命令：

ROLLBACK PREPARED 'gid'

**注意：**將 gid 替換為查詢結果中的全域交易 ID。

解決孤立的暫存資料表

當 PostgreSQL autovacuum 程序遇到孤立的暫存資料表時，會記錄以下事件：

LOG: autovacuum: found orphan temp table "%s"."%s" in database "%s"

若要識別孤立的暫存資料表，請執行以下命令：

SELECT
  oid,
  relname,
  relnamespace::regnamespace,
  age(relfrozenxid)
FROM pg_class
WHERE relpersistence = 't'
ORDER BY age(relfrozenxid) DESC;

若要刪除暫存資料表，請執行以下命令：

DROP TABLE temporary_table

**注意：**將 temporary_table 替換為您的暫存資料表名稱。

解決 vacuum 效能問題

當 autovacuum 執行速度緩慢時，freeze 作業可能會延遲。

若要解決此問題，請先增加 autovacuum_max_workers 參數，以允許更多並行 vacuum 作業。接著使用以下公式等比例增加 autovacuum_vacuum_cost_limit 參數：

個別工作程序的 cost_limit = autovacuum_vacuum_cost_limit / autovacuum_max_workers

如果增加參數後 vacuum 效能仍然不足，請將您的 Amazon RDS 執行個體升級至具備更多 vCPU 的類型。您可以在資料表層級修改 autovacuum_vacuum_cost_delay 參數，以加速 autovacuum 作業。

若要在資料表層級修改 autovacuum_vacuum_cost_delay 參數，請執行以下命令：

ALTER TABLE mytable SET ( autovacuum_vacuum_cost_delay = value, autovacuum_vacuum_cost_limit = value );

**注意：**將 mytable 替換為您的資料表名稱，並將 value 替換為您所需的參數值。

大型索引 vacuum 效能

當您對大型索引執行 vacuum 時，記憶體限制可能會迫使系統對同一索引進行多次掃描，並顯著增加整體 VACUUM 持續時間。

如果您在 PostgreSQL 16 或更早版本中，對大型索引執行 VACUUM 時遇到速度緩慢的問題，請檢查以下可能原因：

• 索引大小超過 1 GB。
• 發生多次 VACUUM 掃描。若要檢視掃描次數，請查詢 pg_stat_progress_vacuum 檢視中的 index_vacuum_count。
• 您的 maintenance_work_mem 或 autovacuum_work_mem 設定錯誤。

若要解決此問題，請執行以下動作：

• 刪除未使用或重複的索引。
• 增加 maintenance_work_mem 或 autovacuum_work_mem。若要讓新的記憶體設定生效，請終止目前正在執行 autovacuum 的程序
• 手動執行 VACUUM FREEZE 作業，並在您的工作階段中增加 maintenance_work_mem。

如果您使用 PostgreSQL 12 或更新版本，請執行以下命令以檢視可能排除索引的手動 vacuum：

VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;

**注意：**將 table_name 替換為您的資料表名稱。

邏輯不一致

如果邏輯不一致的索引阻礙 autovacuum 程序，您可能會收到以下其中一個錯誤訊息：

• ERROR: right sibling's left-link doesn't match:block 5 links to 10 instead of expected 2 in index ix_name
• ERROR: failed to re-find parent key in index "XXXXXXXXXX" for deletion target page XXX CONTEXT: while vacuuming index index_name of relation schema.table

若要解決此問題，請重建索引。或者在 PostgreSQL 12 及更新版本中，您可以略過索引清理。

若要重建索引，請執行以下命令：

REINDEX INDEX ix_name CONCURRENTLY;

**注意：**將 ix_name 替換為您的索引名稱。

-或-

若要在 PostgreSQL 12 及更新版本中略過索引清理，請執行以下命令：

VACUUM FREEZE INDEX_CLEANUP FALSE table_name;

**注意：**將 table_name 替換為您的資料表名稱。

相關資訊

PostgreSQL 網站上的防止交易 ID 迴繞失敗

在 Amazon Aurora PostgreSQL 中使用 PostgreSQL autovacuum

了解 Amazon RDS PostgreSQL 環境中的 autovacuum

如何結束 Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 相容版資料庫執行個體上，執行時間過長的查詢？