次の方法で共有

Azure Database for PostgreSQL - フレキシブル サーバーでの自動バキュームのチューニング

  • [アーティクル]

適用対象: Azure Database for PostgreSQL - フレキシブル サーバー

この記事では、Azure Database for PostgreSQL フレキシブル サーバーの自動バキューム機能の概要と、データベースの肥大化、自動バキューム ブロッカーの監視に使用できる機能のトラブルシューティング ガイド、およびデータベースが緊急またはラップアラウンドの状況からどれだけ離れているかに関する情報を提供します。

自動バキュームとは

PostgreSQL の内部データの一貫性は、Multi-Version Concurrency Control (MVCC) メカニズムに基づいています。これにより、データベース エンジンは行の複数バージョンを保持できます。また、複数のプロセス間のブロックを最小限に抑えて高いコンカレンシーを実現できます。

PostgreSQL データベースには適切なメンテナンスが必要です。 たとえば、行を削除しても、物理的には削除されません。 その代わり、その行は "使用不能" とマークされます。 更新の場合も同様に、行は "使用不能" とマークされ、新しいバージョンの行が挿入されます。 これらの操作を実行すると、そのバージョンを参照する可能性があるすべてのトランザクションが完了した後でも、使用不能タプルと呼ばれる使用不能レコードが残ります。 クリーンアップしない限り使用不能タプルは残り、ディスク領域が消費され、テーブルとインデックスを肥大化するので、結果としてクエリのパフォーマンスが低下します。

PostgreSQL では、使用不能タプルを自動的にクリーンアップするために自動バキュームというプロセスが使われます。

自動バキュームの内部

自動バキュームは、ページを読み取って使用不能なタプルを検索し、何も見つからない場合には、そのページを破棄します。 自動バキュームによって検出された使用不能は削除されます。 コストは以下に基づいています。

  • vacuum_cost_page_hit: 既に共有バッファー内にあり、ディスクの読み取りを必要としないページを読み取るコスト。 既定値は 1 に設定されています。
  • vacuum_cost_page_miss: 共有バッファーにないページをフェッチするコスト。 既定値は 10 に設定されています。
  • vacuum_cost_page_dirty: 使用不能タプルが見つかったときにページに書き込むコスト。 既定値は 20 に設定されています。

自動バキュームの作業量は、2 つのパラメーターによって変わります。

  • autovacuum_vacuum_cost_limit は自動バキュームで一度に行われる作業量です。
  • autovacuum_vacuum_cost_delay は autovacuum_vacuum_cost_limit パラメーターで指定されたコスト制限に達した後に、自動バキュームがスリープ状態になるミリ秒数です。

現在サポートされているすべてのバージョンの Postgres では、autovacuum_vacuum_cost_limit の既定値は 200 になっています (実際には、既定で 200 に等しくなる標準 vacuum_cost_limit の値である、-1 に設定されます)。

autovacuum_vacuum_cost_delay については、Postgres バージョン 11 の既定値は 20 ミリ秒である一方、Postgres バージョン 12 以降では既定で 2 ミリ秒に設定されます。

自動バキュームは毎秒 50 回 (50*20 ms=1000 ms) スリープから復帰します。 スリープから復帰するたびに、自動バキュームは 200 ページを読み取ります。

つまり、自動バキュームは 1 秒間に次のことを実行できます。

  • 80 MB/秒以下 [ (200 ページ/vacuum_cost_page_hit) * 50 * 8 KB/ページ]: 共有バッファー内の使用不能タプルを含むすべてのページが検出された場合。
  • 8 MB/秒以下 [ (200 ページ/vacuum_cost_page_miss) * 50 * 8 KB/ページ]: 使用不能タプルを含むすべてのページがディスクから読み取られた場合。
  • 4 MB/秒以下 [ (200 ページ/vacuum_cost_page_dirty) * 50 * 8 KB/ページ]: 自動バキュームは最大 4 MB/秒まで書き込むことができます。

自動バキュームを監視する

自動バキュームを監視するには、次のクエリを使います。

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

次の列は、自動バキュームがテーブルのアクティビティに追いついているかどうかを判断するために役立ちます。

  • dead_pct: 有効なタプルと比較した場合の使用不能なタプルの割合。
  • last_autovacuum: そのテーブルが最後に自動バキュームされた日付。
  • last_autoanalyze: そのテーブルが最後に自動分析された日付。

