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

[アーティクル]
08/15/2024

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

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

自動バキュームとは

自動バキュームは PostgreSQL バックグラウンドプロセスであり、使用不能タプルを自動的にクリーンアップし、統計を更新します。これは、次の 2 つの主要なメンテナンスタスクを自動的に実行することで、データベースのパフォーマンスを維持するのに役立ちます。

VACUUM - 使用不能タプルを削除してディスク領域を解放します。
ANALYZE - PostgreSQL オプティマイザーがクエリに最適な実行パスを選択するのに役立つ統計を収集します。

自動バキュームが正しく動作するように、自動バキュームサーバーパラメーターは常に ON に設定する必要があります。有効にすると、PostgreSQL はテーブルで VACUUM または ANALYZE を実行するタイミングを自動的に決定し、データベースの効率と最適化を維持します。

自動バキュームの内部

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

パラメーター	説明
`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 よりも早くトリガーされます。 PG バージョン >=13 の場合、ANALYZE は既定で、テーブルの 20% に 1,000 行が挿入されたときにトリガーされます。

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

自動分析 = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold or autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (PG バージョン >= 13 の場合)
Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

たとえば、100 行のテーブルがある場合があります。次の数式は、分析とバキュームのトリガーのタイミングに関する情報を提供します。

更新/削除の場合: Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

テーブルで 60 行が変更された後に分析をトリガーし、テーブルで 70 行が変更されるとバキュームをトリガーします。

挿入の場合: Autoanalyze = 0.2 * 100 + 1000 = 1020

テーブルに 1,020 行が挿入された後に分析をトリガーします

数式で使用されるパラメーターの説明を次に示します。

パラメーター	説明
`autovacuum_analyze_scale_factor`	テーブルで ANALYZE をトリガーする挿入/更新/削除の割合。
`autovacuum_analyze_threshold`	テーブルを ANALYZE するために挿入/更新/削除されるタプルの最小数を指定します。
`autovacuum_vacuum_insert_scale_factor`	テーブルで ANALYZE をトリガーする挿入の割合。
`autovacuum_vacuum_insert_threshold`	テーブルを ANALYZE するために挿入されるタプルの最小数を指定します。
`autovacuum_vacuum_scale_factor`	テーブルで VACUUM をトリガーする更新/削除の割合。

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

 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_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 パラメーターの値を超えません。
autovacuum_vacuum_scale_factor は、使用不能タプルの蓄積に基づいてテーブルのバキュームをトリガーできるもう 1 つのパラメーターです。既定値: 0.2、許容範囲: 0.05 - 0.1。倍率はワークロード固有なので、テーブル内のデータ量に応じて設定するようにします。この値を変更する前に、ワークロードと個々のテーブルの量を調べてください。

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

自動バキュームを継続的に実行すると、サーバーの 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/データベース数]。

クラスター内のデータベース数が多い場合は 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);

挿入のみのワークロード

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

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

ソリューション

Postgres バージョン <= 13

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 つ目のトラブルシューティングガイドは、自動バキュームブロッカーとラップアラウンドです。これは、潜在的な自動バキュームブロッカーを特定するのに役立ちます。また、サーバー上のデータベースがラップアラウンドまたは緊急の状況からどれだけ離れているかに関する情報も提供します。トラブルシューティングガイドでは、潜在的な問題を軽減するための推奨事項も共有されています。トラブルシューティングガイドを設定して使用する方法については、トラブルシューティングガイドのセットアップに関する記事に従ってください。

Azure Advisor の推奨事項

Azure Advisor の推奨事項は、サーバーの肥大化率が高いかどうか、またはサーバーがトランザクションラップアラウンドシナリオに近づいているかどうかを事前に特定する方法です。「Azure portal を使用して新しい推奨事項に合わせて Azure Advisor アラートを作成する」を使用して、推奨事項のアラートを設定することもできます

推奨事項は次のとおりです。

高い肥大化率: 高い肥大化率は、いくつかの点でサーバーのパフォーマンスに影響する可能性があります。重要な問題の 1 つは、PostgreSQL エンジンオプティマイザーが最適な実行プランの選択に苦労し、クエリパフォーマンスの低下につながる可能性があることです。そのため、このようなパフォーマンスの問題を回避するために、サーバーの肥大化率が特定のしきい値に達すると、推奨事項がトリガーされます。
トランザクションラップアラウンド: このシナリオは、サーバーで発生する可能性がある最も重大な問題の 1 つです。サーバーがこの状態になると、それ以上のトランザクションの受け入れが停止し、サーバーが読み取り専用になる可能性があります。そのため、サーバーが 10 億トランザクションのしきい値を超えた場合に、推奨事項がトリガーされます。

次の方法で共有

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

自動バキュームとは

自動バキュームの内部

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

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

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

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

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

`maintenance_work_mem`

多数のデータベース

メモリ不足エラー

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

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

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

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

負荷の高いワークロード

長時間トランザクション

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

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

テーブル固有の要件

挿入のみのワークロード

ソリューション

Postgres バージョン <= 13

Postgres 13 以降のバージョン

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

Azure Advisor の推奨事項

フィードバック

その他のリソース

次の方法で共有

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

自動バキュームとは

自動バキュームの内部

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

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

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

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

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

maintenance_work_mem

多数のデータベース

メモリ不足エラー

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

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

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

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

負荷の高いワークロード

長時間トランザクション

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

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

テーブル固有の要件

挿入のみのワークロード

ソリューション

Postgres バージョン <= 13

Postgres 13 以降のバージョン

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

Azure Advisor の推奨事項

関連するコンテンツ

フィードバック

その他のリソース

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

`maintenance_work_mem`

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

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

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