SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Synapse の BroadcastMove / ShuffleMove の動作について

leave a comment

Synapse Analytics では、データの移動について、

  • BroadcastMoveOperation
  • ShuffleMoveOperation

という 2 種類の操作を目にする機会が多いかと思います。

これらの動作については、次のドキュメントなどに情報が記載されています。

Synapse の SQL Pool に関しては、Analytics Platform System (APS) という、以前は、Parallel Data Warehouse (PDW) と呼ばれていたシステムがベースになっており、基本的な考え方については、並列データウェアハウスコンポーネント-分析プラットフォームシステム の情報も参考になるケースがあります。

 

クエリの操作については、Analytics Platform System Appliance Update 5 Documentation and Client Tools からダウンロードできるドキュメントが今でも一番情報が記載されているのではないでしょうか。

このドキュメント内には、BroadcastMoveOperation と ShuffleMoveOperation についても記載されています。

  • BroadcastMoveOperation
    • 分散データをレプリケートされたテーブルに移動する
    • この操作は、ディストリビューションと互換性のない結合を実行しているときに使用される機会がある。
    • この操作を実行するために、各ディストリビューションは、該当の行をすべてのComputeノードにブロードキャストする。
    • その結果、各Computeノードは、自分の行に他のComputeノードから受け取ったすべての行を加えて、レプリケートされたテーブルを作成する。
  • ShuffleMoveOperation
    • 分散されたテーブルを再配布する。
    • 再配布されたテーブルは、元の分散テーブルとは異なる分散カラムを持ち、これは、互換性のない結合や互換性のない集約を実行している場合に使用されることがある。
    • この操作を実行するために、DMSは、各行を分散先のテーブルの分布列に従って正しい計算ノードに移動する。
    • 既に正しいComputeノードに格納されている行は、この操作中はコピーされない。

というような記載があります。

 

基本的には、ディストリビューションに格納されていないデータ間での結合や集計が発生した際に、他のディストリビューションのデータを再配布することで必要なデータをコピーする処理となるのですが、どのような動作が実行されているかについて、まとめたことがなかったので、この機会にまとめておこうかと思います。

確認に使用するクエリ

動作の確認には、TPC-H の Q16 が実行されているクエリもシンプルですので、このクエリを実行して各種情報を取得してみたいと思います。

/* TPC_H Query 16 - Parts/Supplier Relationship */
SELECT P_BRAND, P_TYPE, P_SIZE, COUNT(DISTINCT PS_SUPPKEY) AS SUPPLIER_CNT
FROM PARTSUPP, PART
WHERE P_PARTKEY = PS_PARTKEY AND P_BRAND <> 'Brand#45' AND P_TYPE NOT LIKE 'MEDIUM POLISHED%%'
AND P_SIZE IN (49, 14, 23, 45, 19, 3, 36, 9) AND PS_SUPPKEY NOT IN (SELECT S_SUPPKEY FROM SUPPLIER
 WHERE S_COMMENT LIKE '%%Customer%%Complaints%%')
GROUP BY P_BRAND, P_TYPE, P_SIZE
ORDER BY SUPPLIER_CNT DESC, P_BRAND, P_TYPE, P_SIZE
OPTION (LABEL = 'Q16')

今回はデータの移動を発生させて情報の取得を行いたいため、TCP-H のテーブルはすべてラウンドロビンの分散でデータを格納しています。

 

Synapse Analytics のクエリ情報の取得方法

Synapse のクエリ情報の取得ですが、クエリ実行を監視する のステップで情報を取得することが一般的ではないでしょうか。

Synapse ではポータルのクエリアクティビティから次のような情報を取得することができます。

image

上記のドキュメントはこの情報をクエリベースでブレークダウンしながら取得する方法が記載されています。

Synapse のクエリの情報ですが、監査ログに記載されている サーバーの監査を設定する の動作と似たような形で、長い文字列が切り捨てられるケースが多いです。

Azure SQL Database および Azure Synapse の監査では、監査レコードの文字フィールドに 4,000 文字のデータを格納します。 監査可能なアクションから返された、statement または data_sensitivity_information 値に 4,000 を超える文字が含まれる場合、最初の 4,000 文字以降のすべてのデータは、切り捨てられ、監査されません。 以下のセクションでは、Azure Portal を使用した監査の構成について説明します。

クエリの情報の詳細は 4,000 文字で切り捨てられていることが多く、長い文字のクエリについては クエリストア を使用しないと、全文が取得できないケースに遭遇することになります。(クエリストアであれば 4,000 文字を超えたクエリの情報も取得できていそうでした)

Synapse のクエリストアについては、設定がシンプルな構文になっており、細かな設定を行うことができません。

ALTER DATABASE DWDB SET QUERY_STORE = ON

sys.database_query_store_options から確認できる範囲では、サイズ 10 GB / 保持期間 7 日がデフォルトの設定となっているようで、期間やサイズについては、現時点では変更することができないようです。


