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

自律チューニングは、ワークロードから記録されたクエリを分析し、それらのクエリのパフォーマンスを向上させるための推奨事項を提供する、Azure Database for PostgreSQLフレキシブル サーバーの機能です。

これは、Azure Database for PostgreSQL フレキシブル サーバーに組み込まれた、クエリ ストア機能を基盤とする機能です。 自律チューニングでは、クエリ ストアによって追跡されるワークロードが分析され、分析されたワークロードのパフォーマンスを向上させるためにインデックスまたはテーブルの推奨事項が生成されます。 新しいインデックスの作成、重複または未使用のインデックスの排除、統計や古い統計がないテーブルの分析、またはバキューム肥大化テーブルの推奨事項を生成できます。

• 自律チューニング セッション中に分析されたクエリが大幅に向上する可能性があるため、作成に役立つインデックスを特定します。
• 正確に重複し、削除できるインデックスを特定します。
• 除去の候補になる可能性がある、構成可能な期間内に使われていないインデックスを特定します。
• 無効としてマークされたインデックスを特定 し、インデックスを再作成して有効なインデックスに変換する必要があります。
• 分析する必要がある現在の統計がないテーブルを特定します。
• 肥大化したテーブルを特定し、バキュームする必要があります。

自律チューニング アルゴリズムの一般的な説明

index_tuning.modeするように report パラメーターを構成すると、index_tuning.analysis_interval パラメーターで構成した頻度 (分単位) でチューニング セッションが自動的に開始されます。

最初のフェーズでは、チューニング セッションは、推奨事項がシステムの全体的なパフォーマンスに大きな影響を与える可能性があるデータベースの一覧を検索します。 そのため、このチューニング セッションの検索対象期間内に実行が取り込まれ、クエリ ストアによって記録されたすべてのクエリが収集されます。 現在、検索間隔の範囲は、チューニング セッションの開始時刻から過去 index_tuning.analysis_interval 分までです。

クエリ ストアに実行が記録されていて、実行時統計がリセットされていない、すべてのユーザー開始クエリについて、システムは集計された合計実行時間に基づいてそれらをランク付けします。 クエリの実行時間に基づき、最も重要度の高いクエリに焦点を当てます。

次のクエリは、その一覧から除外されます。

• システムによって開始されたクエリ。 (つまり、azuresu ロールによって実行されたクエリ)
• システム データベース (azure_sys、template0、template1、azure_maintenance) のコンテキストで実行されたクエリ。

アルゴリズムはターゲットのデータベースを反復処理し、分析対象のワークロードのパフォーマンスを向上させる可能性のあるインデックスを検索します。 また、重複しているか、構成可能な期間使用されていないために削除できるインデックスも検索します。 また、現在の統計または肥大化されたテーブルがないテーブルも識別します。

CREATE INDEX に関する推奨事項

分析する候補として識別された各データベースについて、このプロセスでは、検索間隔中およびその特定のデータベースのコンテキストで実行されたすべての SELECT、UPDATE、INSERT、DELETE クエリが考慮されます。

このプロセスでは、集計された合計実行時間に基づいて結果の一連のクエリがランク付けされ、上位の index_tuning.max_queries_per_database でインデックスの推奨事項が分析されます。

次の種類のクエリのパフォーマンスを向上させるための推奨事項事項が提供される可能性があります。

• フィルターを使用するクエリ (つまり、WHERE 句の述語を持つクエリ)。
• 複数の関係を結合するクエリ。結合が JOIN 句で表される構文に従っているか、または結合述語が WHERE 句で表されているかどうか。
• フィルターと結合述語を組み合わせたクエリ。
• グループ化を含むクエリ (GROUP BY 句を使うクエリ)。
• フィルターとグループ化を組み合わせたクエリ。
• 並べ替えを含むクエリ (ORDER BY 句を使うクエリ)。
• フィルターと並べ替えを組み合わせたクエリ。

クエリがテーブルの 1 つの列を参照し、そのテーブルに統計がない場合、実行を改善するためのインデックスの推奨事項は生成されません。 ただし、テーブルを分析するための推奨事項が生成されます。

index_tuning.max_indexes_per_table は、推奨できるインデックスの数を指定します。ただし、チューニング セッションの間に任意の数のクエリによって参照された単一のテーブルについて、テーブルに既に存在する可能性があるインデックスは除きます。

index_tuning.max_index_count は、チューニング セッションの間に分析されるデータベースのすべてのテーブルについて生成されるインデックス推奨事項の数を指定します。

