17.00 - 17.05 - 戦術的問合わせのロックの問題 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

戦術的問合わせによって発生する特別なロックの問題

戦術的な問合わせは、可能な時にはいつでも行ハッシュ レベルのロックを使用して、応答時間およびスループットを向上させます。行ハッシュ レベルのロックが望ましい理由は次のとおりです。
  • 並列性が高い

    ロックが1行または数行だけに設定されるなら、同時に他のユーザーが同じテーブルのその他の行にアクセスしたり、これを更新することができます。

  • 必要なリソースがより少ない

    行ハッシュ レベルのロックでは、拘束されるのが単一のAMPだけなので、適用するリソースが少なくて済みます。テーブル レベルのロックを適用する場合、処理はシステム内のすべてのAMPに渡って実行および調整されなければなりません。テーブル レベルのロックを保守するには、2つの別個の全AMPステップを必ず実行しなければなりません。最初のステップはロックの設定、2番目のステップはその解放です。

  • スケーラビリティが高い

    最低限のリソースでリクエストを満たすように整理すると、同様のリクエストをより多く並列処理できます。並行ユーザー数が大きくなることと、構成にさらに多くのノードを追加することの両方によって、スループットは増大します。

混合戦術的および意思決定支援の問合わせワークロードの処理を促進するデータベースの設計方法については、<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。

戦術的問合わせのグループAMPロックに関する考慮事項

グループAMP操作では、問合わせに関係する行ごとに1つずつ、行ハッシュ ロックを使用します。グループAMP操作の詳細については、<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。

グループAMP処理の主要なメリットの1つは、テーブル レベルのロックを適用する必要性がかなり少なくなるということです。テーブル レベルのロックは行ハッシュ レベルのロックに比べてレベルが高いため、テーブル レベルのロックを使用するとデータベース オブジェクトの競合が発生しやすくなり、そのためにその他の並列実行操作の実行が遅くなったり、さらには実行が妨げられたりする可能性が高くなります。また、最適化ルーチンはテーブル レベルのロックを別個のステップで適用し、テーブル レベルのロックを取得および適用するそのステップに、システム内のすべてのAMPが含められます。

パフォーマンスにマイナスの影響を与えるこれらの操作とは対照的に、グループAMP操作では、設定されるロックのレベルが低いだけでなく、設定される場所が少なくて済むため、読取りにおいても更新においても並列処理のための条件が改善されます。構成に含まれるAMPの数が多いほど、グループAMP操作によって得られるパフォーマンス上のメリットが大きくなり、また、最適化ルーチンがグループAMPに基づく問合わせステップを指定する可能性が高くなります。

ACCESSロックと戦術的問合わせ

同一のテーブルに対して一群のリクエストが更新を実行し、別の一群が読取りを実行する場合、ACCESSロックを使用するとスループットが向上します。ACCESSロックを使用すると、すでにWRITEロックまたはREADロックされているかもしれないオブジェクトに対する読取りアクセスが許可されます。



ACCESSロックを使用すると、アクセスされるデータのビューに一貫性がなくなる危険があります。更新処理中のデータが、一貫性のあるものとしてリクエスト側に返される可能性があります。

戦術的問合わせの行ハッシュ レベルまたはテーブル レベルのACCESSロック

ACCESSロックを使用すると問合わせの待ち時間を削減できるかもしれませんが、注意深く処理しないと余計な作業が増えてしまう可能性もあります。ロックの細分度と問合わせの傾向を理解して、余計なオーバーヘッドをワークロードに加えないようにする必要があります。

例えば、修飾子LOCKING TABLE customer FOR ACCESSはテーブル レベルのロックを要求します。その結果、問合わせ計画に単一のAMPステップしかないとしても、全AMPで操作が行なわれます。このロック修飾子を使用すると、全AMPが関係する2つの余計なステップが問合わせ計画に追加されるかもしれず、PEとAMPの間で余分のディスパッチャ ステップが強制的に送り出されることになります。

