PostgreSQLの自動バキュームをチューニングする

このページでは、PostgreSQLの自動バキュームをチューニングする方法、および一般的な自動バキュームの問題をトラブルシューティングする方法について説明します。

自動バキュームの仕組み

PostgreSQLはマルチバージョン同時実行制御（MVCC）を使用します。行を更新または削除しても、PostgreSQLはすぐに古い行バージョンを削除しません。代わりに、PostgreSQLは古いバージョンをデッドとしてマークし、実行中のトランザクションがそれを参照できなくなるまで保持します。自動バキュームとは、これらのデッド行バージョン（デッドタプルとも呼ばれる）を削除するバックグラウンドプロセスです。これらが占有する領域を再利用し、プランナーの統計情報を更新し、可視性マップを維持します。

自動バキュームは、データベースの健全性にとって重要です。自動バキュームがないと、デッド行がテーブルおよびインデックスの肥大化として蓄積されます。肥大化はクエリを遅くし、ディスク領域を無駄にし、バックアップとメンテナンスの時間を増加させます。自動バキュームは、データベースへの書き込みを停止させるトランザクションIDのラップアラウンドも防止します。

自動バキュームが実行されるタイミング

自動バキュームは定期的に各テーブルをチェックし、デッドタプルの数が次のしきい値を超えると、バキュームを実行します:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × number_of_live_tuples

デフォルトでは、PostgreSQLはautovacuum_vacuum_scale_factorを0.2に設定します。その後、PostgreSQLは行の20％がデッドになった場合にのみテーブルをバキュームします。大規模なテーブルでは、20％は多数のデッドタプルになります。そのため、自動バキュームが処理を行う前に、テーブルが長時間肥大化します。この遅延を軽減するため、GitLab Linuxパッケージインストールでは、デフォルトでautovacuum_vacuum_scale_factorを0.02（2％）に引き下げています。最大のテーブルや頻繁に更新されるテーブルでは、それでもテーブルごとに低い値が必要になる場合があります。大規模なテーブルを個別にチューニングするを参照してください。

推奨される自動バキューム設定

GitLab Linuxパッケージインストールには、ほとんどのワークロードに適した自動バキュームのデフォルト設定が用意されています。モニタリングにより、自動バキュームがデッドタプルに追いつけないことが判明した場合は、これらの設定を変更してください。目標は、データベースのI/Oを枯渇させることなく、ワークロードが作成するのと同じ速さで自動バキュームにデッドタプルを再利用させることです。

最も影響の大きい変更は、通常、autovacuum_vacuum_cost_limitの引き上げ、autovacuum_max_workersの引き上げ、および最大のテーブルのスケールファクターの引き下げです。このセクションの値は出発点として扱い、自身のワークロードに対して検証してください。

クラスター全体の設定

GitLab Linuxパッケージインストールの場合は、/etc/gitlab/gitlab.rbでクラスター全体の値を設定してから、sudo gitlab-ctl reconfigureを実行します。

postgresql['autovacuum_max_workers'] = "<value>"
postgresql['autovacuum_vacuum_cost_limit'] = "<value>"
postgresql['autovacuum_vacuum_cost_delay'] = "<value>"
postgresql['maintenance_work_mem'] = "<value>"

外部PostgreSQLインスタンスの場合は、代わりにpostgresql.confで同じパラメータを設定してから、PostgreSQLをリロードします:

autovacuum_max_workers = <value>
autovacuum_vacuum_cost_limit = <value>
autovacuum_vacuum_cost_delay = <value>
maintenance_work_mem = <value>
autovacuum_work_mem = <value>

SELECT pg_reload_conf();を使用するか、サービスをリロードすることで、PostgreSQLをリロードします。両方のインストールタイプにおいて、autovacuum_max_workersなどの設定にはPostgreSQLの完全な再起動が必要です。リロードや再設定だけでは不十分です。

次の設定は、自動バキュームが処理に追いつけるかどうかに最も大きな影響を与えます:

大規模なテーブルを個別にチューニングする

クラスター全体のスケールファクターが低くても、最大のテーブルは肥大化する可能性があります。これらのテーブルでは、PostgreSQLで直接、テーブルごとに低いスケールファクターまたはしきい値を設定してください。非常に大規模で頻繁に更新されるテーブルでは、0.01（1％）前後のスケールファクターが一般的です:

ALTER TABLE <table_name> SET (autovacuum_vacuum_scale_factor = 0.01);

テーブルごとの設定は、そのテーブルについてのみクラスター全体の値を上書きします。モニタリングを使用して遅れているテーブルを見つけ、まず本番環境以外で変更をテストしてください。

避けるべき設定

次の設定は避けてください:

