Synapse Analytics の Dedicated SQL Pool (専用 SQL プール) に対して、データローディングを行う際には、いくつかのポイントがあり、特性を意識したデータローディングを実施しないと、パフォーマンスが大幅に低下する恐れがあります。
本投稿ではどのようなアプローチを行えばよいのかについてまとめておきたいと思います。
Contents
外部ファイルのデータローディング
外部ファイルのデータローディングを行う際に、一読しておきたいドキュメントが Azure SQL Data Warehouse loading patterns and strategies です。
2 年以上前のドキュメントですが、Gen2 を使用した場合についての動作についても触れられており、「ファイルからデータをローディング」する際には必読のドキュメントです。
外部ファイルからデータをローディングする際には、「どのように Reader が動作しているか」を意識することが重要です。
基本動作として意識しておくことが次の記載です。
- PolyBase automatically parallelizes the data load process, so you don’t need to explicitly break the input data into multiple files and issue concurrent loads, unlike some traditional loading practices. Each reader automatically read 512MB for each file for Azure Storage BLOB and 256MB on Azure Data Lake Storage.
- Multiple readers will not work against gzip files. Only a single reader is used per gzip compressed file since uncompressing the file in the buffer is single threaded. Alternatively, generate multiple gzip files. The number of files should be greater than or equal to the total number of readers.
Dedicated SQL Pool のデータローディングについては、MPP の並列処理の性能特性を活かすため、PolyBase または、COPY ステートメントを使用してデータのロードを行うことを最初に検討します。
(T-SQL によるステートメント実行でなく、Azure Data Factory (ADF) で実施する場合もこれらの機能を使用することができます)
フラットファイルのロードについては、BLOB ストレージの場合は、1 ファイルであっても 512MB / ADL Gen2 については、256MBに自動的に分割され、Reader によって処理が行われます。
gzip で圧縮されたファイルについてもロードすることができますが、gzip については、自動的に分割はされず、ファイル単位で Reader によって処理が行われるため、gzip を直接ロードする場合には、適切なファイル数に分割した状態で、データロードに使用するストレージに配置を行う必要があります。
リーダーの挙動を意識する
実際の動作を確認してみます。
TRUNCATE TABLE LINEITEM; INSERT INTO LINEITEM SELECT * FROM [dbo].[lineitem_text_external] OPTION (LABEL='DataLoading');
このクエリは、外部データソースに配置しているフラットファイルをロードしているクエリになります。
データローディングで最初に意識することが重要なのが「Reader がどのように起動されているか」です。
データローディングをする際には、データローディング先の処理性能も重要ですが、「データローディング元」のアクセス方法も重要です。
上記のクエリであれば、クエリで指定しているラベルを使用して、クエリ実行中に次のようなクエリで情報の取得を行います。
DECLARE @request_id nvarchar(100) = (SELECT TOP 1 request_id FROM sys.dm_pdw_exec_requests WHERE [label] = 'DataLoading' ORDER BY request_id DESC) SELECT type, status, bytes_per_sec, bytes_processed, source_info FROM sys.dm_pdw_dms_workers WHERE request_id = @request_id ORDER BY step_index ASC
次の画像は上が PolyBase、下が COPY ステートメントを使用して、513 MB 程度の 1 つのフラットファイルのデータロードしたときの情報です。
PolyBase でデータをロードした場合、1 ファイルでも複数の Reader が自動的に起動して、並列データの読み込みが行われており、COPY の場合は、Direct Reader を使用して、1 ファイルでデータの読み取りが行われていることが確認できますね。
ちなみに上記のケースの場合、External Reader が 2 個起動している Polybase の方が早いかというと、実は、Direct Reader が 1 個起動している COPY ステートメントの方が処理時間が短かったりもします。
COPY ステートメントのパフォーマンスについては、PolyBase と比較した場合の COPY コマンドのパフォーマンスについて教えてください。に記載されています。
ワークロードによっては PolyBase より COPY ステートメントの方がパフォーマンスが向上するケースがあることが明記されていますので、実行するワークロードではどちらが適切かをを検証してみることはパフォーマンスを改善させるために重要な要素の一つです。
外部ファイルのデータローディングでは次のような内容はローディング最適化を行う際のポイントとなるのではないでしょうか。
- Reader が何個起動してデータの取り込みが行われるか
- PolyBase / COPY ステートメントのどちらがパフォーマンスが良いか
LOCATION で使用するアクセスポイント
Synapsse から外部データをストレージからローディングする場合、Azure BLOB ストレージを使用するケースが多く、接続先の指定については、次のドキュメントから確認できます。
最近だと、ADL Gen2 をデータソースとすることが多いのではないでしょうか。
BLOB と ADL Gen2 の違いについては、次のドキュメント等に記載されています。
ADL Gen2 は、マルチプロトコルアクセス が可能なストレージですので、BLOB API / ADLS API の両 API を使用してアクセスを行うことができます。
これは、Synapse からのアクセスも同様で、PolyBase でアクセスする場合は、「abfss」「wasbs」のプレフィックス、COPY ステートメントでアクセスする場合は、「dfs.core.windows.net」「blob.core.windows.net」のアクセスによって、どちらの API を使用するかを使い分けることができます。
COPY ステートメントについては次の記載があり、blob エンドポイントの方が高速なようです。
.blob エンドポイントは ADLS Gen2 にも使用でき、現在最高のパフォーマンスを実現しています。 お使いの認証方法で .dfs が必要ない場合、.blob エンドポイントを使用します。
PolyBase を使用する場合ですが、シングルファイルの取り込みで検証した際には、wasbs より abfss (ADLS API) の方が高速に処理ができているケースがありました。
どちらの API を使用するかによって性能に変化が出るワークロードがありそうですので、ADL Gen2 を使用する場合、自分が実行するワークロードでは、BLOB API / ADLS API のどちらを使うと最適な性能が出るかは確認しておくと良いかと。
ADL Gen2 も Standard / Premium がありますので、Standard の ADL Gen2 でデータローディング性能が出ない場合は、Premium の Gen2 を使用してみるのも良いのではないでしょうか。
データ取り込み先のテーブルの形式
データローディングを行う際の取り込み先のテーブル (ターゲットテーブル) の設定も意識する必要があります。
テーブルの設定については、次のようなドキュメントが公開されています。
最終的なデータを提供するテーブルについては、「列ストアインデックス」「パーティショニング」のテーブルの利用を検討することになるかと思いますが、ステージング用のようなデータ加工用のテーブルについては、「ヒープ」「一時テーブル」を使用することで性能向上につながる可能性があります。
列ストアインデックス / ヒープ
「列ストアインデックス」「ヒープ」のどちらが適しているかは、
- データ投入後のデータの状態 (インデックスの品質)
- 投入したデータを再加工して利用するか
というような観点も考量する必要があるかと思います。
列ストアインデックスは、列指向 / 高圧縮により高速なデータ読み取りを可能としますが、「投入後のデータがどのような状態になっているか」が重要です。
これについては、列ストア インデックスの品質の低さの原因 に記載されていますが、列ストアインデックスは 100 万行 (Bulk Insert の場合は、10 万行) 単位で圧縮が行われるという構造になっており、「データ投入後に圧縮された状態になっているか」が重要です。 (列ストアインデックスの構成の基本については、列ストア インデックス – データ読み込みガイダンス を参照して下さい)
データの投入が終わった際に、次のようなクエリを実行して、列ストアインデックスの状態を確認します。
SELECT
rg.distribution_id,
rg.state_description,
rg.size_in_bytes,
ps.row_count
FROM
sys.pdw_permanent_table_mappings AS tm
INNER JOIN sys.pdw_nodes_tables AS nt
ON nt.name = tm.physical_name
INNER JOIN sys.pdw_nodes_column_store_row_groups AS rg
ON rg.object_id = nt.object_id AND nt.distribution_id = rg.distribution_id
INNER JOIN sys.dm_pdw_nodes_db_partition_stats AS ps
ON ps.object_id = nt.object_id and ps.distribution_id = nt.distribution_id
WHERE
tm.object_id = OBJECT_ID('LINEITEM')
「state_description」が「OPEN」になっているようだと、該当の行グループは圧縮されていない状態 (行ストアとなっている状態) となり、列ストアインデックスによる検索効率化のメリットを受けることができません。
列ストアとして圧縮されている場合は「COMPOERSSED」となっており、この状態であれば、列ストアとして圧縮が行われている状態です。
列ストアインデックスはデータ投入後の状態を意識することで最適な性能を発揮することができますので、データ投入後にインデックスがどのような状態となっているかを把握することは重要です。
ステージングテーブルを列ストア化して後続の検索を効率的に実行するのであれば、圧縮されている状態が好ましいですし、圧縮されていない状態なのであればヒープを使った方が効率的です。
永続化テーブル / 一時テーブル
通常のテーブル (CREATE TABLE テーブル名) を使用するか、一時テーブル (CREATE TABLE #テーブル名) を使用するかについてもパフォーマンスに影響する要因となります。
一時テーブルとは に記載されていますが、Synapse の一時テーブルは NVMe のローカルストレージ上に作成されます。これは、tempdb が ローカルの NVMe 上に作成されているためです。
Synapse には、「アダプティブ キャッシュ」という列ストアのセグメントをキャッシュする機能もあり、これについても NVMe のローカルストレージ上に格納が行われます。
一方、通常のテーブルについては、Azure ストレージ上に構成が行われており、リモートストレージへのアクセスとなります。
実際のアクセスパスを確認すると次のような違いがあります。
1 行目がデータベースの mdf/ndf/ldf ファイルへのアクセス時のアクセスパスで、BLOB ストレージが利用されていることが確認できます。
2 行目が tempdb / 3 行目がアダプティブキャッシュへのアクセスパターンとなるのですが、これらにアクセスされる場合は、ローカルドライブが使用されていることが確認できます。
一時テーブルに使用される tempdb はローカルドライブが実際に使用されていることが確認できますね。
同一セッション内での処理を実行する場合は一時テーブルが活用できると処理の高速化につながる可能性がありますので、NVMe 上の領域の活用は考慮すると良いかと。
データの分散方式
テーブル間 (テーブル to テーブル) のデータロードを行う場合に、何も考えずに ROUND_ROBIN を使用するのはやめましょう。
データローディング時もデータの分散方式は性能に大きく影響を与えます。
テーブル間のデータのローディングを実施する場合、CTAS を使うケースが多いかと思います。
CREATE TABLE T01 WITH (DISTRIBUTION = ROUND_ROBIN) AS SELECT * FROM LINEITEM3
CTAS も分散方式 (DISTRIBUTION) を設定することができますが、ROUND_RBOIN を前提にするのはやめた方がよいかと思います。
上記のクエリの場合、LINEITEM3 は、L_ORDERKEY という列でハッシュ分散をしているのですが、それを ROUND_ROBIN のテーブルにロードしています。
この時、どのようなデータの動きが発生しているかを次のクエリで確認してみます。
DECLARE @request_id nvarchar(100) = (SELECT TOP 1 request_id FROM sys.dm_pdw_exec_requests WHERE [label] = 'DataLoading' ORDER BY request_id 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 SELECT * FROM sys.dm_pdw_dms_workers WHERE request_id = @request_id ORDER BY step_index ASC SELECT * FROM sys.dm_pdw_dms_external_work WHERE request_id = @request_id SELECT * FROM sys.dm_pdw_resource_waits WHERE request_id = @request_id
DMS によって、RoundRobinMoveOperation が発生していることが確認できます。
これは「各ディストリビューションに対してデータの移動が発生している」ことを示しています。
Synapse のデータ操作は「データ移動を極力発生させずにデータを取得する」ことが、データアクセスの重要なポイントとなります。
- ROUND_ROBIN から ROUND_ROBIN テーブルのへのデータロード
- HASH から ROUND_ROBIN テーブルへのデータロード
- ROUND_ROBIN から HASH テーブルへのデータロード
- HASH テーブルから異なる HASH キーの HASH テーブルへのデータロード
このようなデータロードを実行すると DMS によるデータ移動が発生し、アクセス効率が著しく低下します。
テーブル間で高速にデータロードを行う場合には、
- HASH テーブルから同一の HASH キーの HASH テーブルへのデータロード
を心掛ける必要があります。
先ほどのクエリであれば、LINEITEM3 は L_ORDERKEY でハッシュ分散されていますので、クエリとしては次のようにすることで高速データローディングを行うことができます。
CREATE TABLE T01 WITH (DISTRIBUTION = HASH(L_ORDERKEY)) AS SELECT * FROM LINEITEM3 OPTION (LABEL = 'DataLoading')
同一データ型によるハッシュ分散であり、格納されている値が同一値であれば (一般的には結合に使用する項目はこの傾向になると思います)、RoundRobinMoveOperation / shuffleMoveOperation によるデータの移動は発生せずに同一のディスとリビュージョン上の INSERT 処理が行われ高速にデータローディングを行うことができます。(データ移動が発生した場合と比較すると倍以上、性能の違いが出てきます)
CTAS は HEAP / 一時テーブルと組み合わせることもできますので、上記のクエリは次のようなクエリとしても実行することができます。
CREATE TABLE #T01 WITH (HEAP, DISTRIBUTION = HASH(L_ORDERKEY)) AS SELECT * FROM LINEITEM3 OPTION (LABEL = 'DataLoading')
今回はテーブルを右から左に流していますが、小さなテーブルと JOIN をしてデータを作成する場合は、小さなテーブルを REPLICATE にして、データの移動が発生しないようにすることも重要です。
小さなテーブルだからと言って、標準的な ROUND_ROBIN の分散としていると、そのテーブルを結合に使用したクエリでは、小さなデータサイズのテーブルがに起因して、DMS による大量データの移動が発生する可能性があります。
レプリケートテーブルの設計方針については、Synapse SQL プールでレプリケート テーブルを使用するための設計ガイダンス に記載されていますが、
データの偏り (Skew) の発生状況を意識する
同一のハッシュ分散キーを使用したテーブル間のデータ移動は高速ですが、ハッシュ分散を使用した場合「ディストリビューターにデータの偏りが発生していないか」を意識することが重要です。
ラウンドロビン分散であれば、投入するデータを各ディストリビューションに均等に分散を行いますが、ハッシュ分散の場合はハッシュ分散の場合は分散に使用したキーのハッシュ値で格納するディストリビューションを決定しますので、指定した列のバリエーションによっては、60 に分散されているデータベースに偏りが発生する可能性があります。
これについては、Azure Synapse Analytics で専用 SQL プールを使用して分散テーブルを設計するためのガイダンス に記載されています。
「データ投入後に列ストアインデックスの状態」を確認することについては、上述しましたが、「データ投入後に各ディストリビューションの使用状況がどのようになっているか」を確認することも、ローディングのスループットを向上させるためには重要です。
60 のデータベースにデータがどのように分散されているかは、次のようなクエリで取得することができます。
select
object_name(tm.object_id) AS object_name,
ps.reserved_page_count,
ps.row_count,
ps.pdw_node_id,
ps.distribution_id
from
sys.pdw_permanent_table_mappings AS tm
INNER JOIN sys.pdw_nodes_tables AS nt
ON nt.name = tm.physical_name
INNER JOIN sys.dm_pdw_nodes_db_partition_stats AS ps
ON ps.object_id = nt.object_id and ps.distribution_id = nt.distribution_id
where
tm.object_id = OBJECT_ID('L2')
order by
distribution_id asc
各データベース (ディストリビューション) にデータが均等に配置されているのであれば、選択したハッシュ分散キーは「データの分散を均等に行うためには適しているキー」であると言えるかと思います。
データに偏り (データベース毎に使用状況が著しく異なる) が発生している場合は、ハッシュ分散キーを見直してみるとテーブル間のデータローディングのスループットの向上につながることがあります。
上記の観点で決定したハッシュ分散キーは、「選択したハッシュ分散キーはデータローディングに適している」と言えるかもしれませんが、これは「データ検索時、特にテーブル間の結合に適しているハッシュ分散キーではない」可能性があるかもしれません。
「どの処理に重きを置くか」によってハッシュ分散キーの選択の戦略も変わってきますので、最終的な分散キーは、いくつかのワークロードの検証を行ったうえで慎重に決定する必要があります。
2021/6 時点の Dedicated SQL Pool ではハッシュ分散キーは次のような制約があることも意識しておく必要があるかと。
- ハッシュ分散キーは単一列でのみ指定可能
- 複数の列を指定したハッシュ分散は指定できないため、複数の列のデータを分散キーに使用したい場合は、各列を結合した列を新規に作成して、それを分散キーに指定する
統計情報の更新の必要性
Dedicated SQL Pool の統計情報の動作については、Synapse SQL の統計 に記載されています。
Dedicated SQL Pool の統計情報の動作としては、次のような設定となっており、自動作成は有効 / 無効を変更することが可能ですが、自動更新については、現時点では変更することはできなかったかと思います。
- 統計情報の自動作成 : 有効
- 統計情報の自動更新 : 無効
CTAS でデータローディングを実施すると、最後に次のようなクエリが実行され、データローディング直後の状態で初期の統計情報が自動的に作成されていることが確認できます。(ぱっと見は投入したデータ全件で初期の統計情報を作成しているように見えますね)
この挙動は新規にインデックスを追加した場合も同様です。
UPDATE STATISTICS [DWDB].[dbo].[T01] WITH ROWCOUNT = 59986052, PAGECOUNT = 634032
この統計情報の自動作成についてはインデックスキーではない列に対して、初回アクセス時の自動的な列統計の作成 (_WA_sysxxxx の統計情報) についても実施されます。(こちらについては、既定のサンプリングを使用して作成しているように見えますが)
「統計情報」ですが、基本的な利用方法としては「クエリコンパイル時に実行プランを作成する際の列統計 (どのような傾向のデータが格納されているか) として使用」するものとなり、生成されるクエリの実行プランに影響を与えるものとなります。
CTAS のワークロードについては、テーブルの新規作成ですので、基本的に自動作成によって作成される統計情報で対応ができているような気がしています。
「CTAS 後にデータを追加投入」した場合には、追加投入したデータを統計情報に含めることで、投入されたデータを考慮した実行プランの生成を行うことができます。
「どのタイミングで作成された統計情報を使用しているか」を意識しておくと、適切な統計情報の更新タイミングが検討できるのではないでしょうか。
ワークロード管理
構築後の初期状態では、管理者ユーザーのみが存在している状態になるかと思います。
初期の検証を実施する際には、この管理者ユーザーを使用して検証を実施していることも多いのではないでしょうか？
管理者のユーザーで実行したとしても、初期状態では、クエリは動的リソースクラスの「smallrc」のリソースクラスで実行が行われます。
Dedicated SQL Pool では、ワークロード管理によって利用可能なメモリの制限が異なり、複雑なクエリになると、ワークスペースのメモリが大量に必要となり、同じ DWU 内でもより上位のワークロードに割り当てる必要が出てくる可能性があります。
しかし、単純なテーブル間のデータローディングでは、メモリはあまり必要としないケースがありますので、「より上位のワークロードに割り当てる = 性能が向上する」とはならない可能性があることも考慮しておく必要があるかと。
ワークロード管理については、次の情報からメモリの使用状況等を確認できます。
実行されているクエリをどのワークロード / リソースクラスで実行されているかを、「sys.dm_pdw_exec_requests」から確認しておくのも良いのではないでしょうか。
Dedicated Sql Pool は同時実行スロットを管理する必要がありますので、並列のデータローディングでスロットを消費すると、他の処理に影響が出る可能性もありますので。
また、tempdb の使用状況についても意識しておくことが重要です。
Dedicated Sql Pool では、データの移動が発生すると tempdb にデータを書き込んでそれを利用するという動作が行われ、結構な頻度で tempdb が使用されます。
tempdb の使用状況の確認方法については、tempdb を監視する に記載されており、利用可能な tempdb のサイズの上限については、ワークロードの管理 に記載されています。
Dedicated SQL Pool で発生したエラーについては、「sys.dm_pdw_errors」から取得することができますので、tempdb のサイズ関係のエラーが出ていた場合は、処理対象のデータの分割や、より上位の DWU への変更が必要になるかもしれませんね。
まとめ
データの取得を行う場合、データの移動が極力発生しない、テーブル構造にすることが重要ですが、これはテーブル間のデータローディングでも考え方は同じです。
Dedicated SQL Pool に対してデータのアクセスをする場合には、データがどのように取得されているかを意識することで、どこを改善すればよいかの検討につながるかと思います。
[…] https://blog.engineer-memo.com/2021/06/13/dedicated-sql-pool-%e3%81%a7%e3%83%87%e3%83%bc%e3%82%bf%e3… […]
【後で読みたい！】Dedicated SQL Pool でデータローディングを最適化するときの基本アプローチ | Tak's Bar
13 6月 21 at 20:19