Aurora PostgreSQL のクエリ実行計画の管理 - Amazon Aurora

Aurora PostgreSQL のクエリ実行計画の管理

Amazon Aurora PostgreSQL 互換エディション のクエリ計画管理を使用すると、クエリ実行計画の変更方法と変更時期を制御できます。クエリ計画管理には、主に 2 つの目的があります。

  • データベースシステム変更時に起きる計画不具合の防止

  • クエリオプティマイザで新しい計画が使用可能になる時期の制御

クエリ最適化の品質と整合性は、リレーショナルデータベース管理システム (RDBMS) のパフォーマンスと安定性に大きく影響します。クエリオプティマイザは、ある時点における SQL ステートメントのクエリ実行計画を作成します。そのため、条件が変更されると、オプティマイザはパフォーマンスを改良または悪化させる別の計画を選択する場合があります。場合によっては、多数を変更することで、クエリオプティマイザで別の計画が選択され、パフォーマンスが低下する原因になります。例えば、統計情報、制限事項、環境設定、クエリパラメータのバインディング、ソフトウェアアップグレードの変更などがあります。パフォーマンス低下は、高性能アプリケーションにおける大きな懸念事項です。

クエリ計画管理により、管理したい一連のステートメントに対する実行計画を制御できます。以下の操作を行うことができます。

  • オプティマイザに、問題のないことがわかっている少数の計画から強制的に選択させることで、計画の安定性を改善する。

  • 計画を一元的に最適化してから、最善の計画を全体に配布する。

  • 使用されていないインデックスを特定し、インデックスの作成または削除の影響を評価する。

  • オプティマイザが発見した、新しい最小コスト計画を自動的に検出する。

  • パフォーマンスを改善する計画変更のみが承認されるように選択し、新しいオプティマイザの機能を少ないリスクで試す。

Aurora PostgreSQL のクエリ計画管理の有効化

クエリプラン管理は、次の Aurora PostgreSQL バージョンで使用できます:

  • すべての Aurora PostgreSQL 13 バージョン

  • Aurora PostgreSQL 12.4 以降のバージョン

  • Aurora PostgreSQL 11.6 以降のバージョン

  • Aurora PostgreSQL 10.5 以降のバージョン

次の手順を完了できるのは、rds_superuser ロールを持つユーザーのみです。rds_superuser は、apg_plan_mgmt エクステンションとその apg_plan_mgmt ロールの作成に必要です。apg_plan_mgmt エクステンションを管理するには、ユーザーに apg_plan_mgmt ロールを付与する必要があります。