インデックス推奨事項が出力されるためには、分析対象のワークロード内の少なくとも 1 つのクエリが、index_tuning.min_improvement_factor で指定された係数だけ改善すると、チューニング エンジンが推定する必要があります。

同様に、このプロセスでは、すべてのインデックスの推奨事項がチェックされ、 index_tuning.max_regression_factorで指定された要因のワークロード内の 1 つのクエリに回帰が導入されていないことが確認されます。

前の段落で説明したすべてのパラメーター、既定値、および有効な範囲については、 構成オプションで説明します。

インデックスを作成するための推奨事項と共に生成されるスクリプトは、次のパターンに従います。

CREATE INDEX CONCURRENTLY {indexName} ON {schema}.{table}({column_name}[, ...])

CONCURRENTLY 句を含みます。 この句の効果の詳細については、 CREATE INDEX の PostgreSQL 公式ドキュメントを参照してください。

自律チューニングでは、推奨されるインデックスの名前が自動的に生成されます。これは通常、"_" (アンダースコア) で区切られた異なるキー列の名前と、定数 "_idx" サフィックスで構成されます。 名前の合計長が PostgreSQL の制限を超える場合、または既存の関係と競合する場合、名前は若干異なるものになります。 切り詰められて、名前の末尾に数字が追加される可能性があります。

CREATE INDEX の推奨事項の影響を計算する

インデックスの推奨事項を作成した場合の影響は、IndexSize (メガバイト) と QueryCostImprovement (パーセンテージ) について測定されます。

IndexSize は、テーブルの現在のカーディナリティと、推奨インデックスによって参照される列のサイズを考慮した、インデックスの推定サイズを表す 1 つの値です。

QueryCostImprovement は値の配列で構成され、各要素は、このインデックスが存在した場合にプランのコストが改善すると推定される各クエリについて、プランのコストがどの程度改善されるかを表します。 各要素では、クエリの識別子 (queried) と、推奨事項が実装された場合にプランのコストが改善する割合 (dimensional) が示されています。

DROP INDEX と REINDEX のレコメンデーション

候補として識別されたデータベースごとに、プロセスによって新しいセッションが開始されます。 CREATE INDEX の推奨事項フェーズが完了したら、次の条件に基づいて既存のインデックスを削除またはインデックスを再作成することをお勧めします。

• 他のものと重複していると考えられる場合はドロップします。
• 構成可能な期間に使用されていない場合はドロップします。
• 無効としてマークされているインデックスのインデックスを再作成します。

重複するインデックスを削除する

重複するインデックスを削除するための推奨事項は、まず、重複するインデックスを特定することから始めます。

重複は、インデックスに属性を付けることができるさまざまな関数に基づいてランク付けされ、その推定サイズに基づいてランク付けされます。

最後に、このプロセスでは、参照リーダーよりも低いランク付けですべての重複を削除し、各重複がそのようにランク付けされた理由を説明します。

2 つのインデックスを重複と見なすには、次の操作を行う必要があります。

• 同じテーブルに対して作成されている。
• まったく同じ型のインデックスである。
• それらのキー列を一致させ、複数列のインデックス キーについては、参照される順序も一致させます。
• その述語の式ツリーが一致する。 この条件は、部分インデックスにのみ適用されます。
• すべての非単純列の参照の式ツリーが一致する。 この条件は、式で作成されたインデックスにのみ適用されます。
• キーで参照される各列の照合順序が一致する。

未使用のインデックスを削除する

未使用のインデックスを削除するための推奨事項では、次のインデックスが識別されます。

• 少なくとも index_tuning.unused_min_period 日間使われていない。
• インデックスが作成されたテーブルで最小の index_tuning.unused_dml_per_table DML の量 (日単位の平均) を示します。
• インデックスが作成されたテーブルで最小の index_tuning.unused_reads_per_table 読み取りの量 (日単位の平均) を示します。

無効なインデックスのインデックスを再作成する

既存のインデックスのインデックスを再作成するための推奨事項では、無効としてマークされているインデックスが識別されます。 インデックスが無効としてマークされている理由とタイミングの詳細については、PostgreSQL の公式ドキュメントの REINDEX を参照してください。

DROP INDEX の推奨事項の影響を計算する

インデックス削除推奨事項の影響は、Benefit (パーセンテージ) と IndexSize (メガバイト) の 2 つのディメンションで測定されます。

この利点は、現時点では無視できる 1 つの値です。

IndexSize は、テーブルの現在のカーディナリティと、推奨インデックスによって参照される列のサイズを考慮した、インデックスの推定サイズを表す 1 つの値です。

