Azure SQL DWのパフォーマンス測定｜13億行のデータで検索と集計処理時間を計測【2015.09.29更新】：Microsoft Azure SQL Data Warehouse を使ってみた(2)

TAG : azure | Microsoft Azure SQL DW | Tech & Science | クラウドサービス
AUTHOR : ギックス
POSTED : 2015.09.04 21:57

1 Azure SQL Data Warehouseのキャッシュ機能がスゴイ！Redshiftと性能比較した

Azure SQL Data Warehouseのキャッシュ機能がスゴイ！Redshiftと性能比較した

———

2015.09.29. 更新：Amazon Redshift のソートキーが効かない事象に対して、対策を講じ、再計測を行いました。

———

前回は、Azure SQL Data Warehouse(Azure SQL DW)のDB環境構築とインポート処理について説明しました。今回は、前回登録した約13億行のデータを使って、検索と集計のSQL処理速度を計測しました。

Azure SQL Data Warehouseで処理速度計測

今回、処理速度で使用したSQLは下記の3本です。検索や集計で使用しているテーブル項目には、テーブルのインデックスを付けています。

--1.件数
select count(kokyaku_no) from t_uriage

--2.検索
select * from t_uriage where kokyaku_no = '123456789';

--3.集計
select use_ymd, sum(uriage) from t_uriage where kokyaku_no = '123456789' group by use_ymd;

--1.件数

select count(kokyaku_no) from t_uriage

--2.検索

select * from t_uriage where kokyaku_no = '123456789';

--3.集計

select use_ymd, sum(uriage) from t_uriage where kokyaku_no = '123456789' group by use_ymd;

この時、Azure SQL Data Warehouseのパフォーマンスは、「100DWU」を使用しており、DBの最小設定です。また、検索と集計のSQL命令で対象となるデータは、600件(全体:約13億件)になるデータで検証しました。

検証では、それぞれのSQL命令に対して、3、4回計測して、計測結果とする予定でしたが、Azure SQL Data Warehouseのキャッシュ機能のためなのか、どんどん処理速度が速くなってしまいました。キャッシュクリアのSQL命令(DBCC DROPCLEANBUFFERS / DBCC FREEPROCCACHE)や、DBの削除＆リカバリによって、キャッシュのクリアを試みましたが、うまく機能しませんでした。そのため、これから記載する計測値は、ご参考までにお願いします。

件数：約190～230秒
検索：最初だけ97秒、それ以降は、18～37秒。キャッシュが効いていると数秒
集計：11～22秒。キャッシュが効いていると数秒

検索について、最初の処理時間を除けば、40秒以内で返ってくることになります。そして、検索のキャッシュが効きやすく、同じ検索条件の場合だけでなく、検索する文字情報を変えた場合も瞬時で結果が返ってきました。また、検索より集計が早い理由について、集計の方が、取得する項目が少ないため、単純な検索より早くなったと考えられます。(対象のテーブルのカラム数は15カラム(平均:約100バイト))

Amazon Redshiftで処理速度計測

Amazon Redshift(ds2.xlarge:1ノード)でも、Azure SQL Data Warehouseと同様のテーブル情報とSQL命令で処理速度を計測しました。また、Redshiftには、インデックスを付けることができませんので、代わりにソートキー(INTERLEAVED SORTKEY)を付けました。

件数：約90秒
検索：約11分30秒～約12分
集計：約170秒

Redshiftは、取得するカラム数が多くなると遅くなる傾向があるため、検索に非常に時間が掛かってしまいました。そして、今回のデータでは、なぜかソートキーが有効に働かず、ソートキーなしでも検索、集計の処理速度は、若干、遅くなる程度でした。

vacuum reindexを実行して再度処理速度計測(2015.09.29更新)

前回のRedshiftでの処理速度測定でソートキーがうまく働いていなかった件について、原因と解決方法が分かりましたので追記します。

ソートキーが働いていなかった原因は、検証テーブルの登録時にデータの抽出元テーブルから「insert into t_uriage select ～」でデータをコピーしたためでした。この登録方法では、「INTERLEAVED SORTKEY」のソートキーは更新されず、結果、ソートキーが効かなくなってました。そのため、対処方法として「vacuum reindex t_uriage」を実行して、ソートキーを作り直ししました。なお、「vacuum reindex」処理には、テーブルのデータ量に比例した処理時間が掛かるため、今回の作り直し処理に約8時間かかりました。

そして、以前、実行した同条件で再計測した結果が下記です。

件数：約58秒
検索：18～24秒。キャッシュが効いていると数秒
集計：約4秒。キャッシュが効いていると数秒

Redshiftのキャッシュは、同じ検索条件の場合のみに有効になるようです。そのため、検索条件の値を変えた場合、キャッシュが効く前の処理時間が掛かってしまいます。

Azure SQL Data Warehouseはインデックスを付けると早くなる

今回のデータでは、~~Azure SQL Data Warehouseの方が、Redshiftより処理速度が速い結果になりました。~~Redshiftの方が、Azure SQL Data Warehouseより集計処理が早いですが、単純な検索では同程度の性能でした。(2015.09.29更新) そして、今回、比較したAzure SQL Data Warehouseのパフォーマンスは、100DWUであるため、1か月の使用料金は、約$500＋記憶容量(BLOB)の料金になります。これは、Redshift(ds2.xlarge:1ノード)の約$850/月と比較するとコストパフォーマンスは良いようです。

今回、Azure SQL Data Warehouseの処理速度の検証で、検索対象のテーブル項目にインデックスを付けました。試しにインデックスなしで同様の検索を行うと、約15~17倍(0.5分→8分)の時間が掛かってしまいました。さらにキャッシュも効きませんので、性能の差は歴然でした。このことから、Azure SQL Data Warehouseは、インデックスを適切に付けることで、Redshift以上の処理性能を出すことが可能なことが分かりました。ただ、現在、Azure SQL Data Warehouseは、まだプレビュー版のため、ユーザー数が増えたとき、レスポンスが落ちる懸念点はあります。

次回は、Azure SQL Data Warehouseの処理速度以外の仕様について、検証したいと思います。

【連載：Microsoft Azure SQL Data Warehouse を使ってみた】

DB作成からデータインポートまで
Azure SQL DWのパフォーマンス測定｜13億行のデータで検索と集計処理時間を計測 (本稿)
すばやい一時停止とパフォーマンス変更でコストを削減できる
PolyBaseを使ったAzure SQL DWへの高速インポート～Azure Storageにデータをアップロードする～
PolyBaseを使ったAzure SQL DWへの高速インポート～図解で分かりやすく説明～
Azure SQL DWパフォーマンスチューニング(リソースクラス編)
Azure SQL DWパフォーマンスチューニング(ハッシュ分散編)