クエリ計画管理を有効化するには

  1. Amazon RDS コンソール (https://console.aws.amazon.com/rds/) を開きます。

  2. クエリ計画管理のパラメータとして使用する新しいインスタンスレベルのパラメータグループを作成します。詳細については、「DB パラメータグループを作成する」を参照してください。新しいパラメータグループを、クエリ計画管理を使用する DB インスタンスに関連付けます。詳細については、「DB クラスター内の DB インスタンスの変更」を参照してください。

  3. クエリ計画管理のパラメータとして使用する新しいクラスターレベルのパラメータグループを作成します。詳細については、「DB クラスターのパラメータグループの作成」を参照してください。新しいクラスターレベルのパラメータグループを、クエリ計画管理を使用する DB クラスターに関連付けます。詳細については、「コンソール、CLI、API を使用した DB クラスターの変更」を参照してください。

  4. クラスターレベルのパラメータグループを開き、rds.enable_plan_management パラメータを 1 に設定します。詳細については、「DB クラスターパラメータグループのパラメータの変更」を参照してください。

  5. この新しい設定を有効にするには、DB インスタンスを再起動してください。

  6. psql などの SQL クライアントを使用して DB インスタンスに接続します。

  7. DB インスタンス用の apg_plan_mgmt エクステンションを作成します。例を以下に示します。

    psql my-database my-database=> CREATE EXTENSION apg_plan_mgmt;

    apg_plan_mgmt のデフォルトのデータベースに template1 エクステンションを作成すると、新しいデータベースを作成するたびに、クエリ計画管理のエクステンションが使用できるようになります。

クエリプランの管理は、apg_plan_mgmt.use_plan_baselines および apg_plan_mgmt.capture_plan_baselines をオフにすることで、いつでも無効にできます。

my-database=> SET apg_plan_mgmt.use_plan_baselines = off; my-database=> SET apg_plan_mgmt.capture_plan_baselines = off;

クエリ計画管理のアップグレード

クエリ計画管理の最新バージョンは 2.0 です。以前のバージョンのクエリ計画管理をインストールした場合は、バージョン 2.0 にアップグレードすることを強くお勧めします。バージョンの詳細については、「Amazon Aurora PostgreSQL のエクステンションバージョン」を参照してください。

アップグレードするには、以下のコマンドをクラスターレベルまたは DB インスタンスレベルで実行します。

ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.0'; SELECT apg_plan_mgmt.validate_plans('update_plan_hash'); SELECT apg_plan_mgmt.reload();

クエリ計画管理の基本

クエリ計画管理では、ステートメントの複雑さに関係なく、SELECT、INSERT、UPDATE、または DELETE ステートメントを管理できます。準備済み、動的、組み込み、および即時モードの SQL ステートメントがすべてサポートされています。パーティショニングされたテーブル、継承、行レベルセキュリティ、再帰的なテーブル共通表現 (CTE) など、PostgreSQL のすべての言語機能を使用できます。

手動計画取り込みの実行

特定のステートメントの計画を取得するには、手動取り込みモードを次の例のように使用します。

/* Turn on manual capture */ SET apg_plan_mgmt.capture_plan_baselines = manual; EXPLAIN SELECT COUNT(*) from pg_class; -- capture the plan baseline SET apg_plan_mgmt.capture_plan_baselines = off; -- turn off capture SET apg_plan_mgmt.use_plan_baselines = true; -- turn on plan usage

SELECT、INSERT、UPDATE、または DELETE ステートメントを実行するか、EXPLAIN ステートメントを上述のように含めることもできます。ステートメントの実行によるオーバーヘッドや、潜在的な副作用のない状態で計画を取得するために EXPLAIN を使用します。手動による取得の詳細については、「特定の SQL ステートメントの計画の手動取り込み」を参照してください。クエリ計画管理では、pg_class など、システムテーブルを参照するステートメントの計画は保存されないのでご注意ください。

取得された計画の表示

前の例で EXPLAIN SELECT が実行する際、オプティマイザによって計画が保存されます。そのためにオプティマイザは、行を apg_plan_mgmt.dba_plans ビューに挿入し、自律型トランザクションでそれをコミットします。apg_plan_mgmt.dba_plans ロールを付与されている場合、apg_plan_mgmt の内容を表示できます。以下のクエリは、dba_plans ビューの重要な列の一部を表示します。

SELECT sql_hash, plan_hash, status, enabled, plan_outline, sql_text::varchar(40) FROM apg_plan_mgmt.dba_plans ORDER BY sql_text, plan_created;

表示される各行は、管理計画を表します。前述の例には、次の情報が表示されます。

  • sql_hash - 計画の対象となる、管理ステートメントの ID。

  • plan_hash - 管理計画の ID。

  • status - 計画のステータス。オプティマイザは、承認済みの計画を実行できます。

  • enabled - 計画が有効で使用可能か、または無効で使用不可かどうかを示す値。

  • plan_outline - 管理計画の詳細。

apg_plan_mgmt.dba_plans ビューの詳細については、「apg_plan_mgmt.dba_plans ビューでの計画の検証」を参照してください。

管理ステートメントと SQL ハッシュの使用

管理ステートメントは、クエリ計画管理でオプティマイザにより取得された SQL ステートメントです。手動または自動のいずれかの取得方法を使用して、管理ステートメントとして取得する SQL ステートメントを指定します。

  • 手動取得の場合は、前の例で示したように、オプティマイザに特定のステートメントを指定します。

  • 自動取得の場合、オプティマイザは複数回実行されるステートメントの計画を取得します。自動取得の例は後に示します。

apg_plan_mgmt.dba_plans ビューでは、SQL のハッシュ値で管理ステートメントを識別できます。SQL ハッシュは、リテラル値などのいくつかの違いを取り除く SQL ステートメントの正規化表現で計算されます。正規化を使用するということは、複数の SQL ステートメントでリテラル値またはパラメータ値のみが異なる場合、apg_plan_mgmt.dba_plans ビューにおいて同じ SQL ハッシュで表されることを意味します。したがって、同じ SQL ハッシュに対して複数の計画がある可能性があります。それぞれの計画が異なる条件下で最適となります。

SQL ステートメントを処理するとき、オプティマイザは次の規則に従って正規化 SQL ステートメントを作成します。

  • 先頭のブロックコメントを削除する

  • EXPLAIN キーワードと EXPLAIN オプションを削除する (ある場合)

  • 末尾のスペースを削除する

  • すべてのリテラルを削除する

  • 読みやすくするために空白および大文字と小文字の区別を保持する

例えば、以下のステートメントを見てみましょう。

/*Leading comment*/ EXPLAIN SELECT /* Query 1 */ * FROM t WHERE x > 7 AND y = 1;

オプティマイザは、このステートメントを次のように正規化します。

SELECT /* Query 1 */ * FROM t WHERE x > CONST AND y = CONST;

自動計画取り込みの使用

アプリケーション内のすべての SQL ステートメントの計画を取得する場合、または手動取り込みを使用できない場合は、自動計画取り込みを使用してください。自動計画取り込みを使用すると、オプティマイザは、少なくとも 2 回実行されるステートメントの計画を取得します。自動計画取り込みを使用するには、以下を実行します。

  1. 実行している Aurora PostgreSQL のバージョンの、デフォルトの DB パラメータグループに基づき、カスタム DB パラメータグループを作成します。

  2. apg_plan_mgmt.capture_plan_baselines 設定を automatic に変更して、カスタム DB パラメータグループを編集します。

  3. カスタマイズした DB パラメータグループを保存します。

  4. このカスタム DB パラメータグループを、既に実行している Aurora DB インスタンスに次のように適用します。

    • ナビゲーションペインのリストから Aurora PostgreSQL DB インスタンスを選択し、[変更] を選択します。

    • [DB インスタンスを変更] ページの [追加設定] のセクションで、DB パラメータグループに対して、カスタム DB パラメータグループを選択します。

    • [Continue (続行)] を選択します。[変更のサマリー] を確認し、[すぐに適用] を選択します。

    • [DB インスタンスを変更] を選択し、カスタム DB パラメータグループを適用します。

カスタム DB パラメータグループは、新しい Aurora PostgreSQL DB インスタンスを作成するときにも使用できます。パラメータグループの詳細については、「DB パラメータグループのパラメータの変更」を参照してください。

アプリケーションが実行されると、オプティマイザは 1 回以上実行されるステートメントの計画を取得します。オプティマイザは、管理ステートメントの初期に取得された計画のステータスを常に approved に設定します。管理ステートメントの、一連の承認済み計画は、計画ベースラインと呼ばれます。

アプリケーションが継続して実行されると、オプティマイザが管理ステートメントに追加の計画を見つけることがあります。オプティマイザは、追加でキャプチャされた計画を Unapproved ステータスに設定します。

管理ステートメント用に取得された一連のすべての計画は、計画履歴と呼ばれます。後で、Unapproved 計画が正常に機能するかどうかを確認し、Approved 関数または Rejected 関数を使用してステータスを Preferredapg_plan_mgmt.evolve_plan_baselines、または apg_plan_mgmt.set_plan_status に変更できます。

自動計画取り込みをオフにするには、DB インスタンスのパラメータグループで apg_plan_mgmt.capture_plan_baselinesoff に設定します。上記で説明したものと同じ一般的な手順に従って、apg_plan_mgmt.capture_plan_baselines にカスタム DB パラメータのグループ値を変更し、このカスタム DB パラメータグループを Aurora DB インスタンスに適用します。

計画取り込みの詳細については、「実行計画の取得」を参照してください。

計画の検証

管理計画が依存するオブジェクト (インデックスなど) が削除されると、管理計画は利用不可能 (「停滞」) になる可能性があります。停滞しているすべての計画を検索して削除するには、apg_plan_mgmt.validate_plans 関数を使用します。

SELECT apg_plan_mgmt.validate_plans('delete');

詳細については、「計画の検証」を参照してください。

パフォーマンスを向上させる新しい計画の承認

管理計画の使用中に、オプティマイザによって検出された新しい低コスト計画が、既に計画ベースラインに含まれている最小コスト計画よりも速いかどうかを確認できます。パフォーマンス比較を行い、必要に応じてより速い計画を承認するには、apg_plan_mgmt.evolve_plan_baselines 関数を呼び出します。

次の例では、有効になっており、計画ベースラインにおける最小コスト計画より少なくとも 10% 速い未承認の計画を、自動的に承認します。

SELECT apg_plan_mgmt.evolve_plan_baselines( sql_hash, plan_hash, 1.1, 'approve' ) FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved' AND enabled = true;

apg_plan_mgmt.evolve_plan_baselines 関数は実行されると、パフォーマンス統計を収集して apg_plan_mgmt.dba_plans ビューの planning_time_msexecution_time_mscardinality_errortotal_time_benefit_ms、および execution_time_benefit_ms 列に保存します。apg_plan_mgmt.evolve_plan_baselines 関数は、列 last_verified または last_validated timestamps も更新します。この列から、パフォーマンス統計が収集された最新の時刻を見ることができます。

SELECT sql_hash, plan_hash, status, last_verified, sql_text::varchar(40) FROM apg_plan_mgmt.dba_plans ORDER BY last_verified DESC; -- value updated by evolve_plan_baselines()

計画の検証の詳細については、「計画パフォーマンスの評価」を参照してください。

計画の削除

計画が実行されていない場合、または計画保持期間の最小コスト計画として選択されていない場合、オプティマイザは自動的に計画を削除します。デフォルトでは、計画保持期間は 32 日です。計画保持期間を変更するには、apg_plan_mgmt.plan_retention_period パラメータを参照してください。

また、apg_plan_mgmt.dba_plans 関数を使用して apg_plan_mgmt.delete_plan ビューの内容を確認し、不要な計画を削除することができます。詳細については、「計画の削除」を参照してください。