• autovacuum_vacuum_cost_delay = 0は、I/Oスロットリングを削除します。その結果、自動バキュームは一時停止せずに実行され、ディスクI/Oを飽和させる可能性があります。クエリが遅くなります。
• 最大のテーブルにデフォルトのスケールファクターを使用すること。その結果、これらのテーブルのバキュームがあまりにもまれにしか行われなくなります。代わりにテーブルごとに低い値を設定してください。
• autovacuum_vacuum_cost_limitについて-1の継承に依存すること。その結果、有効な値が別の設定内にあり、見落とす可能性があります。明示的に設定してください。

自動バキュームをモニタリングする

データベース診断ページ

データベース診断ページには、各データベースのVacuumのアクティビティと自動バキュームの設定が表示されます。

前提条件:

• 管理者アクセス権が必要です。

ページを表示するには:

1. 右上隅で、管理者を選択します。
2. 左側のサイドバーで、モニタリング > データベース診断を選択します。

ページの2つのセクションが自動バキュームに関連しています。各セクションには、インストール内のデータベースごとに1つのカードがあります。

Vacuumの情報セクションには、Vacuumのアクティビティパネルが含まれています。このパネルには、現在実行中のバキュームごとに1行が表示されます。列は、Table、Type、Running for、Phase、Heap scanned、Indexes processed、Dead tuples、Index passes、Delay timeです。行には次のバッジが付くことがあります:

• アンチラップアラウンド: バキュームがトランザクションIDのラップアラウンドを防止しています。このバキュームは自動的にキャンセルされません。終了させないでください。終了させた場合、データベースはデータを保護するためにシャットダウンする可能性があります。
• 長時間実行中: バキュームが6時間以上実行されています。大規模なテーブルではこれくらいの時間がかかることがありますが、決して完了しないバキュームはブロックされているか、リソースが不足している可能性があります。
• メモリ負荷: バキュームに複数回のインデックスパスが必要でした。ヒープスキャンが完了する前に、デッドタプルストアがいっぱいになりました。maintenance_work_memまたはautovacuum_work_memを引き上げてください。

自動バキュームの設定セクションには、次のパネルが含まれています:

• 有効な設定: データベースが実際に使用している自動バキュームの設定で、pg_settingsから読み取られます。ステータス列には、チェックで検出された設定ミスがそれぞれマークされます。autovacuum_vacuum_cost_limitが-1の場合、パネルにはvacuum_cost_limitから継承される有効な制限値も表示されます。
• テーブルごとの上書き設定: ストレージパラメータで自動バキュームのパラメータを設定しているテーブル。このパネルには、各テーブルのサイズ、推定行数、および上書き設定が表示されます。自動バキュームを無効にしているテーブルには、自動バキューム無効バッジが付きます。このパネルには、自動バキュームを無効にしているテーブルを先頭にして、最大50個のテーブルが表示されます。上書き設定を持つテーブルがない場合、このパネルは非表示になります。
• スケールファクターのリスク: 有効なスケールファクターが依然として高い大規模なテーブル。チェックで該当するテーブルが見つからない場合、このパネルは非表示になります。

一部の検出結果は、単一の設定ではなくテーブルに関するものです。これらの検出結果は、パネルの上にアラートとして表示されます。各検出結果の背景にある条件については、自動バキュームの検出結果を参照してください。

自動バキュームはプライマリデータベースでのみ実行されるため、両方のセクションはプライマリデータベースから読み取ります。

コンソールで診断をレポートする

UIにアクセスできない場合、またはスクリプトからレポートを取得したい場合は、Rakeタスクを使用してください。

Linuxパッケージインストールの場合:

sudo gitlab-rake gitlab:db:diagnostics

デフォルトでは、このタスクはすべてのデータベースについてレポートします。データベースを選択するには、その名前を渡します:

sudo gitlab-rake "gitlab:db:diagnostics[main,ci]"

自動バキュームのチェックのみを実行するには、専用のタスクを使用してください。これは同じデータベース名を受け付けます:

sudo gitlab-rake "gitlab:db:diagnostics:autovacuum_settings[main,ci]"

レポートは、最初にサマリーを表示し、その後各チェックのセクションを表示します。自動バキューム設定セクションには、各データベースの検出結果、有効な設定、テーブルごとの上書き設定、およびスケールファクターのリスクが一覧表示されます。

タスクはエラーを検出するとステータス1で終了するため、モニタリングジョブはその終了コードを使用できます。

ターミナルでは、このタスクは複数のチェックを実行する場合に、各セクションを開くメニューを表示します。1つのチェックのみを実行する場合、または出力をパイプまたはリダイレクトする場合、このタスクはレポート全体を表示します。

自動バキュームの検出結果

データベース診断ページとRakeタスクは、同じチェックを使用します。どちらも同じ検出結果をレポートします。最初の5つの検出結果は、それぞれ1つの設定に関するもので、有効な設定パネルのステータス列に表示されます。最後の2つはテーブルに関するもので、アラートとして表示されます。