以下のEXPLAINレポートで、全AMPのテーブル レベルのロックを実行するために生成される別個のステップ(ステップ1)と、全AMPに渡ってテーブル レベルのロックを解放する追加のステップ(ステップ3)に注目してください。

     EXPLAIN
     LOCKING TABLE customer FOR ACCESS
     SELECT c_name, c_acctbal
     FROM customer
     WHERE c_custkey = 93522;
Explanation
-----------------------------------------------------------------------
  1) First,  we lock CAB.customer for access.
  2) Next, we do a single-AMP RETRIEVE step from CAB.customer by
     way of the unique primary index "CAB.customer.C_CUSTKEY =
     93522" with no residual conditions. The estimated time for this
     step is 0.03 seconds.
  3) Finally,  we send out an END TRANSACTION step  to all AMPs involved
     in processing the request.

プライマリ インデックス値を使用して単一行を読み取るなどの単一AMP操作の場合、テーブル全体をロックするよりも、LOCKING ROW FOR ACCESSロック修飾子を使用するほうが常に優れています。EXPLAINレポートを見ると、行レベルのACCESSロックが適用されていることと、問合わせの処理に使用されているAMPが1つだけであることが分かります。

次のEXPLAINテキストは、行レベルのACCESSロックを示しています。

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT c_name, c_acctbal
     FROM customer
     WHERE c_custkey = 93522;
 
Explanation
-----------------------------------------------------------------------
  1) First, we do a single-AMP RETRIEVE step from CAB.customer by
     way of the unique primary index "CAB.customer.C_CUSTKEY =
     93522" with no residual conditions  locking row for access. The
     estimated time for this step is 0.03 seconds.

ロック強化機能の自動エスカレーション

行ハッシュ レベルのACCESSロックを明示的に指定したとしても、もし問合わせ計画が全AMP操作を必要としていて、問合わせで参照されるテーブルが1つしかない場合には、最適化ルーチンは行ハッシュ レベルのACCESSロックを自動的にテーブル レベルのロックに変換します。EXPLAINレポートを常にチェックして、戦術的問合わせのために行ハッシュ レベル(適切な場合にはテーブル レベル)のACCESSロックが発行されていることを確認するようにしてください。

以下の全AMP問合わせのEXPLAINレポートでは、LOCKINGリクエスト修飾子が明示的に行ハッシュ レベルのACCESSロックを要求しているにもかかわらず、最適化ルーチンがテーブル レベルのACCESSロックを適用する様子が示されています。

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT c_name, c_acctbal
     FROM customer
     WHERE c_nationkey = 15;
Explanation
-----------------------------------------------------------------------
1) First,  we lock CAB.customer for access.
2) Next, we do  an all-AMPs RETRIEVE step  from CAB.customer by way
   of an all-rows scan with a condition of
   ("CAB.customer.C_NATIONKEY = 15")
   into Spool 1, which is built locally on the AMPs. The input table
   will not be cached in memory, but it is eligible for synchronized
   scanning. The size of Spool 1 is estimated with high confidence
   to be 300,092 rows. The estimated time for this step is 2 
   minutes and 8 seconds.
3) Finally,  we send out an END TRANSACTION step  to all AMPs
   involved in processing the request.

リクエストの中で複数のテーブルが指定されていたとしても、LOCKING ROW FOR ACCESS修飾子が指定されている場合、最適化ルーチンは、そのリクエストの中で全AMPアクセスを受けるすべてのテーブルについてテーブル レベルのACCESSロックを適用します。

リクエストそのものによって実行されるのが単一のAMP操作に過ぎない場合に、テーブル レベルのACCESSロックを使用するコストと、行ハッシュ レベルのACCESSロックを使用するコストとを、次のグラフに比較して図示します。