テーブルの推奨事項

分析する候補として識別されたデータベースごとに、テーブル レベルの推奨事項を生成することを目的としたセッションが開始されます。 これらの推奨事項は、クエリがアクセスを検査したテーブルに対して ANALYZE または VACUUM を実行するように招待します。 チューニング エンジンでは、これらのコマンドを実行するとワークロードのパフォーマンスが向上する可能性があると見なされます。

ANALYZE テーブルのレコメンデーション

テーブルを分析するための推奨事項では、次のテーブルが識別されます。

• クエリで参照され、その述語の 1 つ (WHERE、 JOIN、 ORDER BY、 GROUP BY) で使用されるテーブルの列があり、次の 2 つの条件のいずれかを満たします。
  • 分析されません。
  • ある時点で分析されましたが、統計情報が不足しています (通常、統計がディスクに保持される前にサーバーがクラッシュしたため)。

VACUUM テーブルの推奨事項

テーブルのバキュームに関するレコメンデーションは、肥大化したテーブルを識別します。 このプロセスでは、ワークロードの分析時に autovacuum_enabled がサーバー レベルで off に設定されていない場合にのみ、これらの推奨事項が生成されます。

自律チューニングの構成

動作を制御する一連のパラメーターを使用して、自律チューニングを有効、無効、および構成できます。

自律チューニングを有効にすると、 index_tuning.analysis_interval パラメーター (既定値は 720 分または 12 時間) で構成された頻度で起動し、その期間中にクエリ ストアによって記録されたワークロードの分析が開始されます。

index_tuning.analysis_intervalの値を変更した場合、新しい値は、次のスケジュールされた実行が完了した後にのみ有効になります。 たとえば、1 日の午前 10 時に自律チューニングを有効にした場合、 index_tuning.analysis_interval の既定値は 720 分であるため、最初の実行は同じ日の午後 10 時に開始するようにスケジュールされます。 午前 10:00 から午後 10:00 の間に index_tuning.analysis_interval の値に加えた変更は、その初期スケジュールには影響しません。 スケジュールされた実行が完了した場合にのみ、 index_tuning.analysis_interval に設定されている現在の値が読み取られ、その値に従って次の実行がスケジュールされます。

自律チューニング パラメーターを構成するには、次のオプションを使用します。

CLI コマンドaz postgres flexible-server autonomous-tuning show-settingsとaz postgres flexible-server autonomous-tuning set-settingsを使用して自律チューニング設定を表示または変更する場合、--nameパラメーターの引数として受け入れられる値は、前の表のParameter列に示されているもので、プレフィックスindex_tuning.を除いたものです。

自律チューニングによって生成される情報

自律チューニングの推奨事項を使用して、自律チューニング によって生成される推奨事項を取得して使用する方法について詳しく説明します。

制限事項とサポート可能性

次の一覧では、自律チューニングの制限事項とサポート範囲について説明します。

推奨事項の自動削除

推奨事項は、前回生成されてから 35 日後に自動的に削除されます。 この自動削除メカニズムを機能させるには、自律チューニングを有効にする必要があります。

hypopg 拡張機能の依存関係

CREATE INDEX推奨事項を生成するために、自律チューニングでは hypopg 拡張機能が使用されます。

チューニング セッションの開始時に拡張機能が存在する場合、プロセスは作成されたスキーマで拡張機能を使用します。 チューニング セッションが完了しても、プロセスは拡張機能を削除しません。 この規則の例外は、拡張機能が pg_catalog スキーマで作成された場合です。 その場合、自律チューニングによって拡張機能が削除されます。

拡張機能が最初の場所に存在しなかった場合、またはプロセスが pg_catalog スキーマで作成されたために削除された場合、自律チューニングによって、 ms_temp_recommendations709253という名前のスキーマの下に作成されます。 チューニング セッションが正常に完了すると、プロセスによって拡張機能が削除され、スキーマが削除されます。

azure_pg_admin ロールのメンバーであるユーザーは、自律チューニング機能によって作成された場合でも、いつでも hypopg 拡張機能を削除できます。 ただし、自律チューニング セッションの実行中に削除すると、そのセッションが失敗し、推奨事項が生成されない可能性があります。

サポートされているコンピューティング レベルと SKU

Azure Database for PostgreSQLフレキシブル サーバーは、現在使用可能なすべてのレベル (バースト可能、汎用、メモリ最適化) で自律チューニングをサポートしています。 また、少なくとも 4 つの仮想コアを持つ 現在サポートされているコンピューティング SKU で自律的なチューニングもサポートしています。

