Aurora PostgreSQL クエリ計画管理の概要

すべての AWS サービスと同様に、Amazon Aurora PostgreSQL のロードマップは、主にお客様のご意見と製品強化のご要望によって推進されます。Oracle と Microsoft SQL Server から Amazon Aurora にデータベースを移行した、複数の企業お客様からいただいたフィードバックは、2 つの点を示唆しています。重要なアプリケーションのデータベースワークロードを実行する企業は、最適なデータベースパフォーマンスを必要とします。また、企業には Aurora PostgreSQL と互換性のあるデータベースでさまざまなシステム変更を行っても、安定かつ一貫性を保つパフォーマンスが必要です。

PostgreSQL データベースのパフォーマンスが変動する主な原因の 1 つはチェックポイントプロセスにあります。多くの場合、パフォーマンスと回復性の間のトレードオフのことです。Aurora PostgreSQL では、データベースのチェックポイントを排除することでこの問題に対処してきました。ログ記録とストレージレイヤーを切り離すために、ログベースのストレージサブシステムを実装します。応答時間が変動するもう 1 つの主な原因は、クエリ計画の不安定性によるものです。クエリ実行計画を予期せずに変更する、さまざまな要因があります。以下に例を挙げます。

オプティマイザ統計の変更 (手動または自動)
クエリ計画設定のパラメータに対する変更
新しいインデックスの追加など、スキーマに対する変更
クエリで使用されるバインド変数に対する変更
PostgreSQL データベースバージョンへのマイナーバージョンまたはメジャーバージョンのアップグレード。PostgreSQL 9.6.x から 10.x など

クエリ計画管理

Aurora PostgreSQL クエリ計画管理 (QPM) 機能は、データベースユーザーが一連の管理 SQL ステートメントに対して安定かつ最適なパフォーマンスを維持できるようにすることで、計画不安定性の問題を解決します。QPM では主に 2 つの目的を果たすことができます。

計画安定性。システムに上記の変更が発生した場合、QPM は計画の回帰が生じないようにして、計画安定性を向上させます。
計画適応性。QPM は、新しい最小費用計画を自動的に検出し、新しい計画が使用されるときに制御し、その変更に適応します。

QPM の仕組み

以下のフローチャートは、QPM の仕組みを表しています。フローチャートのステップ 1 から 9 は、フローチャート図の下に説明されています。詳細については、Amazon Aurora のドキュメントから QPM の基本情報を参照してください。

オプティマイザが SQL ステートメントを処理すると、最小費用計画が生成されます。
QPM が有効になっていない場合、オプティマイザは簡単にこの最小費用計画を実行します。QPM が有効になっている場合、オプティマイザはクエリ計画管理を使用します。
オプティマイザが、SQL ステートメントが管理ステートメントではないと判断した場合は、生成された最小費用計画が実行されます。
手動取得または自動取得が有効になっている場合、オプティマイザが取得計画の処理を行います。
QPM の use_plan_baselines オプションが有効になっていない場合、オプティマイザは最小費用計画を実行します。
オプティマイザが生成した計画が保存された計画ではない場合、オプティマイザはその計画を新しい未承認計画として取得して保存します。
計画が却下または無効ではない場合、オプティマイザは生成された計画を実行用に選択します。必要に応じて、推定コストが指定したしきい値を下回る計画を簡単に実行するように QPM を設定できます。これにより、簡易計画に対する計画の強制オーバーヘッドを回避できます。
管理ステートメントに有効で使用可能な優先計画がある場合、オプティマイザは最小費用計画を実行します。1 つ以上の優先計画が存在する場合、オプティマイザは各優先計画の費用を再計算し、再作成可能な最小費用の優先計画を選択します。
オプティマイザは、承認および有効化された実施の最小費用計画を選択します。優先計画が存在しない場合、または再作成できる場合は、オプティマイザは各承認計画の費用を再計算し、最小費用の承認計画を選択します。優先計画または承認計画が存在しない場合、あるいは再作成可能な場合は、最後の手段としてオプティマイザの最小費用計画が使用されます。

自動取得で QPM を使用するためのクイックスタートガイド

