Synapse SQLプールの性能を考える～データの再分散（DMS）編～

Synapse SQLプールは性能に関してよく直面する「データの再分散」に関して調査と検証しましたので記載します。

データの再分散がおこる理由

Synapse SQLは60のディストリビューションにそれぞれ分散されてデータが配置されます。このことからテーブルの事を「分散テーブル」と呼ばれることもあるようです。60のディストリビューションに分散してデータを配置することにより超並列処理（MPP）を可能にしています。

一方で、60のディストリビューションにデータを分散して格納している為、クエリを実行する際の「集計」や「結合」の処理の際に、データの再分散を行わなければならない場合があります。これが「データの再分散（DMS）」であり、非常にコストの高い処理であるとされています。

例①集計処理

「データの再分散」の代表的な例の１つに、クエリ内でGROUP BYなどにより集計した時があります。

例えばラウンドロビン分散で格納されている以下のORDERSテーブルで考えます。

CREATE TABLE ORDERS  (
	O_ORDERKEY       INTEGER NOT NULL,
    O_CUSTKEY        INTEGER NOT NULL,
    O_ORDERSTATUS    CHAR(1) NOT NULL,
    O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
    O_ORDERDATE      DATE NOT NULL,
    O_ORDERPRIORITY  CHAR(15) NOT NULL,
    O_CLERK          CHAR(15) NOT NULL,
    O_SHIPPRIORITY   INTEGER NOT NULL,
    O_COMMENT        VARCHAR(79) NOT NULL
)
WITH(
	DISTRIBUTION = ROUND_ROBIN
);

ORDERSテーブルには1,500,000件のデータが格納されており、ラウンドロビン分散の為１つのディストリビューション当たり、25,000件ずつデータが格納されることになります。

上記テーブルに対して、O_CUSTKEY毎のO_TOTALPRICEを合計（SUM）するような以下のSQLを実行します。

select O_CUSTKEY,sum(O_TOTALPRICE) from ORDERS group by O_CUSTKEY;

SQLを実行するとSynapse SQLプールでは60のディストリビューションのうち、どのディストリビューションに何のO_CUSTKEYが格納されているかわからない為、内部的にO_CUSTKEYでデータの再分散を行おうとします。この動作がデータの再分散（DMS）と呼ばれる動作になります。

実際のプランでも以下の通り、データの再分散を表す「SHUFFLE_MOVE」が表示されている事がわかります。

これを回避するために以下の通りO_CUSTKEYをハッシュキーとしたハッシュ分散でORDERSテーブルを再作成して同じSQLを実行してみます。
ハッシュ分散はハッシュキーに指定したデータに基づいてデータの分散を行う分散方式です。今回の例ではO_CUSTKEYをハッシュキーとしている為、同じO_CUSTKEYのデータは同じディストリビューションに配置されることになります。つまり、O_CUSTKEYでGroup byする際に、違うディストリビューションの値を必要としない為、データの再分散をする必要がありません。なので、データ再分散を回避できます。

CREATE TABLE ORDERS  (
	O_ORDERKEY       INTEGER NOT NULL,
    O_CUSTKEY        INTEGER NOT NULL,
    O_ORDERSTATUS    CHAR(1) NOT NULL,
    O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
    O_ORDERDATE      DATE NOT NULL,
    O_ORDERPRIORITY  CHAR(15) NOT NULL,
    O_CLERK          CHAR(15) NOT NULL,
    O_SHIPPRIORITY   INTEGER NOT NULL,
    O_COMMENT        VARCHAR(79) NOT NULL
)
WITH(
	DISTRIBUTION = HASH(O_CUSTKEY)
);

ORDERSテーブルをハッシュ分散で再作成した後のプランは以下の通りとなり、データの再分散が発生しなくなったことがわかります。また、コストも小さくなっている事を確認出来ると思います。（ハッシュ分散にした後はコストが低くなり0と表示されています。）

例②テーブルの結合

データの再分散はテーブルの結合時にも発生します。先ほどのORDERSテーブルに加えて、CUSTOMERテーブルも作成し、ORDERSとCUSTOMERを結合するSQLを考えます。（ORDERSはO_CUSTKEYにてハッシュ分散しています。）

CREATE TABLE CUSTOMER (
    C_CUSTKEY     INTEGER NOT NULL,
    C_NAME        VARCHAR(25) NOT NULL,
    C_ADDRESS     VARCHAR(40) NOT NULL,
    C_NATIONKEY   INTEGER NOT NULL,
    C_PHONE       CHAR(15) NOT NULL,
    C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
    C_MKTSEGMENT  CHAR(10) NOT NULL,
    C_COMMENT     VARCHAR(117) NOT NULL
)
WITH(
	DISTRIBUTION = ROUND_ROBIN
);

実行するSQLは以下の通りです。

select
	C_NAME
	,sum(O_TOTALPRICE)
from
	ORDERS_HASH_CUSTKEY O
	inner join CUSTOMER C on O_CUSTKEY = C_CUSTKEY
group by C_NAME;

SQLのプランは以下の通りです。

まず、ORDERSテーブルとCUSTOMERを結合させるために、結合キーとなっているC_CUSTKEYにてCUSTOMERテーブルのデータを再分散している事がわかります。その後、集計するために、C_NAMEにて再びデータの再分散を行っている事が確認出来ます。

では、C_CUSTKEYをハッシュキーにしたハッシュ分散でCUSTOMERテーブルの再作成を行い、再び同じSQLを実行します。

CREATE TABLE CUSTOMER (
    C_CUSTKEY     INTEGER NOT NULL,
    C_NAME        VARCHAR(25) NOT NULL,
    C_ADDRESS     VARCHAR(40) NOT NULL,
    C_NATIONKEY   INTEGER NOT NULL,
    C_PHONE       CHAR(15) NOT NULL,
    C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
    C_MKTSEGMENT  CHAR(10) NOT NULL,
    C_COMMENT     VARCHAR(117) NOT NULL
)
WITH(
	DISTRIBUTION = HASH(C_CUSTKEY)
);

CUSTOMERテーブルをハッシュ分散で再作成した後、SQLの実行計画を取得すると以下のようになります。

先ほどあったC_CUSTKEYでのデータの再分散がなくなり、コストも、23.99・・・→10.79・・・と下がっている事がわかります。
これは結合キーであるORDERSのO_CUSTKEYとCUSTOMERのC_CUSTKEYが共にハッシュキーでハッシュ分散されており、それぞれのテーブルの結合対象のデータが同じディストリビューション上に分散されている為、他のディストリビューションのデータを必要とせず、結果、データの再分散を回避できている為です。
また、今回はハッシュ分散のテーブル同士の結合でしたが、一方がレプリケートによる分散でも同様の効果を得ることができます。
テーブルの分散方式の種類は以下を参考にしてください。
Synapse SQLプールのデータの分散配置

さいごに

今回はデータの再分散（DMS）の代表的な２パターンを紹介しましたが、次の機会には他のパターンも記載したいと思います。