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

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

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

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

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

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

```SQL
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を実行します。

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

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

実際のプランでも以下の通り、データの再分散を表す「SHUFFLE_MOVE」が表示されている事がわかります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/881f6533-5614-ed06-7ae3-d159ddce5171.png)


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

```SQL
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と表示されています。）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/57011832-f760-4646-f8ab-bbce75f46c99.png)

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

```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 = ROUND_ROBIN
);
```

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

```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のプランは以下の通りです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/e8f022a8-9631-c3ee-2c1f-abdfaed33b6e.png)

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

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

```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の実行計画を取得すると以下のようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/c6f2be69-544c-0f80-42b7-e905d16002ef.png)

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

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