有効なスケールファクターは、設定されている場合はテーブルごとの上書き値であり、設定されていない場合はクラスター全体の値です。テーブルごとのautovacuum_vacuum_thresholdは、テーブルを対象外にはしません。このチェックは、自動バキュームが無効になっているテーブルをスキップします。

デッドタプルとフリーズ年齢を確認する

データベース診断ページもRakeタスクも、テーブルごとのデッドタプルやフリーズ年齢はレポートしません。代わりに、その情報についてはデータベースにクエリしてください。Linuxパッケージインストールでは、次のコマンドで接続します:

sudo gitlab-psql

デッドタプルが最も多いテーブルと、自動バキュームが最後にそれらを処理した時刻を確認するには:

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;

n_dead_tupのカウントが増加しており、last_autovacuumが古いか空である場合、自動バキュームがそのテーブルに追いついていないことを意味します。自動バキュームがテーブルに追いつけないを参照してください。

各テーブルのフリーズ年齢を確認するには:

SELECT n.nspname AS schema_name, c.relname AS table_name, age(c.relfrozenxid) AS xid_age
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY xid_age DESC
LIMIT 20;

xid_ageは、PostgreSQLが最後にテーブルをフリーズした後に実行されたトランザクションの数をカウントします。これをautovacuum_freeze_max_ageと比較します。有効な設定パネルはこの値をレポートします。テーブルがこの値を超えると、PostgreSQLはそのテーブルに対してアンチラップアラウンドの自動バキュームを開始します。フリーズ年齢が上昇し続けることは、自動バキュームが十分な速さで行をフリーズできていないことを意味します。トランザクションIDのラップアラウンドが迫っているを参照してください。

一般的な問題のトラブルシューティングを行う

自動バキュームがテーブルに追いつけない

テーブルは、自動バキュームが削除するよりも速くデッドタプルを蓄積します。デッドタプル数は増加し続け、テーブルは肥大化し、そのテーブルに対するクエリは遅くなります。この問題には、2つの一般的な原因があります:

• 高い変更率。データベースの移行、または行を頻繁に更新するエンドポイントは、自動バキュームがクリアするよりも速くデッドタプルを作成します。
• クラスター全体の設定を使用している、使用頻度の高いテーブル。大規模で頻繁に更新されるテーブルは、独自の自動バキューム設定が必要になることがよくあります。

遅れているテーブルを見つけるには、各テーブルのデッドタプル数と自動バキュームが最後に実行された時刻を確認してください。自動バキュームをモニタリングするを参照してください。この問題を解決するには:

• テーブルごとに低いスケールファクターまたはしきい値を設定します。詳細については、大規模なテーブルを個別にチューニングするを参照してください。
• autovacuum_vacuum_cost_limitとautovacuum_max_workersを引き上げます。詳細については、推奨される自動バキューム設定を参照してください。
• 変動の原因が移行またはアプリケーションの変更である場合は、それを一時停止または元に戻します。

トランザクションIDのラップアラウンドが迫っている

PostgreSQLは、各トランザクションにID（XID）を割り当てます。最も古い未フリーズのXIDが古くなるにつれて、PostgreSQLは古い行をフリーズするためにアンチラップアラウンドの自動バキュームを実行します。これらのバキュームが完了できない場合、データベースは最終的にデータを保護するために書き込みの受け付けを停止します。

このリスクを追跡するには、各データベースおよびテーブルのフリーズ年齢をモニタリングしてください。自動バキュームをモニタリングするを参照してください。フリーズ年齢が上昇し続けることは、自動バキュームが十分な速さで行をフリーズできていないことを意味します。

これを解決するには、自動バキュームが実行され、ブロックされていないことを確認してください。自動バキュームがテーブルに追いつけないを参照してください。データベースがすでに書き込みの受け付けを停止している場合は、データベースがラップアラウンドによるデータ損失を回避するためにコマンドを受け付けないに記載されている回復手順に従ってください。

自動バキュームが多くのリソースを使用する

自動バキュームは、他のクエリを遅くする可能性があるI/Oトラフィックを生成します。PostgreSQLは、コストベースの遅延によってこのトラフィックをスロットリングします。この遅延は、実行中のすべての自動バキュームワーカーに分散されます。そのため、実行されているワーカー数にかかわらず、合計I/Oへの影響はほぼ同じままになります。

他のクエリへの影響を減らすには、ワーカーがより頻繁に一時停止するようにautovacuum_vacuum_cost_delayを引き上げてください。遅延を大きくすると、バキュームの速度が遅くなります。デッドタプルに追いつく必要性とのバランスを取ってください。遅延を0に設定しないでください。これにより、スロットリングが完全に解除されます。詳細については、避けるべき設定を参照してください。