クエリ計画管理は、Amazon Aurora PostgreSQL バージョン 10.5 互換 (Aurora 2.1.0) 以降、または Amazon Aurora PostgreSQL バージョン 9.6.11 互換 (Aurora 1.4.0) 以降でご利用いただけます。最も簡単に QPM を有効にするには、自動計画取得を使用します。これにより、2 回以上実行されるすべての SQL ステートメントの計画取得が有効になります。

自動取得および QPM での管理計画使用のために、Aurora PostgreSQL クラスターで QPM の使用を設定して有効にする手順は次のとおりです。

QPM に関連する Amazon Aurora DB クラスターと DB インスタンスパラメータを変更します。
a.https://console.thinkwithwp.com/rds/ で Amazon RDS コンソールを開きます。
b.クエリ計画管理に使用する新しいインスタンスレベルのパラメータグループを作成します。詳細については、DB パラメータグループの作成を参照してください。
c.クエリ計画管理に使用する新しいクラスターレベルのパラメータグループを作成します。詳細については、DB クラスターパラメータグループの作成を参照してください。
d.クラスターレベルのパラメータグループを開き、rds.enable_plan_management パラメータを 1 に設定します。詳細については、DB クラスターパラメータグループのパラメータ変更を参照してください。

e.データベースレベルのパラメータグループを開き、apg_plan_mgmt.capture_plan_baselines パラメータを automatic に、apg_plan_mgmt.use_plan_baselines を true に設定します。詳細については、DB パラメータグループのパラメータ変更を参照してください。これらのパラメータは、クラスターレベルまたはデータベースレベルで設定できます。デフォルトの推奨事項は、Aurora クラスターレベルで設定することです。

f.新しい設定を有効にするには、DB インスタンスを再起動してください。
g.psql などの SQL クライアントを使用して DB インスタンスに接続します。詳細については、psql を使用した PostgreSQL DB インスタンスへの接続を参照してください。
h.クラスターに対して Aurora PostgreSQL と互換性のあるバージョンをクエリを行います。
```
pg105db=> select aurora_version(),version();
 aurora_version |                                   version                                   
----------------+-----------------------------------------------------
2.1.0	         | PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
```

DB インスタンスの apg_plan_mgmt 拡張機能を作成して確認します。
a.DB インスタンス用の apg_plan_mgmt 拡張機能を作成します。

pg105db=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION

pg105db=> pg105db=> select extname,extversion from pg_extension where extname='apg_plan_mgmt';
    extname    | extversion 
---------------+------------
 apg_plan_mgmt | 1.0.1

b.すべての QPM 関連パラメータの値が適切に変更されていることを確認するためにクエリを行います。

pg105db=> show rds.enable_plan_management;
 rds.enable_plan_management 
----------------------------
 1

pg105db=> show apg_plan_mgmt.capture_plan_baselines;
 apg_plan_mgmt.capture_plan_baselines 
--------------------------------------
 automatic

pg105db=> show apg_plan_mgmt.use_plan_baselines;
 apg_plan_mgmt.use_plan_baselines 
----------------------------------
 on

自動取得で合成ワークロードを実行します。
a.pgbench (PostgreSQL ベンチマークツール) を使用して、指定された期間中に同じクエリを実行する、シミュレートされたワークロードを生成します。自動取得を有効にすると、QPM は 2 回以上実行される各クエリの計画を取得します。以下はその例です。

pgbench]$ ./pgbench --host=apg105.xxxxxxxxxxxx.us-west-2.rds.amazonaws.com --username=xxxx --protocol=prepared -P 60 --time=100 --client=16 --jobs=96 pg105db> results1.lo

b.apg_plan_mgmt.dba_plans テーブルのクエリを行い、pgbench ツールで開始された管理ステートメントと SQL ステートメントの説明計画を表示します。

pg105db=> SELECT sql_hash,
       plan_hash,
       status,
       enabled,
       sql_text 
FROM   apg_plan_mgmt.dba_plans;

  sql_hash   |  plan_hash  |   status   | enabled |                                               sql_text                                                
-1677381765	-225188843	 Approved   	 t       	UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
-60114982	300482084	 Approved   	 t       	 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP);
1319555216	30042398	 Approved   	 t       	select count(*) from pgbench_branches;

