Amazon Web Services ブログ

Amazon Aurora PostgreSQL でのクエリ計画管理のユースケース

このブログの投稿は一連の投稿の 2 回目です。前回のブログ記事では、SQL ステートメントの実行計画に回帰を引き起こす可能性があるその他の変更の中で、安定かつ一貫したデータベースパフォーマンスの必要性について説明しました。また、PostgreSQL と互換性のある Amazon Aurora のクエリ計画管理 (QPM) が、計画安定性と計画適応性の問題を克服できるようにする方法も述べています。

この記事では、引き続き Aurora PostgreSQL QPM の機能について説明します。特に、これらの機能によって、さまざまな高度なユースケースに対して計画安定性と適応性を実現する方法についてお話します。

ユースケース #1: QPM 手動取得による計画安定性と適応性の支援

最初のユースケースでは、QPM が計画安定性を確保する方法について例を挙げて説明します。次に、前回の記事 Aurora PostgreSQL クエリ計画管理の概要で説明した変更を行います。QPM を使用しない場合は、これらの変更により計画の回帰が生じる可能性があります。

ほとんどの場合、自動計画取り込みを使用するように QPM を設定して、2 回以上実行されるすべてのステートメントを取得することもできます。ただし、手動で指定した特定のステートメントセットの計画を取得することもできます。そのためには、デフォルトに capture_plan_baselines = off を設定します。セッションレベルでは、capture_plan_baselines = manual を設定します。設定の仕方については後で説明します。

  1. 手動計画取り込みを有効にして、目的の SQL ステートメントの実行計画を手動で取得するように QPM に指示します。 
    pg105db=> SET apg_plan_mgmt.capture_plan_baselines = manual;
SET
    SQL
  2. QPM がクエリ計画を取得できるように、クエリ説明計画を実行します (説明計画の以下の出力は、簡潔にするために省略されています)。 
    pg105db=> explain (hashes true) 
SELECT Sum(delta),
       	    Sum(bbalance)
FROM   pgbench_history h,
       	   pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx); 
                      QUERY PLAN                                                  
----------------------------------------------------------------------
 Aggregate  (cost=23228.13..23228.14 rows=1 width=16)
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mn  (cost=0.00..5346.11 rows=247911 width=8)
   InitPlan 2 (returns $3)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mx  (cost=0.00..5346.11 rows=247911 width=8)
   ->  Nested Loop  (cost=0.00..9292.95 rows=632 width=8)
         Join Filter: (h.bid = b.bid)
         ->  Seq Scan on pgbench_history h  (cost=0.00..9188.74 rows=2107 width=8)
               Filter: ((mtime >= $1) AND (mtime <= $3))
         ->  Materialize  (cost=0.00..14.15 rows=3 width=8)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..14.14 rows=3 width=8)
                     Filter: (bid = ANY ('{1,2,3}'::integer[]))
………………………………………………………………………..
SQL Hash: 1561242727, Plan Hash: -1990695905
    SQL
  3. 目的の SQL ステートメントの実行計画を取得した後で、計画の手動取得を無効にします。 
    pg105db=> SET apg_plan_mgmt.capture_plan_baselines = off;
SET
    SQL
  4. 以前実行されたクエリで取得したクエリ計画を表示します。テーブル apg_plan_mgmt.dba_plans の plan_outline 列には、SQL の全体計画が表示されます。簡潔にするため、plan_outline はここには示されません。代わりに、前述の説明計画の plan_hash_value が apg_plan_mgmt.dba_plans クエリ出力の plan_hash と比較されます。 
    pg105db=> SELECT sql_hash,
       		 plan_hash,
       		status,
       		estimated_total_cost "cost",
       		sql_text
FROM	apg_plan_mgmt.dba_plans; 

 sql_hash     |  plan_hash  |  status  | cost 	 |  sql_text                                                                                                         
                                                                      
--------------+-------------+----------+---------+-----------------------------------------------------------
 
1561242727	  -1990695905	 Approved 	 23228.14    select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx);
    SQL
  5. 管理ステートメントに承認取得計画または優先取得計画を使用するようにクエリオプティマイザに指示するには、パラメータ apg_plan_mgmt.use_plan_baselines を true に設定します。 
    pg105db=> SET apg_plan_mgmt.use_plan_baselines = true;