サポートされている PostgreSQL のバージョン

Azure Database for PostgreSQLフレキシブル サーバーでは、メジャー バージョン12 以降での自律チューニングがサポートされています。

search_path の使用

自律チューニングでは、query_store.qs_view の search_path 列の値が使用されます。 各クエリを分析するときに、クエリが最初に実行されたときに設定されたのと同じ search_path 値を使用して、考えられる推奨事項を分析します。

パラメーター化されたクエリ

PREPARE を使用して、または拡張クエリ プロトコルを使用して作成されたパラメーター化されたクエリが解析および分析され、インデックスの推奨事項が生成されます。

パラメーター化されたクエリを分析するには、自律チューニングでは、クエリ ストアがクエリの実行を キャプチャするときに、pg_qs.parameters_capture_mode を capture_first_sample に設定する必要があります。 また、クエリの実行時に、クエリ ストアがパラメーターを正しくキャプチャすることも必要です。 つまり、分析対象のクエリでは、query_store.qs_view のparameters_capture_status列をsucceededに設定する必要があります。

読み取り専用モードと読み取りレプリカ

自律チューニングは 、クエリ ストア が azure_sys データベースにローカルに保持されるデータに依存し、 読み取りレプリカまたはサーバーが読み取り専用モードの場合 はサポートされていないため、読み取りレプリカまたは読み取り専用モードのサーバーではこの機能はサポートされません。

読み取りレプリカに表示される推奨事項は、プライマリ レプリカで実行されたワークロードのみを分析した後、プライマリ レプリカで生成されました。

コンピューティングのスケールダウン

サーバーで自律チューニングを有効にし、そのサーバーのコンピューティングを必要な仮想コアの最小数未満にスケールダウンした場合、この機能は有効なままです。 この機能は仮想コア数が 4 未満のサーバーではサポートされていないため、コンピューティングのスケールダウン時に index_tuning.mode が ON に設定されている場合でも、ワークロードを分析して推奨事項を生成するために実行されません。 サーバーは最小要件を満たしていませんが、すべての index_tuning.* パラメーターにアクセスできません。 最小要件を満たすコンピューティングにサーバーをスケールアップするたびに、 index_tuning.mode は、要件を満たしていないコンピューティングにスケールダウンする前に、設定された値で構成されます。

高可用性と読み取りレプリカ

サーバーで 高可用性 レプリカまたは 読み取りレプリカ を構成する場合は、推奨インデックスを実装するときに、プライマリ サーバーでの書き込み負荷の高いワークロードの生成に関連する影響に注意してください。 推定されるサイズが大きいインデックスの作成には特に注意してください。

自律チューニングによって特定のクエリに対するインデックスの作成に関する推奨事項が生成されない理由

自律チューニングでは、次の種類のクエリに対する CREATE INDEX 推奨事項は生成されません。

• 自律チューニング エンジンが分析フェーズ中に EXPLAIN 出力を取得しようとしたときにエラーが発生するクエリ。
• pg_statistic システム カタログ内の内容に関する統計なしでテーブルを参照するクエリ。 これらのテーブルに対して ANALYZE を実行して、チューニング エンジンが将来これらのクエリを考慮できるようにします。
• クエリ ストア内のクエリ テキストが切り捨てられたクエリ。 この切り捨ては、クエリ テキストの長さが pg_qs.max_query_text_length で構成された値を超えた場合に発生します。
• 分析が行われる前に削除または名前を変更したオブジェクトを参照するクエリ。 これらのクエリは構文的に有効ですが、意味的には有効ではありません。
• 一時テーブルまたは一時テーブルのインデックスにアクセスするクエリ。
• ビューまたは具体化されたビューにアクセスするクエリ。
• パーティション テーブルにアクセスするクエリ。
• ユーティリティ ステートメントとして識別されるクエリ。 ユーティリティ ステートメントまたはユーティリティ コマンドは、基本的に、 SELECT、 INSERT、 UPDATE、 DELETE、または MERGEと見なされないステートメントと、これらのステートメントのいずれかを含む特定のコマンドです。
• 分析対象のデータベースと期間において、最も遅い上位 index_tuning.max_queries_per_database 件に含まれないクエリ。
• 1 つの特定のデータベースのコンテキストで実行されるクエリ 。サーバー レベルで最も低速なクエリが 1 つも識別されない場合。

関連コンテンツ

• クエリ ストア。
• 自律チューニングを構成します。
• 自律チューニングの推奨事項を使用します。