経過時間は、20のセッションを使用して、100 000の単一行のSELECTリクエストを実行するための合計時間を表わしています。リクエストに対してテーブル レベルのACCESSロックを指定した場合、このワークロードの合計実行時間は5倍に増加しました。行ハッシュ レベルACCESSロックおよびNO LOCKING修飾子なしというラベルが付いている変数の応答時間は、ほとんど同一です。これは、NO LOCKING修飾子が指定された場合に、最適化ルーチンがバックグラウンドで行ハッシュ レベルのREADロックを適用したためです。このロックのオーバーヘッドは、行レベルのACCESSロックと同じです。

リクエスト ツールの中には、ACCESSロック修飾子を指定しにくいものもあります。それでも、ACCESSロックを明示的に設定することはできます。この場合、ビュー定義の中で適切なLOCKINGリクエスト修飾子を指定し、そのビューに対して問合わせを行ないます。

行ハッシュ レベルのACCESSロック、結合インデックス、および戦術的問合わせ

最適化ルーチンは、適切な場合に、行ハッシュ レベルのACCESSロックを結合インデックスに伝搬させます。次のような、customerテーブルに定義された単一テーブル結合インデックスを想定してみてください。customerテーブルのUPIは、c_custkeyです。あるクエリーがリクエストを出し、c_nameの値を指定して、行ハッシュ レベルのACCESSロックがcustomerテーブルに適用されるようリクエストします。

   CREATE JOIN INDEX CustNameJI
    AS SELECT c_name, c_acctbal, c_mktsegment, c_range
    FROM customer
    PRIMARY INDEX (c_name);
   
    EXPLAIN
    LOCKING ROW FOR ACCESS
    SELECT c_acctbal, c_mktsegment, c_range
    FROM customer
    WHERE c_name='Customer#000000999';
Explanation
-----------------------------------------------------------------------
1) First, we do  a single-AMP RETRIEVE step from CAB.NAMEJI  by
way of the primary index "CAB.NAMEJI.C_NAME = 'Customer#000000999'"
with a residual condition of 
("CAB.NAMEJI.C_NAME = 'Customer#000000999'") into Spool 1, which is 
built locally on that AMP. The input table will not be cached in
memory, but it is eligible for synchronized scanning 
 locking row for access. The size of Spool 1 is estimated
with high confidence to be 1 row.

結合インデックスがクエリーを引き受け、指定された特定のc_name値に基づきリクエストされたcustomerテーブルのデータを提供します。最適化ルーチンは、要求された行ハッシュ レベルのACCESSロックを、基本テーブルの行ハッシュにではなく、結合インデックスの行ハッシュに適用します。

行ハッシュ レベルのACCESSロック、グループAMPステップ、および戦術的問合わせ

グループAMPステップの主要なメリットの1つは、テーブル レベルのロックを回避できるということです。テーブル レベルのロックを設定する代わりに、グループAMP操作では、AMPグループ内の行ハッシュごとに行ハッシュ レベルのロックを設定します。ロック レベルが変わることによるこのようなパフォーマンスの向上は、SQLリクエストにLOCKING ROW FOR ACCESS修飾子を指定することによって行ハッシュ レベルのロックを明示的に要求した場合にも発生します。これは、以下に示すSELECTリクエストで生成されるEXPLAINレポートに示されています。リクエストでアクセスされる2つのテーブルのうちどちらのテーブルの行に対しても、ステップ2において、ACCESS重大度の行ハッシュ レベルのロックが適用されていることに注意してください。

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT p_name, p_type
     FROM lineitem, parttbl
     WHERE l_partkey=p_partkey
     AND   l_orderkey=5;