SET
    SQL
  6. 説明計画の出力を表示して、QPM 承認計画がクエリオプティマイザによって使用されていることを確認します。 
    pg105db=> explain (hashes true) 
SELECT Sum(delta),
       	    Sum(bbalance)
FROM   pgbench_history h,
       	   pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx); 

                      QUERY PLAN                                                  
----------------------------------------------------------------------
 Aggregate  (cost=23228.13..23228.14 rows=1 width=16)
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mn  (cost=0.00..5346.11 rows=247911 width=8)
   InitPlan 2 (returns $3)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mx  (cost=0.00..5346.11 rows=247911 width=8)
   ->  Nested Loop  (cost=0.00..9292.95 rows=632 width=8)
         Join Filter: (h.bid = b.bid)
         ->  Seq Scan on pgbench_history h  (cost=0.00..9188.74 rows=2107 width=8)
               Filter: ((mtime >= $1) AND (mtime <= $3))
         ->  Materialize  (cost=0.00..14.15 rows=3 width=8)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..14.14 rows=3 width=8)
                     Filter: (bid = ANY ('{1,2,3}'::integer[]))
………………………………………………………………………..
SQL Hash: 1561242727, Plan Hash: -1990695905
    SQL
  7. 計画設定を変更し、クエリオプティマイザに新しい計画を生成するために、pgbench_history テーブルの「mtime」列に新しいインデックスを作成します。 
    pg105db=> create index pgbench_hist_mtime on pgbench_history(mtime);
CREATE INDEX
    SQL
  8. 説明計画の出力を表示して、QPM が新しい計画を検出した場合でも、承認計画を使用し計画安定性を維持していることを確認します。 
    pg105db=> explain (hashes true) 
SELECT Sum(delta),
       	    Sum(bbalance)
FROM   pgbench_history h,
       	   pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx); 

                      QUERY PLAN                                                  
Aggregate  (cost=23228.13..23228.14 rows=1 width=16)
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mn  (cost=0.00..5346.11 rows=247911 width=8)
   InitPlan 2 (returns $3)
     ->  Finalize Aggregate  (cost=6966.00..6966.01 rows=1 width=8)
           ->  Gather  (cost=6965.89..6966.00 rows=1 width=8)
                 Workers Planned: 1
                 ->  Partial Aggregate  (cost=5965.89..5965.90 rows=1 width=8)
                       ->  Parallel Seq Scan on pgbench_history mx  (cost=0.00..5346.11 rows=247911 width=8)
   ->  Nested Loop  (cost=0.00..9292.95 rows=632 width=8)
         Join Filter: (h.bid = b.bid)
         ->  Seq Scan on pgbench_history h  (cost=0.00..9188.74 rows=2107 width=8)
               Filter: ((mtime >= $1) AND (mtime <= $3))
         ->  Materialize  (cost=0.00..14.15 rows=3 width=8)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..14.14 rows=3 width=8)
                     Filter: (bid = ANY ('{1,2,3}'::integer[]))

………………………………………………………………………..

注意: この例では、最小費用計画の代わりに承認計画を使用しました。
SQL Hash: 1561242727, Plan Hash: -1990695905, Minimum Cost Plan Hash: -794604077
    SQL
  9. 以下の SQL クエリを実行して、新しい計画と計画のステータスを表示します。計画安定性を確保するために、QPM は管理クエリに対して新しく生成されたすべての計画を未承認計画として QPM に保存します。次の出力は、2 つの異なる plan_hash 値によって示されるように、同じ管理ステートメントに対して 2 つの異なる説明計画が保存されていることを表します。新しい実行計画は費用を最小限に (承認計画よりも低く) 抑えられますが、QPM は計画安定性を維持するために未承認計画を無視し続けます。テーブル apg_plan_mgmt.dba_plans の plan_outline 列には、SQL の全体計画が表示されます。簡潔にするために、plan_outline はここには示されません。代わりに、前述の説明計画の plan_hash_value が apg_plan_mgmt.dba_plans クエリ出力の plan_hash と比較されます。 
    pg105db=> SELECT sql_hash,
       		 plan_hash,
       		status,
       		estimated_total_cost "cost",
       		sql_text