PostgreSQL が自動バキュームをトリガーするタイミング

自動バキューム アクション (ANALYZE または VACUUM のいずれか) がトリガーされるのは、使用不能タプルの数が特定の数値 (テーブルの合計行数と、固定のしきい値という 2 つの要素によって変わります) を超えたときです。 ANALYZE がトリガーされるのは、既定で、テーブルの 10% + 50 行が変わったときです。一方、VACUUM がトリガーされるのは、テーブルの 20% + 50 行が変わったときです。 VACUUM のしきい値は ANALYZE のしきい値の 2 倍なので、ANALYZE は VACUUM よりも早くトリガーされます。

各アクションの正確な式は次のとおりです。

  • Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold
  • Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

たとえば、analyze は 100 行を含むテーブルの 60 行が変わった後にトリガーされ、vacuum は同じテーブルの 70 行が変わったときにトリガーされます。この場合、次の式を使います。

Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

次のクエリを使って、データベース内のテーブルを一覧表示し、自動バキューム プロセスの対象となるテーブルを特定します。

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

Note

このクエリでは、"alter table" DDL コマンドを使ってテーブル単位で自動バキュームを構成できることが考慮されていません。

一般的な自動バキュームの問題

次の一覧で、自動バキューム プロセスに関して考えられる、一般的な問題をレビューしてください。

ビジー状態のサーバーに追いつかない

自動バキューム プロセスでは、すべての I/O 操作のコストを見積もり、実行する各操作の合計を蓄積し、コストの上限に達した時点で一時停止します。 autovacuum_vacuum_cost_delay と autovacuum_vacuum_cost_limit は、このプロセスで使われる 2 つのサーバー パラメーターです。

既定では、autovacuum_vacuum_cost_limit は -1 に設定されています。つまり、自動バキュームのコスト上限はパラメーター vacuum_cost_limit と同じ値 (既定では 200) です。 vacuum_cost_limit は手動バキュームのコストです。

autovacuum_vacuum_cost_limit を -1 に設定した場合、自動バキュームには vacuum_cost_limit パラメーターが使われますが、autovacuum_vacuum_cost_limit 自体を -1 より大きく設定した場合は、autovacuum_vacuum_cost_limit パラメーターが考慮されます。

自動バキュームが追いつかない場合、次のパラメーターを変更できます。

パラメーター 説明
autovacuum_vacuum_scale_factor 既定値: 0.2、範囲: 0.05 - 0.1。 倍率はワークロード固有なので、テーブル内のデータ量に応じて設定するようにします。 この値を変更する前に、ワークロードと個々のテーブルの量を調べてください。
autovacuum_vacuum_cost_limit 既定値:200。 コスト制限を引き上げることができます。 変更の前と後に、データベースの CPU と I/O の使用率を監視する必要があります。
autovacuum_vacuum_cost_delay Postgres バージョン 11 - 既定値: 20 ms。 パラメーターは 2-10 ms に減らすことができます。
Postgres バージョン 12 以降 - 既定値: 2 ms。

Note

autovacuum_vacuum_cost_limit 値は、実行中の自動バキューム worker 数に比例して分散されるので、複数ある場合、各 worker の上限の合計は autovacuum_vacuum_cost_limit パラメーターの値を超えません

自動バキュームの継続的な実行

自動バキュームを継続的に実行すると、サーバーの CPU と IO の使用率に影響する可能性があります。 次のような理由が考えられます。

maintenance_work_mem

自動バキューム デーモンには、既定で -1 に設定されている autovacuum_work_mem が使われます。つまり、autovacuum_work_mem はパラメーター maintenance_work_mem と同じ値になります。 このドキュメントでは、autovacuum_work_mem を -1 に設定し、maintenance_work_mem が自動バキューム デーモンによって使われることを前提としています。

maintenance_work_mem が低い場合、Azure Database for PostgreSQL フレキシブル サーバーで最大 2 GB まで増やすことができます。 一般的な経験則では、1 GB の RAM ごとに maintenance_work_mem に 50 MB を割り当てます。

多数のデータベース

自動バキュームは、autovacuum_naptime 秒ごとに各データベース上で worker の開始を試みます。

