Synapse Analytics

Azure Synapse Analytics 専用SQLプール上の分散テーブルのData Skewを特定して改善する

こんばんは。今日は、Azure Synapse Analyticsの分散テーブルのSkewを特定して改善する方法を試してみたので、メモを残しておきたいと思います。

それではまいります。

Contents

Synapse Analyticsのデータ分散のしくみ

まずはSynapaseの分散テーブルのしくみについて復習しておきたいと思います。

以下、公開されているSynapseのアーキテクチャ図です。

専用 SQL プール (旧称 SQL DW) アーキテクチャ – Azure Synapse Analytics | Microsoft Docs
  • Synapseのデータは内部的にAzure Storageに保存されている
  • データはパフォーマンス最適化のため、ディストリビューションにシャード化されている（分散テーブルになっている）
  • このときのシャーディングのオプションとして、ハッシュ分散・ラウンドロビン・レプリケートの３つが選択可能
  • ディストリビューションは60個あり（固定）、各コンピューティングノードには一つまたは複数のディストリビューションが割り当てられる（コンピューティングノードが１つの場合は、60個のディストリビューションが割り当てられ、最大の60ノードの場合は、１個のディストリビューションが割り当てられる）

このデータを分散する際、特にハッシュ分散を選択すると、分散キーに指定した値の分布によってディストリビューション毎に格納されるデータ量の偏りが発生し得ます。

公式ドキュメントによると、10%以上の偏り（Data Skew）（＝最大行数と最小行数のディストリビューションの行数差が、最大行数のディストリビューションの行数の10%以上）があると、クエリパフォーマンスに影響を及ぼし得る、との説明があります。

ハッシュ分散テーブルにデータを読み込んだ後は、60 のディストリビューションにどの程度均等に行が分散されているかを確認します。 ディストリビューションあたりの行数の変化が 10 % までであれば、パフォーマンスに顕著な影響はありません。

分散テーブルの設計ガイダンス – Azure Synapse Analytics | Microsoft Docs

なぜパフォーマンスに影響が出るか？こちらに説明があります。

最適なパフォーマンスを得るために、すべてのディストリビューションでほぼ同じ行数を含むようにする必要があります。 1 つまたは複数のディストリビューションに含まれる行数が不均衡な場合、並列クエリが一部のディストリビューションで他よりも先に終わります。 クエリは、すべてのディストリビューションで処理が終了するまで完了できないので、各クエリは単に最も処理が遅いディストリビューションと同じ速度になります。

分散テーブルの設計ガイダンス – Azure Synapse Analytics | Microsoft Docs

ので、パフォーマンスの観点からData Skewを特定して改善することは重要になってくるわけです。

分散テーブルのData Skewを特定して改善する

それでは、実際に分散テーブルのData Skewを特定して改善する手順を再現してみたいと思います。

この手順については、以下の公式ドキュメントにまとめられているので、こちらも併せて確認ください。

分散テーブルの設計ガイダンス – Azure Synapse Analytics | Microsoft Docs

分散テーブルの作成

最初に、Data Skewのある分散テーブルを作っておきます。

以下のようなレイアウトの簡単なテーブルに、1000万行のデータ（Blob Storageに配置したCSVファイルから取り込み）を追加しておきます。

データはHASH分散でSynapseに取り込むのですが、HASHキーとする”data”列の値が偏りのある状態（ほとんどの行で同じ値）にしています。

## 読み込み先テーブルの作成
CREATE TABLE dbo.TestTable_Hash
(   
    [id]              int                   NOT NULL,
    [data]            varchar(255)          NOT NULL
)
WITH
(       
    CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([data])
);
COPY INTO [dbo].[TestTable_Hash]
FROM 'https://＜ストレージアカウント名＞.blob.core.windows.net/＜ファイルのパス＞'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = ',',
    FIELDQUOTE = '',
    FIRSTROW = 2
)
OPTION (LABEL = 'COPY : Load [dbo].[TestTable_Hash]');

読み込んだデータのサンプル。（61行目以降はdata列がすべて同じ値）

データスキュー発生の確認

それではさっそくData Skewの状況を確認していきます。