FROM	apg_plan_mgmt.dba_plans; 

  sql_hash 	|  plan_hash  |  status  | cost       |  sql_text                                                                                                         
                                                                      
------------+-------------+----------+------------+-----------------------------------------------------------
 
1561242727	-1990695905		Approved	23228.14   	 select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx); 
1561242727	-794604077	 	UnApproved 	 111.17    	 select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx);
    SQL

    以下は、QPM による計画適応性の例です。この例では、最小速度向上係数に基づいて未承認計画を評価します。ステートメントに対して最も優れた承認計画よりも 10% 以上速い、取得済みの未承認計画を承認します。詳細については、Aurora のドキュメント計画パフォーマンスの評価を参照してください。

    pg105db=> SELECT apg_plan_mgmt.Evolve_plan_baselines (sql_hash, plan_hash, 1.1,'approve')
FROM   apg_plan_mgmt.dba_plans
WHERE  status = 'Unapproved'; 

NOTICE: [Unapproved] SQL Hash: 1561242727, Plan Hash: 1944377599, SELECT sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where ...
NOTICE:      Baseline   [Planning time 0.693 ms, Execution time 316.644 ms]
NOTICE:      Baseline+1 [Planning time 0.695 ms, Execution time 213.919 ms]
NOTICE:      Total time benefit: 102.723 ms, Execution time benefit: 102.725 ms, Avg Log Cardinality Error: 3.53418, Cost = 111.16..111.17
NOTICE:      Unapproved -> Approved
    SQL

    QPM が速度係数に基づいて計画を評価した後、計画のステータスが承認済みに変わります。この時点で、オプティマイザは管理ステートメントに計画をすぐ選択できるようになります。

    pg105db=> SELECT sql_hash,
       		 plan_hash,
       		status,
       		estimated_total_cost "cost",
       		sql_text
FROM	apg_plan_mgmt.dba_plans; 

  sql_hash  |  plan_hash   |  status   | cost 	     |  sql_text                                                                                                         
                                                                      
------------+-------------+------------+-------------+-----------------------------------------------------------
 
1561242727	 -1990695905	 Approved 	 23228.14     	 select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx); 
1561242727	 -794604077		 Approved 	 111.17     	 select sum(delta),sum(bbalance) from pgbench_history h, pgbench_branches b where b.bid=h.bid and b.bid in (1,2,3) and mtime between (select min(mtime) from pgbench_history mn) and (select max(mtime) from pgbench_history mx);
    SQL

  10. 説明計画の出力を表示して、クエリが新しく承認された最小費用計画を使用しているかどうかを確認します。

    pg105db=> explain (hashes true) 
SELECT Sum(delta),
       	    Sum(bbalance)
FROM   pgbench_history h,
       	   pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx); 

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=111.16..111.17 rows=1 width=16)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.46..0.47 rows=1 width=8)
           InitPlan 1 (returns $0)
             ->  Limit  (cost=0.42..0.46 rows=1 width=8)
                   ->  Index Only Scan using pgbench_hist_mtime on pgbench_history mn  (cost=0.42..14882.41 rows=421449 width=8)
                         Index Cond: (mtime IS NOT NULL)
   InitPlan 4 (returns $3)
     ->  Result  (cost=0.46..0.47 rows=1 width=8)
           InitPlan 3 (returns $2)
             ->  Limit  (cost=0.42..0.46 rows=1 width=8)
                   ->  Index Only Scan Backward using pgbench_hist_mtime on pgbench_history mx  (cost=0.42..14882.41 rows=421449 width=8)
                         Index Cond: (mtime IS NOT NULL)
   ->  Hash Join  (cost=14.60..107.06 rows=632 width=8)
         Hash Cond: (h.bid = b.bid)
         ->  Index Scan using pgbench_hist_mtime on pgbench_history h  (cost=0.42..85.01 rows=2107 width=8)
               Index Cond: ((mtime >= $1) AND (mtime <= $3))
         ->  Hash  (cost=14.14..14.14 rows=3 width=8)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..14.14 rows=3 width=8)
                     Filter: (bid = ANY ('{1,2,3}'::integer[]))

   
 SQL Hash: 1561242727, Plan Hash: -794604077
    SQL

     