Explanation
-----------------------------------------------------------------------
  1) First, we do a  single-AMP RETRIEVE  step from CAB.lineitem by way
     of the primary index "CAB.lineitem.L_ORDERKEY = 5" with no
     residual conditions  locking row for access  into Spool 2
     (group_amps), which is redistributed by hash code to all AMPs.
     Then we do a SORT to order Spool 2 by row hash. The size of Spool
     2 is estimated with low confidence to be 1 row. The estimated
     time for this step is 0.01 seconds.
  2) Next, we do a  group-AMPs JOIN  step from Spool 2 (Last Use) by way
     of a RowHash match scan,  which is joined to CAB.parttbl locking 
     row of CAB.parttbl for access. Spool 2 and CAB.parttbl are
     joined using a merge join, with a join condition of ("L_PARTKEY =
     CAB.parttbl.P_PARTKEY"). The result goes into Spool 1
     (group_amps), which is built locally on that AMP. The size of
     Spool 1 is estimated with low confidence to be 1 row. The
     estimated time for this step is 0.11 seconds.
  3) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.

ACCESSロック、結合および戦術的問合わせ

行ハッシュ レベルのACCESSロックが要求された問合わせに結合操作も含まれている場合、最適化ルーチンは、行ハッシュ レベルのACCESSロック リクエストを2つのテーブルの両方に(適用可能な場合)適用します。

次のリクエストで生成される最適化ルーチン計画は、2つのテーブルが共通のorderkey UPI列に関して結合されているため、単一AMPです。

    EXPLAIN
    LOCKING ROW FOR ACCESS
    SELECT l_quantity, l_partkey, o_orderdate
    FROM lineitem, ordertbl
    WHERE l_orderkey=o_orderkey
    AND   o_orderkey=832094;
Explanation
-----------------------------------------------------------------------
  1) First, we do  a single-AMP JOIN  step from CAB.ordertbl by way of
     the unique primary index "CAB.ordertbl.O_ORDERKEY = 832094" with
     no residual conditions, which is joined to CAB.lineitem by way of
     the primary index "CAB.lineitem.L_ORDERKEY = 832094"  locking 
     row of CAB.ordertbl for access and row of CAB.lineitem for 
      access. CAB.ordertbl and CAB.lineitem are joined using a
     merge join, with a join condition of ("CAB.lineitem.L_ORDERKEY =
     CAB.ordertbl.O_ORDERKEY"). The input tables CAB.ordertbl and
     CAB.lineitem will not be cached in memory, but CAB.ordertbl 
     is eligible for synchronized scanning. The result goes into 
     Spool 1(one-amp), which is built locally on that AMP. The 
     size of Spool 1 is estimated with low confidence to be 35
     rows. The estimated time for this step is 0.03 seconds.

戦術的問合わせにおける行ハッシュ レベルのACCESSロックと集約処理の互換性

集約処理を単一AMP操作として実行可能な場合、最適化ルーチンは、明示的な行ハッシュ レベルのACCESSロック リクエストを受け付けます。例えば、次のリクエストでは、l_orderkeyはlineitemテーブルのNUPIであり、リクエストを満たすためにロックされる必要があるのは単一の行ハッシュだけです。

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT l_quantity, COUNT(*)
     FROM lineitem
     WHERE l_orderkey=382855
     GROUP BY l_quantity;
   
Explanation
-----------------------------------------------------------------------
  1) First, we do a  single-AMP SUM  step to aggregate from CAB.lineitem
     by way of the primary index "CAB.lineitem.L_ORDERKEY = 382855"
     with no residual conditions, and the grouping identifier in field
     1029 locking row for access. Aggregate Intermediate Results are
     computed locally, then placed in Spool 3. The input table will
     not be cached in memory, but it is eligible for synchronized
     scanning. The size of Spool 3 is estimated with low confidence to
     be 35 rows. The estimated time for this step is 0.03 seconds.
  2) Next, we do a  single-AMP RETRIEVE  step from Spool 3 (Last Use) by
     way of the primary index "CAB.lineitem.L_ORDERKEY = 382855" into
     Spool 1 (one-amp), which is built locally on that AMP. The size
     of Spool 1 is estimated with low confidence to be 35 rows. The
     estimated time for this step is 0.04 seconds.
  3) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.