分散テーブルの設計ガイダンス – Azure Synapse Analytics | Microsoft Docs

DBCC PDW_SHOWSPACEUSED('dbo.TestTable_Hash_Skew');

この構文の詳細はこちら。

DBCC PDW_SHOWSPACEUSED (Transact-SQL) – SQL Server | Microsoft Docs

このコマンドを使うと、ディストリビューション毎の情報が得られます。例えば上のキャプチャの通り、指定したオブジェクトについて、各ディストリビューション毎に含まれる行数が分かります。

Synapseワークスペース上でT-SQLを実行している場合、以下のように行数の偏りを”グラフ”で可視化してやってもわかりやすいですね。

なお、10%を超える偏り、といったより厳密な検査を行うには、dmvが利用できます。

公式Docにも「DBCC コマンドを使用できるのは極めて限定的です。 動的管理ビュー (DMV) には、DBCC コマンドよりも詳しい情報が表示されます。 」とあります。
テーブルの設計 – Azure Synapse Analytics | Microsoft Docs

以下のコマンドを実行すればOKです。

## ビューの作成
CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
 GETDATE()                                                             AS  [execution_time]
, DB_NAME()                                                            AS  [database_name]
, s.name                                                               AS  [schema_name]
, t.name                                                               AS  [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name)                              AS  [two_part_name]
, nt.[name]                                                            AS  [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL))     AS  [node_table_name_seq]
, tp.[distribution_policy_desc]                                        AS  [distribution_policy_name]
, c.[name]                                                             AS  [distribution_column]
, nt.[distribution_id]                                                 AS  [distribution_id]
, i.[type]                                                             AS  [index_type]
, i.[type_desc]                                                        AS  [index_type_desc]
, nt.[pdw_node_id]                                                     AS  [pdw_node_id]
, pn.[type]                                                            AS  [pdw_node_type]
, pn.[name]                                                            AS  [pdw_node_name]
, di.name                                                              AS  [dist_name]
, di.position                                                          AS  [dist_position]
, nps.[partition_number]                                               AS  [partition_nmbr]
, nps.[reserved_page_count]                                            AS  [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count]                    AS  [unused_space_page_count]
, nps.[in_row_data_page_count]
    + nps.[row_overflow_used_page_count]
    + nps.[lob_used_page_count]                                        AS  [data_space_page_count]
, nps.[reserved_page_count]
 - (nps.[reserved_page_count] - nps.[used_page_count])
 - ([in_row_data_page_count]
         + [row_overflow_used_page_count]+[lob_used_page_count])       AS  [index_space_page_count]
, nps.[row_count]                                                      AS  [row_count]
from
    sys.schemas s
