このページでは、データベースで 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_name が pg_temp_ で始まる場合は、そのテーブルを破棄するしかありません。PostgreSQL では、別のセッションで作成された一時テーブルの VACUUM を行えないためです。そのセッションが有効で、アクセス可能であればテーブルをバキュームできますが、多くの場合はそうなりません。一時テーブルを破棄するには、次の SQL ステートメントを使用します。
SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
DROP TABLE pg_temp_<N>.<tablename>;
これが唯一の阻害要因であれば、約 1 分後に自動バキュームがこの変更を取得し、pg_database で datfrozenxid を次に進めます。これにより、ラップアラウンド保護の読み取り専用状態が解消されます。
通常のテーブル
通常の（一時的でない）テーブルの場合は、以降の手順を続けて、クリーンアップがブロックされないこと、VACUUM が高速に実行されること、そして、最も重要なテーブルがバキュームされることを確認します。
スタックしたトランザクション ID を確認する
システムでトランザクション ID が不足する理由の 1 つは、PostgreSQL が、最も古い実行中のトランザクションの開始後に作成されたトランザクション ID をフリーズできないことです（つまり、すべてのトランザクションが可視としてマークされています）。これは、マルチバージョン同時実行制御（MVCC）ルールによるものです。極端なケースでは、このようなトランザクションが非常に古いものになった場合、VACUUM がトランザクション ID のラップアラウンド上限の 20 億を超える古いトランザクションをクリーンアップできなくなり、システム全体で新しい DML を処理できなくなる可能性があります。通常、ログファイルに「WARNING: oldest
xmin is far in the past」という警告も出力されます。
スタックしたトランザクション ID を修正した場合に限り、最適化に進んでください。
ここでは、トランザクション ID がスタックする 4 つの理由と、その軽減策について説明します。
- 長時間実行トランザクション: トランザクションを特定し、バックエンドをキャンセルまたは終了してバキュームのブロックを解除します。
- 孤立した準備トランザクション: これらのトランザクションをロールバックします。
- 放棄されたレプリケーション スロット: 放棄されたスロットを削除します。
- レプリカ上の長時間実行トランザクション（
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 万に近い値または 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_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 に近いクエリをキャンセルします。
state が active の場合、クエリは、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 │ └─────────────┴───────────────────────────────┘
トランザクション ID として最後のクエリ（この場合は trx_id_pin）の gid を使用して、最も古い孤立した準備トランザクションをロールバックします。
ROLLBACK PREPARED trx_id_pin;
または、commit します。
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 に設定され、最も長い時間実行されているトランザクション確認し、それを無効にします。
レプリカで必要とされる最も古い TXID は pg_stat_replication ビューの backend_xmin 列にあります。
先へ進むには、レプリカの阻害しているクエリを停止します。阻害しているクエリを見つけるには、長時間実行トランザクションのクエリを使用しますが、今回はこのクエリをレプリカで実行します。
また、レプリカを再起動する方法もできます。
VACUUM を構成する
次の 2 つのフラグを設定します。
- autovacuum_vacuum_cost_delay = 0
- autovacuum_work_mem = 1048576
1 つ目のフラグは、PostgreSQL によるバキュームに対するディスクのスロットリングをすべて無効にし、VACUUM が最大速度で実行されるようになります（デフォルトでは、自動バキュームで抑制され、最も遅いサーバーですべてのディスク I/O が使い尽くされることがなくなります）。
2 つ目のフラグの autovacuum_work_mem は、インデックス クリーンアップの実行回数を減らします。可能であれば、テーブルで使用されなくなり、VACUUM でクリーンアップされる行のすべての ID が格納できるサイズにすることをおすすめします。この値を設定する場合は、各 VACUUM が実行できるローカルメモリの最大量を考慮してください。利用可能な大きさ以上にせず、一部は割り当てずに残しておくようにしてください。データベースを読み取り専用モードで実行したままにする場合は、読み取り専用クエリに使用されるローカルメモリも考慮する必要があります。
ほとんどのシステムでは、最大値（サンプルでは 1 GB または 1, 048, 576 KB）を使用します。この値は、最大で約 1 億 7,800 万のデッドタプルに相当します。それでもインデックス スキャンの実行が複数回発生します。
これらのフラグや他のフラグの詳細については、PostgreSQL の VACUUM オペレーションの最適化、モニタリング、トラブルシューティングをご覧ください。
これらのフラグを設定した後、自動バキュームが新しい値で開始されるようにデータベースを再起動します。
pg_stat_progress_vacuum ビューを使用すると、自動バキュームによって開始された VACUUM の進行状況をモニタリングできます。このビューには、すべてのデータベースで実行されている VACUUM が表示されます。また、ビュー列 relid でテーブル名を検索できない他のデータベースのテーブル（リレーション）も表示されます。
次にバキューム処理が必要なデータベースとテーブルを特定するには、PostgreSQL の VACUUM オペレーションの最適化、モニタリング、トラブルシューティングにあるクエリを使用します。サーバー VM が十分に機能していて、自動バキュームよりも多くの並列 VACUUM プロセスに対応する帯域幅がある場合は、手動バキュームを開始できます。
VACUUM 速度を確認する
このセクションでは、VACUUM の速度を確認する方法と、必要に応じて高速化する方法について説明します。
実行中の自動バキュームを確認する
VACUUM を実行しているバックエンドはすべて、システムビュー pg_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) を使用して 2 つのユーザー定義フラグ（save.ts と save.heap_blks_scanned）を pg_stat_progress_vacuum から現在の値に設定します。
次のクエリでは、この 2 つの値を比較の基準値として使用し、速度と完了時間を判断します。
注: WHERE datname = DB_NAME は、調査を一度に 1 つのデータベースに制限します。このデータベースで自動バキュームが 1 つしか実行されておらず、データベースごとに複数の行が存在する場合は、この値で十分です。要がある場合はこれで十分です単一の自動バキュームの行を示すには、WHERE に追加のフィルタ条件 ('AND relid= …'') を追加する必要があります。次のクエリについても同じことが言えます。
基準値を保存すると、次のクエリを実行できます。
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_second と remaining_time を計算します。これにより、VACUUM の実行速度が十分であるか、それとも高速化するかを判断できます。remaining_time 値は scanning heap フェーズ専用です。フェーズによっては、さらに時間がかかることもあります。バキュームの詳細をご覧ください。また、インターネット上にも、バキュームのさまざまな側面について解説しているブログ投稿がありますので、そちらもご覧ください。
VACUUM を高速化する
VACUUM スキャンを高速化する最も簡単で早い方法は、autovacuum_vacuum_cost_delay=0 を設定することです。これは Google Cloud Console で行えます。
残念ながら、すでに実行中の 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 ページ/秒未満から 6,500 ページ/秒に増加し、ヒープスキャン フェーズの推定残り時間は 9 時間から 23 分に短縮されました。
他のフェーズのスキャン速度は測定が容易ではありませんが、同様の高速化が期待できます。
また、インデックスを使用した複数のパスを回避するために、autovacuum_work_mem はできる限り大きくします。インデックスのパスは、デッドタプル ポインタがいっぱいになるたびに発生します。
データベースが使用されていない場合は、shared_buffers に必要な容量を確保した後、空きメモリが 80% 以下になるように autovacuum_work_mem を設定します。これは、自動バキュームによって開始された VACUUM プロセスごとの上限値です。読み取り専用ワークロードを引き続き実行する場合は、使用するメモリを減らしてください。
スピードを改善する他の方法
インデックスのバキュームを回避する
巨大なテーブルでは、VACUUM がインデックスのクリーンアップに多くの時間を費やします。
PostgreSQL 14 では、システムでラップアラウンドが発生する可能性が高い場合に、インデックスのクリーンアップを回避する特別な最適化が行われます。
PostgreSQL 12 と 13 では、次のステートメントを手動で実行できます。
VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;
バージョン 11 以前では、バキュームを実行する前にインデックスの DROP を実行します。インデックスは後で再作成できます。
autovacuum がすでに実行されているテーブルのインデックスを破棄するには、実行中のバキュームをキャンセルし、autovacuum がそのテーブルでバキュームを再び開始する前に、インデックスを破棄するコマンドを実行する必要があります。
まず、次のステートメントを実行して、終了する必要のある自動バキューム プロセスの PID を特定します。
SELECT pid, query
FROM pg_stat_activity
WHERE state != 'idle'
AND query ilike '%vacuum%';
次のステートメントを実行して、実行中のバキュームを終了し、1 つ以上のインデックスを破棄します。
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 コマンドは、ライブタプルを新しいファイルにコピーするため、新しいファイルとそのインデックスが収まる十分な容量が必要です。
次のステップ
- ラップアラウンドに対する VACUUM について学習する。
- 定型化されたバキュームについて学習する。
- 自動化されたバキュームについて学習する。
- PostgreSQL の VACUUM オペレーションの最適化、モニタリング、トラブルシューティングについて学習する。