クエリストアについては、DMV の request_id や session_id に紐づけることができないような通常の SQL Server のクエリストアと同等の構造になっているようなので、クエリ ラベル を活用しながら、クエリ内のテキストラベルでクエリを特定するような方法を検討してもよさそうですね。

 

実際にクエリ情報を取得してみる

今回は、SSMS でテスト用のクエリを入力しているウィンドウの情報を基にして、クエリの情報をもう一つのウィンドウから取得していきます。

SSMS で接続を行うとステータスバーにセッション ID の情報が表示されています。

image

Synapse ではセッション ID は 「SIDxx」という形式で管理されており、この形式のセッション ID については「SESSION_ID()」関数で取得ができます。

image

従来の SQL Server の形式のセッション ID を SID に変換してクエリの情報を取得する場合、次のようなクエリで情報を取得することができます。

DECLARE @session_id int = 129
DECLARE @pdw_session_id varchar(32), @request_id varchar(32)
SELECT @pdw_session_id = session_id FROM sys.dm_pdw_exec_sessions WHERE sql_spid = @session_id
SELECT TOP 1 @request_id = request_id FROM sys.dm_pdw_exec_requests WHERE session_id = @pdw_session_id ORDER BY submit_time DESC

SELECT * FROM sys.dm_pdw_request_steps WHERE request_id =@request_id ORDER BY step_index ASC
SELECT * FROM sys.dm_pdw_sql_requests WHERE request_id =@request_id ORDER BY step_index ASC, pdw_node_id ASC, distribution_id ASC
SELECT * FROM sys.dm_pdw_dms_workers WHERE request_id = @request_id ORDER BY step_index ASC, pdw_node_id ASC, distribution_id ASC, type ASC

 

今回、Q16 のクエリを設定しているウィンドウはセッション ID 129 を使用しているので、そのセッション ID で実行されたクエリの最新の 1 つのリクエストについて、クエリ情報を取得しています。

Synapse のクエリ情報については、次の 3 種類の DMV を活用することになります。

sys.dm_pdw_request_steps が、ポータルのクエリアクティビティ相当の情報となりますので、これが起点となるかと。

今回のクエリでは requst_steps からは次のような情報が取得できます。

image

BroadcastMoveOperation と ShuffleMoveOperation が含まれていますので、これらを確認してみましょう。

 

BroadcastMoveOperation

基本的には、次の 3 ステップで構成されます。

  • RandomIDOperation
  • OnOperation
  • BroadcastMoveOperation

image

RandomIDOperation では、「TEMP_ID_55」というように、ランダムな ID の付与が行われます。

OnOperation では、次のようなクエリが実行されます。

CREATE TABLE [qtabledb].[dbo].[TEMP_ID_55] ([S_SUPPKEY] INT NOT NULL ) WITH(DISTRIBUTED_MOVE_FILE='');

 

Move Operation では、DMS (Data Movement Service) により、ディストリビューション間のデータの移動が行われます。

この際、移動されたデータに関しては、tempdb に作成された一時テーブルに対して格納が行われますが、このデータ格納用のテーブルが佐草精されています。

最後に BroadcastMoveOperation により、次のようなクエリの実行が行われ、データの移動が行われます。

SELECT 
    [T1_1].[S_SUPPKEY] AS [S_SUPPKEY]
FROM
(
    SELECT 
        [T2_1].[S_SUPPKEY] AS [S_SUPPKEY]
    FROM 
        [DWDB].[dbo].[SUPPLIER] AS T2_1
    WHERE([T2_1].[S_COMMENT] LIKE CAST(N'%%Customer%%Complaints%%' COLLATE SQL_Latin1_General_CP1_CI_AS AS varchar(24)) COLLATE SQL_Latin1_General_CP1_CI_AS)
) AS T1_1
OPTION (MAXDOP 7, MIN_GRANT_PERCENT = [MIN_GRANT], DISTRIBUTED_MOVE(N''), MAX_GRANT_PERCENT = [MAX_GRANT])

 

request_step ではここまでの情報が取得できます。

Synapse は、60 のデータベース (ディストリビューション) が、各 Compute ノードに接続され、データの検索が行われます。

今回は、DW3000c ちうパフォーマンスレベルを使用していますので、6 台のノードに、10 個ずつデータベースが接続されている状態となります。

image

 

BroadcastMoveOperation の場合、各 Compute Node にテーブルが一つずつ作成され、そのテーブルにデータが配信されます。

このような動作が行われているという状態については、sys.dm_pdw_sql_requests から確認することができます。

sys.dm_pdw_request_steps の OnOperation を sys.dm_pdw_sql_requests から確認すると、各 Compute Node で実際に実行されているクエリの情報を確認できます。

image

6 台の Compute Node の tempdb に対してテーブルが作成されていることが確認できますね。

image

各 Compute Node に対して、BroadcastMoveOperation  によってデータを移動させる先のテーブルが作成されました。