たとえば、サーバーに 60 個のデータベースがあり、autovacuum_naptime を 60 秒に設定した場合、自動バキューム worker は毎秒開始されます [autovacuum_naptime/DB 数]。

クラスター内のデータベース数が多い場合は autovacuum_naptime を増やすことをお勧めします。 同時に、autovacuum_cost_limit を増やし、autovacuum_cost_delay パラメーターを減らし、autovacuum_max_workers を既定の 3 から 4 または 5 に増やすことによって、自動バキューム プロセスをさらに積極的に実行することができます。

メモリ不足エラー

maintenance_work_mem 値を過度に積極的にすると、システムでメモリ不足エラーが定期的に発生する可能性があります。 maintenance_work_mem パラメーターを変更する前に、サーバー上で使用できる RAM を把握することが重要です。

自動バキュームによって中断が頻繁に発生する

自動バキュームに多くのリソースが使われている場合、次のことを実行できます。

自動バキュームのパラメーター

パラメーター autovacuum_vacuum_cost_delay、autovacuum_vacuum_cost_limit、autovacuum_max_workers を評価します。 自動バキューム パラメーターの設定が不適切な場合、自動バキュームによって頻繁に中断が発生するシナリオになる可能性があります。

自動バキュームによって頻繁に中断が発生する場合は、次のことを検討してください。

  • autovacuum_vacuum_cost_delay を増やし、autovacuum_vacuum_cost_limit が既定の 200 より高く設定されている場合は減らします。
  • autovacuum_max_workers の数が既定の 3 よりも高く設定されている場合は減らします。

自動バキューム worker の数が多すぎる

自動バキューム worker の数を増やしても、バキュームの速度が上がるとは限りません。 自動バキューム worker の数を多くすることはお勧めしません。

自動バキューム worker の数を増やすと、より多くのメモリが消費されます。また、maintenance_work_mem の値によっては、パフォーマンスが低下する可能性があります。

各自動バキューム worker プロセスには、合計 autovacuum_cost_limit のうち (1/autovacuum_max_workers) のみが割り当てられるので、worker 数が多いと、それぞれが遅くなります。

worker 数を増やす場合は、autovacuum_vacuum_cost_limit も増やす、autovacuum_vacuum_cost_delay を減らしてバキューム プロセスを高速にする、またはその両方を行う必要があります。

ただし、テーブル レベルの autovacuum_vacuum_cost_delay または autovacuum_vacuum_cost_limit のパラメーターを変更した場合、それらのテーブル上で動作する worker は、分散アルゴリズム [autovacuum_cost_limit/autovacuum_max_workers] で考慮されなくなります。

自動バキューム トランザクション ID (TXID) のラップアラウンド保護

データベースでトランザクション ID のラップアラウンド保護が発生した場合、次のようなエラー メッセージを受け取ることがあります。

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

Note

このエラー メッセージは、長年にわたって見過ごされています。 通常、シングル ユーザー モードに切り替える必要はありません。 その代わり、必要な VACUUM コマンドを実行し、VACUUM が高速に実行されるようにチューニングすることができます。 データ操作言語 (DML) は実行できませんが、VACUUM を実行することはできます。

ラップアラウンドの問題は、データベースがバキュームされていない場合、または自動バキュームで削除できなかった使用不能タプルが多すぎる場合に発生します。 この理由として、次のことが考えられます。

負荷の高いワークロード

ワークロードによって短期間に発生した使用不能タプル数が多すぎて、自動バキュームが追いつくのが困難になることがあります。 システム内の使用不能タプルは一定期間蓄積され、クエリ パフォーマンスの低下とラップアラウンドの状況につながります。 この状況が発生する理由の 1 つは、自動バキュームのパラメーターが適切に設定されておらず、ビジー状態のサーバーに追いついていないことが考えられます。

長時間トランザクション

システム内で実行時間が長いトランザクションがあると、自動バキュームの実行中に使用不能タプルを削除できなくなります。 これらはバキューム プロセスの阻害要因です。 実行時間が長いトランザクションを削除すると、自動バキュームの実行時に削除対象の使用不能タプルを解放できます。

実行時間が長いトランザクションは、次のクエリを使って検出できます。

    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;

準備済みのステートメント

コミットされていない準備済みのステートメントがあると、使用不能タプルを削除できなくなります。
次のクエリは、コミットされていない準備済みのステートメントを見つけるのに役立ちます。

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