INNER JOIN sys.tables t
    ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
    ON  t.[object_id] = i.[object_id]
    AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
    ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
    ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
    ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
    ON  nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
    ON  nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
    ON nt.[object_id] = nps.[object_id]
    AND nt.[pdw_node_id] = nps.[pdw_node_id]
    AND nt.[distribution_id] = nps.[distribution_id]
    AND i.[index_id] = nps.[index_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
    ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
    ON cdp.[object_id] = c.[object_id]
    AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
   [execution_time]
,  [database_name]
,  [schema_name]
,  [table_name]
,  [two_part_name]
,  [node_table_name]
,  [node_table_name_seq]
,  [distribution_policy_name]
,  [distribution_column]
,  [distribution_id]
,  [index_type]
,  [index_type_desc]
,  [pdw_node_id]
,  [pdw_node_type]
,  [pdw_node_name]
,  [dist_name]
,  [dist_position]
,  [partition_nmbr]
,  [reserved_space_page_count]
,  [unused_space_page_count]
,  [data_space_page_count]
,  [index_space_page_count]
,  [row_count]
,  ([reserved_space_page_count] * 8.0)                                 AS [reserved_space_KB]
,  ([reserved_space_page_count] * 8.0)/1000                            AS [reserved_space_MB]
,  ([reserved_space_page_count] * 8.0)/1000000                         AS [reserved_space_GB]
,  ([reserved_space_page_count] * 8.0)/1000000000                      AS [reserved_space_TB]
,  ([unused_space_page_count]   * 8.0)                                 AS [unused_space_KB]
,  ([unused_space_page_count]   * 8.0)/1000                            AS [unused_space_MB]
,  ([unused_space_page_count]   * 8.0)/1000000                         AS [unused_space_GB]
,  ([unused_space_page_count]   * 8.0)/1000000000                      AS [unused_space_TB]
,  ([data_space_page_count]     * 8.0)                                 AS [data_space_KB]
,  ([data_space_page_count]     * 8.0)/1000                            AS [data_space_MB]
,  ([data_space_page_count]     * 8.0)/1000000                         AS [data_space_GB]
,  ([data_space_page_count]     * 8.0)/1000000000                      AS [data_space_TB]
,  ([index_space_page_count]  * 8.0)                                   AS [index_space_KB]
,  ([index_space_page_count]  * 8.0)/1000                              AS [index_space_MB]
,  ([index_space_page_count]  * 8.0)/1000000                           AS [index_space_GB]
,  ([index_space_page_count]  * 8.0)/1000000000                        AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;
## ビューをクエリ
select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
order by two_part_name, row_count
;

すると、Skewのあるテーブルが検出されて一覧に出てきました。これで機械的なチェックができそうです。

テーブルの分散オプションを確認する

ちなみに、Data Skewが確認できたら、現状どういう分散方式で、分散のキーは何だったかを確認したくなるかと思います。これは以下コマンドで確認できます。

前提：上のステップでビューvTableSizesを作成済であること。

SELECT
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
,    COUNT(distinct partition_nmbr) as nbr_partitions
,    SUM(row_count)                 as table_row_count
,    SUM(reserved_space_GB)         as table_reserved_space_GB
,    SUM(data_space_GB)             as table_data_space_GB
,    SUM(index_space_GB)            as table_index_space_GB
,    SUM(unused_space_GB)           as table_unused_space_GB
FROM
    dbo.vTableSizes
GROUP BY
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
ORDER BY
    table_reserved_space_GB desc
;

するとこんな感じで、テーブル毎に、分散方式や分散キー等の情報が確認できます。

Data Skewの解消

さて、Data Skewを改善する方法ですが、適切な分散キーを選びなおして、データを分散しなおす必要があります。

実は一度作った分散テーブルの分散キーを後から変更することはできないようなので、以下に紹介されているCTAS T-SQLを使って、別の分散キーを指定した全く新しいテーブルを作り直す必要があるようです。

分散テーブルの設計ガイダンス – Azure Synapse Analytics | Microsoft Docs

CTASの説明はこちら。

CREATE TABLE AS SELECT (Azure Synapse Analytics) – SQL Server | Microsoft Docs

今回の場合はこんな感じのT-SQLになります。値の偏りの大きい”data”列ではなく、すべての値がユニークな”id”列をハッシュキーに選びなおしています。

CREATE TABLE [dbo].[TestTable_Hash_Skew_Redistributed]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([id])
    )
AS
SELECT  *
FROM    [dbo].[TestTable_Hash_Skew]
OPTION  (LABEL  = 'CTAS : TestTable_Hash_Skew')
;

実際に実行してみると、COPYステートメントでCSVから１からデータを読み込む時間の半分くらいで実行が完了しました。

この再作成したテーブル”TestTable_Hash_Skew_Redistributed”に対してディストリビューションの状況を確認してみると、今度は各ディストリビューションに均等にデータが分散したことが確認できました！めでたしめでたし。

DBCC PDW_SHOWSPACEUSED('dbo.TestTable_Hash_Skew_Redistributed');

以上、Synapse 専用SQLプールでData Skewを特定して改善する方法を試してみたメモでした。

ご参考になりましたら幸いです。

おしまい

この記事を気に入っていただけたらシェアをお願いします！

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

ABOUT US
Yuu113
初めまして。ゆうたろうと申します。 兵庫県出身、東京でシステムエンジニアをしております。現在は主にデータ分析、機械学習を活用してビジネスモデリングに取り組んでいます。 日々学んだことや経験したことを整理していきたいと思い、ブログを始めました。旅行、カメラ、IT技術、江戸文化が大好きですので、これらについても記事にしていきたいと思っています。