データの移動については DMS によって実施されます。

BroadcastMoveOperation については、sys.dm_pdw_sql_requests からも取得できますが、実際の移動は DMS で行われますので、詳細については、sys.dm_pdw_dms_workers で確認できます。

image

各 Compute Node の DMS がどのようにデータを読み込んで、処理が行われたかについては、この DMV から確認することができます。

「DIRECT READER」により、他のディストリビューションのデータを読み取り、WRITER により書き込みが行われていることが確認できます。

BroadcastMoveOperation は同一のデータを書き込みますので、WRITER で書き込まれている行数については、同じになっているようですね。

image

 

BroadcastMoveOperation については、各 Compute Node にテーブルを一つずつ作成し、作成したテーブル内にはすべて同一のデータを格納することで、後続の処理の結合に使用できるようにします。

Synapse のレプリケートテーブルを作成するような動作をクエリ内で実行しているイメージでしょうか。

 

ShuffleMoveOperation

 

基本的な考え方については、BroadcastMoveOperation と同じで、各 Compute Node にテーブルを作成して、データの移動を行います。

ShuffleMove については、データを再配布しますので、「OnOperation」で作成されるテーブルは 60 個となり、各 Compute Node に 60 のテーブルが均等に作成されます。

image

このような形で、各 Conmpute Node に合計で 60 このテーブルが作成され、各テーブルに対して、必要なデータが再配布される形となります。

image

「HASH_READER」という処理により新しいハッシュ値で対象のデータを読み取り、WRITER により新しく作成した 60 のテーブルに対して再頒布してデータん書き込みを行います。

クエリで必要なデータに絞って、再頒布をしているのでデータ量は抑えられていますが、再度 60 分割されたデータの生成が行われますので、Shuffule Move はできるだけ抑えた方がよい処理です。

(QTable ですが、Heap で作られているようなので、大量データがシャッフルされる場合のオーバーヘッドも気になります)

 

この時、各テーブルには新しい列のハッシュ値によって、データが再頒布されているはずだり、、内部では「ShuffleParameters」というパラメーターによって、どの項目を使用して再頒布を行ったかの情報を持っているはずなのですが、残念ながら ShuffleMoveOperation でこの項目を確認することはできなさそうです。

このパラメーターは、テキストの後半に存在しており、ShuffleMoveOperation の場合、4,000 文字で切り捨てられてしまっている情報に含まれているので、確認することができなさそうです。

 

ReturnOperation

ここまででクエリで必要になるデータが、各 Compute Node で利用可能ないようにデータの再頒布が行われました。

再頒布を行ったデータによって、各 Compute Node で結果を取得する処理が ReturnOperation となります。

今回のクエリであれば、次のようなクエリが実行されています。

SELECT 
    [T1_1].[P_BRAND] AS [P_BRAND],
    [T1_1].[P_TYPE] AS [P_TYPE],
    [T1_1].[P_SIZE] AS [P_SIZE],
    [T1_1].[col] AS [col]
FROM
(
    SELECT 
        COUNT(DISTINCT [T2_1].[PS_SUPPKEY]) AS [col],
        [T2_1].[P_BRAND] AS [P_BRAND],
        [T2_1].[P_TYPE] AS [P_TYPE],
        [T2_1].[P_SIZE] AS [P_SIZE]
    FROM 
        [qtabledb].[dbo].[TEMP_ID_57] AS T2_1
    GROUP BY 
        [T2_1].[P_BRAND],
        [T2_1].[P_TYPE],
        [T2_1].[P_SIZE]
) AS T1_1
ORDER BY 
    [T1_1].[col] DESC,
    [T1_1].[P_BRAND] ASC,
    [T1_1].[P_TYPE] ASC,
    [T1_1].[P_SIZE] ASC
OPTION (MAXDOP 7, MIN_GRANT_PERCENT = [MIN_GRANT], MAX_GRANT_PERCENT = [MAX_GRANT])

必要となる情報をピンポイントで取得することは難しいのですが、DBCC PDW_SHOWEXECUTIONPLAN という DBCC コマンドで、各ディストリビューションで実際に実行されているクエリの実行プランを取得することができます。

DMS でデータの再頒布や再頒布されたデータを使用したクエリの処理が遅い場合には、この DBCC コマンドを活用することで、実際のクエリを確認することができます。

image

Hash Join が行われている場合、どの項目で行われているかを確認することで、ShuffleMoveOperation で、再頒布された際に、どの辺の項目を使用して再頒布のハッシュ値を生成したかの推測に使うことができないかなとも思っています。

 

Synapse のクエリを眺めるとき、どの辺見るのかをちょいちょい忘れてしまうので、この機会にメモを残しておきました。

Share

Written by Masayuki.Ozawa

9月 22nd, 2020 at 11:57 pm

Posted in Synapse Analytics

Tagged with

Leave a Reply