ユースケース #2。pg_hint_plan を使用して QPM で計画を修正

場合によっては、クエリオプティマイザがクエリに最適な実行計画を生成しないことがあります。この問題を解決するアプローチの 1 つはアプリケーションコードにクエリのヒントを含めることですが、このアプローチはアプリケーションを脆弱にし、保守を難しくするため、広くお勧めされていません。一部の場合、サードパーティー製のアプリケーションによって生成されているため、SQL のヒントを得られないことがあります。ここでは、ヒントを使用してクエリオプティマイザを制御する方法を示しています。ただしこれは、アプリケーションコードにヒントを追加することなく、ヒントを削除して QPM が目的の計画を実行できるようにする方法です。

このために、PostgreSQL ユーザーは pg_hint_plan 拡張機能を使用して、「scan method」、「join method」、「join order」、「row number correction」などのディレクティブをオプティマイザに提供することができます。  結果として生じる計画は、QPM によって、上書きを選択した GUC パラメータ (work_mem など) とともに保存されます。  QPM は GUC パラメータの上書きを記憶し、計画を再作成するときにそれらを使用します。pg_hint_plan 拡張機能のインストール方法と使い方の詳細については、pg_hint_plan のドキュメントを参照してください。

ヒントを使用して生成した計画を修正するための QPM 手順

pg_hint_plan を使用した操作は、ヒントを追加するためにクエリを修正できない場合に非常に便利です。この例では、サンプルのクエリを使用して、管理ステートメントにヒントを追加し、必要な実行計画を生成します。次に、この実行計画を元の未修正ステートメントに関連付けます。

手順の詳細は以下のとおりです。

  1. 計画の取り込みが無効になっていないか確認してください。 
    pg105db=> show apg_plan_mgmt.capture_plan_baselines;
 apg_plan_mgmt.capture_plan_baselines 
--------------------------------------
 off
(1 row)
    SQL
  2. 使用するヒントでクエリを実行します。次の例では、「HashJoin」ヒントを使用します。これは、オプティマイザが HashJoin で join メソッドを選択するためのディレクティブです。ヒントがないクエリの元の計画は次のとおりです。 
    pg105db=> EXPLAIN (hashes true)
SELECT
   * 
FROM
   pgbench_branches b 
   JOIN
      pgbench_accounts a 
      ON b.bid = a.bid 
ORDER BY
   a.aid;                                                   

                                QUERY PLAN                                                    
----------------------------------------------------------------------
 Nested Loop  (cost=0.42..181906.82 rows=1000000 width=465)
   Join Filter: (b.bid = a.bid)
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts a  (cost=0.42..44165.43 rows=1000000 width=97)
   ->  Materialize  (cost=0.00..14.15 rows=10 width=364)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..14.10 rows=10 width=364)
 SQL Hash: 356104612, Plan Hash: -451962956
    SQL

    入れ子のループに必要な計画は次のとおりです。

    pg105db=> EXPLAIN (hashes true)
SELECT
   * 
FROM
   pgbench_branches b 
   JOIN
      pgbench_accounts a 
      ON b.bid = a.bid 
ORDER BY
   a.aid;                                                   

                                QUERY PLAN                                                    
----------------------------------------------------------------------
 Nested Loop  (cost=0.42..181906.82 rows=1000000 width=465)
   Join Filter: (b.bid = a.bid)
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts a  (cost=0.42..44165.43 rows=1000000 width=97)
   ->  Materialize  (cost=0.00..14.15 rows=10 width=364)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..14.10 rows=10 width=364)
 SQL Hash: 356104612, Plan Hash: -451962956
    SQL
  3. pg_hint_plan と手動計画取り込みを有効にします。

    Pg105db=> SET pg_hint_plan.enable_hint = true;