このようなステートメントをコミットまたはロールバックするには、COMMIT PREPARED または ROLLBACK PREPARED を使います。

使用されていないレプリケーション スロット

使用されていないレプリケーション スロットがあると、自動バキュームから使用不能タプルを要求できなくなります。 次のクエリは、使用されていないレプリケーション スロットを特定するのに役立ちます。

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

使用されていないレプリケーション スロットを削除するには、pg_drop_replication_slot() を使います。

データベースでトランザクション ID のラップアラウンド保護が発生した場合は、前述のように阻害要因がないか確認し、自動バキュームを続行および完了できるように手動でそれらを削除します。 また、autovacuum_cost_delay を 0 に設定し、autovacuum_cost_limit を 200 より大きな値に増やすことで、自動バキュームの速度を上げることもできます。 ただし、これらのパラメーターを変更しても、既存の自動バキューム worker には適用されません。 パラメーターの変更を適用するには、データベースを再起動するか、既存の worker を手動で強制終了してください。

テーブル固有の要件

自動バキューム パラメーターは、個々のテーブルに対して設定できます。 特に、テーブルが小さい場合と大きい場合に重要です。 たとえば、100 行しかない小さいテーブルの場合、70 行が変わると、(以前に計算したとおり) 自動バキュームによって VACUUM 操作がトリガーされます。 このテーブルが頻繁に更新される場合、1 日に何百回もの自動バキューム操作が発生する可能性があります。 この場合、変更の割合がそれほど大きくない他のテーブルを自動バキュームで保守できなくなります。 または、10 億行を含むテーブルの場合、自動バキューム操作をトリガーするには、2 億行を変更する必要があります。 自動バキューム パラメーターを適切に設定することで、このようなシナリオを防ぐことができます。

テーブルごとに自動バキュームの設定を行うには、次の例のようにサーバー パラメーターを変更します。

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);

挿入のみのワークロード

13 より前のバージョンの PostgreSQL の場合、ワークロードが挿入のみのテーブルに対して自動バキュームは実行されません。これは、更新または削除がない場合、使用不能タプルは存在せず、再利用する必要のある空き領域はないからです。 ただし、挿入のみのワークロードの場合は新しいデータが存在するので、自動分析が実行されます。 この欠点は次のとおりです。

  • テーブルの可視性マップが更新されません。そのため、クエリ パフォーマンス (特にインデックスのみのスキャンがある場合) は時間の経過と共に低下し始めます。
  • データベースでトランザクション ID のラップアラウンド保護が発生する可能性があります。
  • ヒント ビットは設定されません。

ソリューション

13 より前の Postgres バージョン

pg_cron 拡張機能を使うと、テーブルに対して定期的なバキューム分析をスケジュールする cron ジョブを設定できます。 cron ジョブの頻度はワークロードによって異なります。

pg_cron を使う手順のガイダンスについては、拡張機能に関する記事を参照してください。

Postgres 13 以降のバージョン

自動バキュームは、ワークロードが挿入のみのテーブルに対して実行されます。 2 つの新しいサーバー パラメーター autovacuum_vacuum_insert_threshold と autovacuum_vacuum_insert_scale_factor を使うと、挿入のみのテーブルに対して自動バキュームをトリガーするタイミングを制御できます。

トラブルシューティング ガイド

Azure Database for PostgreSQL フレキシブル サーバー ポータルで利用できる機能のトラブルシューティング ガイドを使用すると、データベースまたは個々のスキーマ レベルで肥大化を監視し、自動バキューム プロセスの潜在的なブロッカーを特定することができます。 2 つのトラブルシューティング ガイドを使用できます。1 つ目は自動バキュームの監視であり、データベースまたは個々のスキーマ レベルで肥大化を監視するために使用できます。 2 つ目のトラブルシューティング ガイドは、自動バキューム ブロッカーとラップアラウンドです。これは、潜在的な自動バキューム ブロッカーと、サーバー上のデータベースがラップアラウンドまたは緊急の状況からどれだけ離れているかに関する情報を特定するのに役立ちます。 トラブルシューティング ガイドでは、潜在的な問題を軽減するための推奨事項も共有されています。 トラブルシューティング ガイドを設定して使用する方法については、トラブルシューティング ガイドのセットアップに従ってください。

関連するコンテンツ