-2033469270	-1987991358	 Approved   	 t       	 UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;

c.自動取得を無効にします。

自動取得を使用してすべての計画を取得すると、ランタイムでのオーバーヘッドがほとんどなく、本番環境で有効にできます。pgbench ワークロードの範囲外で SQL ステートメントを取得しないように、自動取得をオフにしています。これは、DB インスタンスレベルのパラメータグループから apg_plan_mgmt.capture_plan_baselines パラメータを off に設定するとオフにできます。

pg105db=> show apg_plan_mgmt.capture_plan_baselines;
 apg_plan_mgmt.capture_plan_baselines 
--------------------------------------
 Off

d.管理ステートメントの実行計画が QPM によって取得された計画であることを確認してください。

管理ステートメントの 1 つに対して説明計画を手動で実行しました (上記にある黄色の強調表示)。説明計画の出力には、SQL ハッシュと、そのステートメントに対して QPM が承認した計画と一致する計画ハッシュが表示されます。

pg105db=> explain (hashes true)  
UPDATE pgbench_tellers
SET    tbalance = tbalance + 100
WHERE  tid = 200; 
                         QUERY PLAN                                             
----------------------------------------------------------------------
 Update on pgbench_tellers  (cost=0.14..8.16 rows=1 width=358)
   ->  Index Scan using pgbench_tellers_pkey on pgbench_tellers  (cost=0.14..8.16 rows=1 width=358)
         Index Cond: (tid = 200)
 SQL Hash: -2033469270, Plan Hash: -1987991358

自動計画取得に加えて、QPM は手動取得も提供します。すなわち、既知の問題のあるクエリの実行計画を取得するメカニズムを提供します。通常、計画を自動的に取得することをお勧めします。ただし、次のように、手動で計画を取得することが最善となる場合があります。

データベースレベルで計画管理を有効にしたいとは思わなくても、いくつかの重要な SQL ステートメントのみを制御したいとは思われるでしょう。
パフォーマンス問題を引き起こしている特定のリテラル値またはパラメータ値セットの計画を保存したいとします。

計画の進化メカニズムによる QPM 計画適応性

オプティマイザが生成した計画が保存された計画ではない場合、オプティマイザはその計画を新しい未承認計画として取得して保存し、QPM 管理の SQL ステートメントに対する安定性を維持します。

クエリ計画管理は、実行計画を追加、維持、および改善するための技法と機能を提供し、したがって計画適応性を向上させます。ユーザーは、必要に応じてまたは定期的に、保存されているすべての計画を進化させて、承認計画よりも優れた最小費用計画があるかどうかを QPM に確認するように指示できます。

QPM は apg_plan_mgmt.evolve_plan_baselines 関数を提供し、実際のパフォーマンスに基づいて計画を比較します。パフォーマンス実験の結果に応じて、計画のステータスを未承認から承認または却下のいずれかに変更できます。計画が要件を満たしていない場合は、代わりに apg_plan_mgmt.evolve_plan_baselines 関数を使用して計画を一時的に無効にすることができます。

QPM 計画の進化に関する詳細については、計画パフォーマンスの評価を参照してください。QPM 計画の進化に関する詳細な例については、Amazon Aurora PostgreSQL クエリ計画管理のユースケースを参照してください。

まとめ

これまで、Aurora PostgreSQL クエリ計画管理 (QPM) の簡単な紹介と QPM 自動取得のクイックスタートガイドについて紹介しました。QPM は、計画安定性、適応性、および一貫した最適なデータベースパフォーマンスを提供することによって、データベースのパフォーマンスを維持します。実行計画やパフォーマンスに問題が生じるような変更があっても、これらの機能には問題ありません。

高度なユースケースについては、このシリーズの 2 回目のブログ記事 Amazon Aurora PostgreSQL でのクエリ計画管理のユースケースを参照してください。QPM の詳細については、Aurora PostgreSQL のクエリ実行計画の管理を参照してください。

著者について

Sameer Malik は、アマゾンウェブサービスのプリンシパルデータベースソリューションアーキテクトです。

Jim Finnerty は、アマゾンウェブサービスの Aurora PostgreSQL のプリンシパルソフトウェアエンジニアです。

Amazon Web Services ブログ