SET
pg105db=> SET apg_plan_mgmt.capture_plan_baselines = manual;
SET
    SQL
  4. 使用したいヒントでクエリを実行します。次の例では、HashJoin (a、b) ヒントを使用します。これは、オプティマイザがハッシュ結合アルゴリズムを使用してテーブル a からテーブル b に結合するためのディレクティブです。ハッシュ結合で必要な計画は次のとおりです。 
    pg105db=> /*+ HashJoin(a b) */  EXPLAIN (hashes true)
SELECT
   * 
FROM
   pgbench_branches b 
   JOIN
      pgbench_accounts a 
      ON b.bid = a.bid 
ORDER BY
   a.aid;

                            QUERY PLAN                                               
----------------------------------------------------------------------
 Gather Merge  (cost=240409.02..250138.04 rows=833334 width=465)
   Workers Planned: 2
   ->  Sort  (cost=239409.00..240450.67 rows=416667 width=465)
         Sort Key: a.aid
         ->  Hash Join  (cost=14.22..23920.19 rows=416667 width=465)
               Hash Cond: (a.bid = b.bid)
               ->  Parallel Seq Scan on pgbench_accounts a  (cost=0.00..22348.67 rows=416667 width=97)
               ->  Hash  (cost=14.10..14.10 rows=10 width=364)
                     ->  Seq Scan on pgbench_branches b  (cost=0.00..14.10 rows=10 width=364)
 SQL Hash: 356104612, Plan Hash: 1139293728
    SQL
  5. 計画 1139293728 が取得されたことを確認して、計画のステータスを書き留めます。取得した計画と計画のステータスを表示します。 
    pg105db=> 
SELECT sql_hash,
       plan_hash,
       status,
       enabled,
       sql_text
FROM   apg_plan_mgmt.dba_plans
 Where plan_hash=1139293728;

  sql_hash  | plan_hash  |  status  | enabled |         sql_text          
-----------+------------+----------+---------+---------------------------
 356104612 | 1139293728 | Approved | t       | SELECT                   +
           |            |          |         |    *                     +
           |            |          |         | FROM                     +
           |            |          |         |    pgbench_branches b    +
           |            |          |         |    JOIN                  +
           |            |          |         |       pgbench_accounts a +
           |            |          |         |       ON b.bid = a.bid   +
           |            |          |         | ORDER BY                 +
           |            |          |         |    a.aid;
    SQL
  6. 必要に応じて、計画を承認します。この場合、ステートメント 356104612 用に保存された最初で唯一の計画であるため、承認計画として保存されました。  このステートメントにすでに承認計画のベースラインがある場合、この計画は未承認計画として保存されています。一般的に、ステートメントの既存の計画をすべて却下してから特定の計画を 1 つ承認するために、次のように apg_plan_mgmt.set_plan_status を 2 回呼び出すことができます。 
    pg105db=> SELECT apg_plan_mgmt.set_plan_status (sql_hash, plan_hash, 'Rejected') from apg_plan_mgmt.dba_plans where sql_hash = 356104612;
SET
pg105db=> SELECT apg_plan_mgmt.set_plan_status (356104612, 1139293728, 'Approved');
SET
    SQL
  7. ヒントを削除し、手動取得をオフにし、use_plan_baselines をオンにして、目的の計画がヒントなしで使用中であることを確認します。 
    pg105db=> SET apg_plan_mgmt.capture_plan_baselines = off;
SET
pg105db=> SET apg_plan_mgmt.use_plan_baselines = true;
SET
pg105db=> EXPLAIN (hashes true)
SELECT
   * 
FROM
   pgbench_branches b 
   JOIN
      pgbench_accounts a 
      ON b.bid = a.bid 
ORDER BY
   a.aid;             
                             QUERY PLAN                                               