行ハッシュ レベルのACCESSロックを明示的に要求する場合、その問合わせにおいて全AMP集約処理が実行されるなら、そのロックは最適化ルーチンによってテーブル レベルのACCESSロックに昇格されます。

     EXPLAIN
     LOCKING ROW FOR ACCESS
     SELECT SUM (l_quantity), SUM (l_extendedprice), COUNT(*)
     FROM lineitem;
Explanation
-----------------------------------------------------------------------
  1) First,  we lock CAB.lineitem for access.
  2) Next, we do an all-AMPs SUM step to aggregate from
     CAB.lineitem by way of an all-rows scan with no residual
     conditions. Aggregate Intermediate Results are computed globally,
     then placed in Spool 3. The input table will not be cached in
     memory, but it is eligible for synchronized scanning. The size of
     Spool 3 is estimated with high confidence to be 1 row. The
     estimated time for this step is 36 minutes and 56 seconds.
  3) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs. The size of Spool 1 is estimated with high
     confidence to be 1 row. The estimated time for this step is 0.67
     seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.

問合わせツールの中には、SQLリクエストにACCESSロック修飾子を含めることが難しいものがあります。これが問題になる場合には、問合わせが基本テーブルにアクセスするために使用するビューの中にLOCKINGリクエスト修飾子を含めることによって、ACCESSロックを強制的に適用できます。

戦術的問合わせの更新用ロック

テーブルのプライマリ インデックス値を指定する単純な更新リクエストは、更新を処理するために行ハッシュ レベルのWRITEロックを適用するよう、最適化ルーチンに合図を送ります。行ハッシュ レベルのWRITEロックまたは行ハッシュ レベルのREADロックは、EXPLAINテキストに報告されません。以下のリクエストは、o_orderkeyに定義されたUPIを使用して単一行にアクセスすることにより、インデックスが付けられていないordersテーブルの列o_orderpriorityを更新します。このリクエストを処理するために使用されるのは、単一のAMPと行ハッシュ レベル ロックだけです。

    EXPLAIN
    UPDATE orders
    SET o_orderpriority = 5
    WHERE o_orderkey = 39256
   
Explanation
-----------------------------------------------------------------------
  1) First, we do  a single-AMP UPDATE  from CAB.orders by way of
     the unique primary index "CAB.orders.O_ORDERKEY = 39256"
     with no residual conditions.
  -> No rows are returned to the user as the result of statement 1.

複雑な戦術的問合わせの更新用ロック

以下のクエリーでは、UPI (o_orderke)を使用して単一の行にアクセスすることにより、同じ非インデックス列(o_orderpriority)を更新しますが、さらに共通のorderkey値に関してlineitemの行と結合する操作も含まれています。

複雑な更新操作が単一AMP操作であり、結合対象の2つのテーブルに共通のUPIに等価条件がある場合(この例ではo_orderkeyとl_orderkey)、生成されるクエリー計画では、行ハッシュ レベルのロックを使用したパフォーマンスの良い単一のAMPマージ更新が指定されます。次のUPDATEリクエストのEXPLAINレポートのステップ1を参照してください。

    EXPLAIN UPDATE ordertbl
    FROM lineitem
    SET o_orderstatus = 'OK'
    WHERE l_orderkey = o_orderkey
    AND   l_shipdate = o_orderdate
    AND   o_orderkey = 5;
   
Explanation
-----------------------------------------------------------------------
  1) First, we do a  Single AMP MERGE Update  to CAB.ordertbl from
     CAB.lineitem by way of a RowHash match scan.
  2) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.

複雑な更新操作の中には、複数の全AMPステップが関係するものがあります。その場合には、テーブル レベルのロックが必要になります。このような複雑な更新を時々実行するだけであれば、このことは大してパフォーマンス上の問題にはならないでしょう。しかし、そのような操作をかなりの回数実行しなければならない場合、それらの全AMP操作の効果と、それに付随して必要になるテーブル レベルWRITEロックとの効果が組み合わさると、全AMPリクエストの増大による働きとしてスケーラビリティが低下する傾向にあります。