#60に分割されたディストリビューションにデータを配置
Azure Synapse Analytics SQLプールでは60のディストリビューション（ディスク）が用意されており、そこにデータを分散して配置しています。
分散の方式は以下の３種類です。
・特定のキーでHASH値を計算し、データを配置するディストリビューションを決定するHASH分散
・データをラウンドロビン形式ですべてのディストリビューションに均等配置するラウンドロビン分散
・テーブル内のすべてのデータをすべてのコンピュートノードに配置するレプリケーティッドテーブル（ALL分散）
Synapse Analytics SQLプールでは特にHASH分散が重要で、テーブルの結合、Group byなどを行う際に、正しくHASH分散が出来ていると処理の高速化が可能になります。
一方で、HASH分散を指定した際に、ロードされるデータによっては偏りがする場合があります。データの偏りは以下の状況で発生する可能性があります。
・カーディナリティの低いカラムをHASH_KEYにしてしまった。
・HASH_KEYに指定したカラムの特定の値のみ件数が多い。（値によって件数に偏りがある）
データの偏りが発生すると60あるディストリビューションそれぞれに格納されるデータ量に差が生じてしまいます。
なので、データの偏りが発生すると、ディストリビューション毎にデータを読み込むデータ量に差が生まれ、結果性能を発揮できなくなる可能性があります。
 |
|---|
マニュアル上では10%以上のデータの偏りが発生する場合、HASH_KEYを見直すか、ラウンドロビン分散を検討した方がいいとなっています。
#データの偏りの確認方法
Azure Synapse Analytics SQLプールではテーブル毎に以下の方法でデータの偏りを確認する事が出来ます。
--データの偏りを確認
DBCC PDW_SHOWSPACEUSED('<テーブル名>');
結果
60のディストリビューション毎に件数や、格納に使用されている使用量などを確認可能です。
| 列名 | 内容 |
|---|---|
| rows | 該当のディストリビューションに格納されている件数 |
| reserved_space | 該当のディストリビューションでオブジェクトに予約されている合計領域(KB) |
| data_space | 該当のディストリビューションでデータ格納に使用されている領域(KB) |
| index_space | 該当のディストリビューションでインデックスで使用されている領域(KB) |
| unused_space | 該当のディストリビューションで予約済み且つ使用されていない領域(KB) |
| pdw_node_id | 該当のディストリビューションで使用されているコンピュートノードの番号 |
| distribution_id | 該当のディストリビューションの番号（1~60） |
※VIEW SERVER STATE 権限が必要
以下のSQLでデータベース全体を確認する事も可能です。
DBCC PDW_SHOWSPACEUSED
#検証
今回はデータの偏りが発生する事で、どれくらいの性能差が出るのか確認しました。
LINEITEM_TEST1
⇒分散キーをカーディナリティの高いカラムで指定。
LINEITEM_TEST2
⇒分散キーをカーディナリティを1のカラム（1種類しか入っていない）で指定。
この時に、クエリにどれくらいの影響があるのか試してみました。
##検証用テーブルの作成
以下のSQLで検証用のテーブルを作成しました。
--LINEITEM_TEST1 の作成
CREATE TABLE LINEITEM_TEST1 WITH (
DISTRIBUTION = HASH(HASH_KEY)
,CLUSTERED COLUMNSTORE INDEX
) AS
SELECT
*
,L_ORDERKEY AS HASH_KEY
FROM LINEITEM
where L_SHIPDATE >= '1993/1/1' and L_SHIPDATE < '1996/1/1'
;
--LINEITEM_TEST2 の作成
CREATE TABLE LINEITEM_TEST2 WITH (
DISTRIBUTION = HASH(HASH_KEY)
,CLUSTERED COLUMNSTORE INDEX
) AS
SELECT
*
,100000000 AS HASH_KEY
FROM LINEITEM
where L_SHIPDATE >= '1993/1/1' and L_SHIPDATE < '1996/1/1'
;
CTASの基になっているLINEITEMテーブルは以下の記事を参考に作成しています。
TPC-Hを使ってテスト環境を作成する（Synapse SQLプール）
※スケールファクターはSF100で作成。
##検証用のテーブルの偏りの確認
以下のSQLを実行し、データの偏りを確認します。
--データの偏りを確認
DBCC PDW_SHOWSPACEUSED('LINEITEM_TEST1');
DBCC PDW_SHOWSPACEUSED('LINEITEM_TEST2');
結果
LINEITEM_TEST1は60のディストリビューションにまんべんなくデータが入っていますが、LINEITEM_TEST2は21のディストリビューションに全件データが入ってしまいました。
##検証用のSQLを実行
今回はTPCHの3番クエリを元にSQLを実行し実行時間を測定していました。
--LINEITEM_TEST1を使ったクエリ時間の計測
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
/* TPC_H Query 3 - Shipping Priority */
SELECT TOP 10
L_ORDERKEY
,SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE
,O_ORDERDATE
,O_SHIPPRIORITY
FROM
CUSTOMER
,ORDERS
,LINEITEM_TEST1 --LINEITEM_TEST1を指定
WHERE
C_MKTSEGMENT = 'BUILDING'
AND C_CUSTKEY = O_CUSTKEY
AND L_ORDERKEY = O_ORDERKEY
AND O_ORDERDATE < '1995-03-15' AND L_SHIPDATE > '1995-03-15'
GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY
ORDER BY REVENUE DESC, O_ORDERDATE
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO
--LINEITEM_TEST2
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
/* TPC_H Query 3 - Shipping Priority */
SELECT TOP 10
L_ORDERKEY
,SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE
,O_ORDERDATE
,O_SHIPPRIORITY
FROM
CUSTOMER
,ORDERS
,LINEITEM_TEST2 --LINEITEM_TEST2を指定
WHERE
C_MKTSEGMENT = 'BUILDING'
AND C_CUSTKEY = O_CUSTKEY
AND L_ORDERKEY = O_ORDERKEY
AND O_ORDERDATE < '1995-03-15' AND L_SHIPDATE > '1995-03-15'
GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY
ORDER BY REVENUE DESC, O_ORDERDATE
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO
結果
| 走行時間(ms) | |
|---|---|
| LINEITEM_TEST1使用 | 41603 |
| LINEITEM_TEST2使用 | 64106 |
やはり、データの偏りの発生していない、LINEITEM_TEST1の方が、高速に処理が出来ています。
#最後に
今回の検証ではデータの偏りが100%ときわめて非現実的な形での検証としましたが、もう少し現実的なデータの偏りで検証をしてみたいと思います。さらにLINEITEM_TEST1では、データ量が少ないので、一部圧縮が効いていない可能性もあり、あまりちゃんとした検証とならなかったかもしれません。
ただ、データの偏りが発生すると、性能には少なからず影響を及ぼす可能性があるので注意が必要です。
ここで、マニュアルの表記通りの10%以上の偏りがある、テーブルの一覧を取得するSQLを記載しておきます。
--VIEWの作成
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]
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
two_part_name
,partition_nmbr
,max(row_count * 1.000) AS MAX_ROWS
,min(row_count * 1.000) AS NIN_ROWS
,ROUND((max(row_count * 1.000) - min(row_count * 1.000))/max((row_count+1) * 1.000),4) AS DATA_SKEW
from dbo.vTableSizes
group by
two_part_name
,partition_nmbr
order by
DATA_SKEW desc
;
上記の結果でDATA_SKEW列が0.1以上の物が対象となります。