----------------------------------------------------------------------
 Gather Merge  (cost=240409.02..337638.11 rows=833334 width=465)
   Workers Planned: 2
   ->  Sort  (cost=239409.00..240450.67 rows=416667 width=465)
         Sort Key: a.aid
         ->  Hash Join  (cost=14.22..23920.19 rows=416667 width=465)
               Hash Cond: (a.bid = b.bid)
               ->  Parallel Seq Scan on pgbench_accounts a  (cost=0.00..22348.67 rows=416667 width=97)
               ->  Hash  (cost=14.10..14.10 rows=10 width=364)
                     ->  Seq Scan on pgbench_branches b  (cost=0.00..14.10 rows=10 width=364)
 注意: 最小費用計画の代わりに承認計画を使用しました。
 SQL Hash: 356104612, Plan Hash: 1139293728, Minimum Cost Plan Hash: -451962956
    SQL

ユースケース 3: QPM 管理計画のエクスポートとインポートを使用して QPM 管理計画をグローバルにデプロイ

大企業のお客様は、多くの場合、アプリケーションとデータベースをグローバルにデプロイしています。また、アプリケーションデータベースごとに複数の環境 (Dev、QA、Staging、UAT、Preprod、および Prod) を維持しています。ただし、特定の AWS リージョン内の各データベースと各データベース環境で実行計画を手動で管理すると、面倒で時間がかかる場合があります。

QPM は、QPM 管理計画をあるデータベースから別のデータベースにエクスポートおよびインポートするためのオプションを備えています。このオプションを使用すると、クエリの実行を一元的に管理し、データベースをグローバルにデプロイできます。この機能は、preprod データベースで一連の計画を調査し、計画が適切に機能していることを確認してから、本稼働データベースに読み込むシナリオに役立ちます。

QPM 管理計画をあるデータベースから別のデータベースに移行する手順は、次のとおりです。詳細については、Aurora のドキュメントエクスポートおよびインポート計画を参照してください。

  1. ソースシステムから QPM 管理計画をエクスポートします。
    このために、承認された DB ユーザーは 優先実行計画があるソースデータベースから、apg_plan_mgmt.plans テーブルの任意のサブセットを別のテーブルにコピーできます。そのユーザーは pg_dump コマンドを使用して、コピーしたサブセットを保存することができます。pg_dump の詳細については、PostgreSQL のドキュメントから pg_dump を参照してください。
  2. ターゲットシステムに QPM 管理計画をインポートします。
  3. ターゲットシステムで、apg_plan_mgmt.plans テーブルの任意のサブセットを別のテーブルにコピーしてから、pg_dump コマンドを使用してそれを保存します。これは、ソースシステムの新しい計画をインポートする前に、ターゲットシステムの既存の管理計画を保存するオプションの手順です。
  4. ステップ 1 で pg_dump tar 形式のアーカイブを使用したことを前提としています。pg_restore コマンドを使用して、.tar ファイルを新しいテーブル (plan_copy) にコピーします。pg_restore の詳細については、PostgreSQL のドキュメントから pg_restore を参照してください。
  5. 新しいテーブルを apg_plan_mgmt.plans テーブルとマージします。
  6. 管理計画を共有メモリに再度読み込み、一時計画テーブルを削除します。
    SELECT apg_plan_mgmt.reload(); — 新しい計画で共有メモリを更新します。DROP TABLE plans_copy; -- 一時計画テーブルを削除します。

まとめ

この記事では、Aurora PostgreSQL QPM の高度なユースケースについて説明しました。その 1 つが、QPM と pg_hint_plan の統合方法です。これにより、オプティマイザがヒントを使用して最適な実行計画を生成することができます。オプティマイザは、変更できない SQL ステートメント (商用の市販アプリケーションの一部であるステートメントなど) に対しても最適な実行計画を生成できます。

その他のユースケースには、QPM のエクスポートおよびインポート機能が含まれます。これにより、実行計画を一元管理し、グローバルにデプロイできます。集中管理とグローバルなデプロイにより、複数の環境と AWS リージョンにまたがる複数のデータベースに渡って既知の実行計画をすばやくデプロイできます。

これまで QPM の重要なユースケースをいくつか取り上げました。さらに、QPM を使用して、持続可能で最適なデータベースパフォーマンスを達成するためにも同様に重要な他のユースケースを実装できます。QPM の詳細については、Aurora のドキュメントから Aurora PostgreSQL のクエリ実行計画の管理を参照してください。

 

著者について

 

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

 

 

 

 

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