TechEDNAセッション：SQL Azureパフォーマンスの考察とトラブルシューティングまとめ
この投稿は、Tech ED NA 2011の「Understanding the Windows Phone Development Tools | Tech·Ed North America 2011 | Channel 9」セッションをざっくりとまとめ補足した投稿です。途中で力尽きたので、最後辺りはさっぱりしていますので、元ネタを参照した方がいいかと。
SQL Azureアーキテクチャ
- SQLデータベースは共有インフラ
- 一般的なハードウェアを使用した大規模分散クラスター
- スケーラブルな高可用性テクノロジーを提供
それぞれのSQL Azureデータベースは3つに多重化している
自動レプリケーションとフェイルオーバー - TDSリクエストをゲートウェイサービスが転送する
上の図は、物理マシンの簡易構成図です。SQL Azureデータベースは、仮想環境では無く物理マシンにホストされます。
物理マシン上にSQLインスタンスがあり、インスタンスの中にSQL DBがあります。SQL DBには複数ユーザのDBがホストされます。SQLインスタンスやSQL DBは、ユーザがSQL Azureを使用する際に認識することはできません。
例えば、私がDBを3つ作成した場合、1つは物理マシン4に作成されたとしも残りの2つは、物理マシン4かもしれない100、500などまったく別の物理マシンにホストされる可能性のほうが高いです。
膨大な数の物理マシンとDBを保持しているのがSQL Azureです。SQL Azureにリクエストを送信する場合、リクエスト先はSQL Azureゲートウェイサービスです。SQL Azureゲートウェイサービスが、ユーザDBに通信を割り振ります。
可用性と拡張性、レプリケーション、フェイルオーバー、ロードバランスを考慮し、必要に応じてDBのホスト先物理マシンを選択します。一度ホストされても、物理マシンの負荷状況に応じて、別の物理マシンに再配置をしなおすことがあります。
ユーザが認識しているDBサーバは、論理サーバになっています。ユーザDBは、裏側で自動的に異なる物理マシン上に常時3重化しています。3重化の仕組み上、2重化は同時にし、（コミット時に複製するので、複製できなければコミットできない）、3つ目は多少のタイムラグ（数ミリ秒なのか数十ミリ秒なのか数秒なのかは不明）があると思われます。
SQL Azureデータセンターと通信時間について
SQL Azureは、世界6か所にデータセンターがあります。
緑に塗られている箇所は、NW遅延が100ms以内のDCが2つ以上ある地域で、快適に使用することができます。赤いラインで囲まれているのが、NW遅延が200ms以内のDCが2つある地域です。
//日本は黄色なので、100ｍｓ以内のDCが1つある地域ですね。
NW遅延は、
ユーザとアプリケーション間
アプリケーションとSQL Azure DB間
の2つあります。
レスポンス時間は、上の式で求められます。
リクエストの送信時のNW遅延と、リクエスト結果の受信時のNW遅延があるので、NW遅延1とNW遅延2を足したものを倍にしています。それにSQL Azureデータベースでのクエリ実行時間を足したものが、レスポンスタイムになります。
最適化するには、次の項目に注意する必要があります。
- NW遅延1の最小化：大多数のユーザとSQL Azureデータセンタ間の距離が最も近いデータセンターを選択する
- NW遅延2の最小化：Windows Azureアプリケーションを同じデータセンターに配置する
- NWラウンドトリップを小さくする
NW遅延の計測方法
SQL Server Management Studio（SSMS)を使用して、NW遅延を計測することができます。SSMSを起動し、SQL Azureデータベースに接続します。
新しいクエリボタンをクリックして、クエリエディタを開きます。
ツールバーの「クライアント統計」を含めるをクリックします。
クエリに「SELECT 1」と入力し、実行します。
すると、「クライアント統計」と言うタブが表示されるので、そのタブを選択します。
ちなみに、実行都度情報を記録するので、複数回クエリを実行しておくと良いかと思います。ここでは7回実行しました。
こんな感じの統計情報を見ることができます。NW遅延を確認する場合は、下の方にある「時間統計」の項を参照します。列の一番右端に平均値が表示されます。
//日本から朝計測したもので、180msかかっていますねぇー。DCはアメリカを選択していたと思います。
計測結果
世界6か所から、6か所のDCに対して接続したときのNW遅延を計測した結果です。
東アジアの日本から使用する場合は、East AsiaかSouth-Asiaしか選択肢はなさそうですね。
リソース管理と複数テナント
マシン上のリソースを他のユーザのDBと共有しています。
- CPU、メモリ、データファイル/ログファイル
- tempdb、ワーカースレッド、ネットワーク、他の人のDB
同じマシーンにのっている別の人のDBがとても高負荷の処理をすると、性能に影響を受ける
そういった状況で、全ユーザが快適にSQL Azureを使用できるように、複数テナント管理機能を提供している。
- ロードバランサー
- スロットサービス（制限サービス）
ロードバランサー
- すべての物理マシンで、リソース利用のバランスを取ります。
- 物理マシンの過剰利用を最小限にし、制限を減少させます。
- Swapと物理マシンの移動
Swapは、プライマリデータベースを変更することです。SQL Azureデータベースは常に三重化しているので、プライマリデータベースの役割をセカンダリデータベースに移動させます。これは、とても早くできます。
物理マシンの移動は、データベースのホスト場所を別の物理マシンにコピーし移動させることです。
SQL Azureリソース制限
制限サービスには次の特徴があります。
- システムリソースを大量に長い時間使用されることからマシンを守ります
- 実際のリソース使用量と安全閾値をリアルタイムに比較評価します
- 最もリソースを使用しているDBを制限します。（ソフトな制限）
- もし無理ならすべてのDBを制限します。（ハードな制限）
制限サービスは、新しい接続要求の受付を停止します。さらに、制限が必要な場合は、リソースを過剰消費している処理を中断します。
制限サービスにより制限されている場合、次の接続エラーを表示します。
“The service is currently busy. Retry the request after 10 seconds. Code: %d.”
制限コードをデコードすることで、より詳細に状況を知ることができます。
制限コードのデコード
制限コードのレコード方法は、
- 制限コードを256で割る
- 「1」の結果をバイナリに変換する
- 右から左へ2つずつグルーピングする
その結果から、次の表を参照して結果を分析する。
制限シナリオ１
- 顧客AがマシンのCPUを30%使用している
- 顧客Bが同じマシンで、追加で70%のCPUを開始した
- 顧客Bは制限されます
解決方法：ロードバランサーは、このマシンからAまたはBを移動させる
制限トリガー：顧客B
制限対象：顧客B
公平性：公平に顧客Bを制限します
Bは、AよりもCPUを使用している
このマシーンの制限は、Bがトリガーとなっている。
制限シナリオ2
- 顧客Aがマシン上でCPUを70％使用している
- 顧客Bが同じマシンで、追加で30%のCPUを開始した
- 顧客Aは制限されます
解決方法：ロードバランサーは、このマシンからAまたはBを移動させる
制限トリガー：顧客B
制限対象：顧客A
公平性：顧客Aを制限するのは公平では無い
このマシーンの制限は、Bがトリガーとなっている。
制限シナリオ3
- マシン上にアクティブなワークロードが無い
- 顧客AがCPU100％を使用し、何度も制限されている
- 顧客Aは制限されます
解決方法：無し。どこに配置しても制限されます。顧客AはマシンのCPUを使用しすぎます。
制限トリガー：顧客A
制限対象：顧客A
公平性：システム観点からは公正だが、顧客は幸せでは無い
顧客Aは、SQL Azureマシンに適合するように、最適化とリソース使用量を減らす必要があります。
動的管理ビューと監視
動的管理ビューは、個別のユーザDBにデータがマッピングされます。動的管理ビューは、SQL Server 2008と同じ動作をします。
データベースストレージの合計使用料
select
sum(reserved_page_count)*8.0/1024 AS [Storage_in_MB]
from
sys.dm_db_partition_stats
CPUの使用率が高いクエリ
select
highest_cpu_queries.total_worker_time,
q.text AS [Query_Text],
highest_cpu_queries.plan_handle
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
IOの使用率が高いクエリ
select top 25
(total_logical_reads/execution_count) as avg_logical_reads, ｚｚ(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
sql_handle,
plan_handle
from sys.dm_exec_query_stats
order by
(total_logical_reads + total_logical_writes) Desc
パフォーマンス予想の設定
クラウドで、パフォーマンス問題を解決することはできません。
クラウドは、ハードウェアが異なり、マルチテナント環境であり、NW遅延があるためオンプレミスと比較して同じパフォーマンスを保証することはできません。
SQL Azureへマイグレーションする前にオンプレミスのDBをよく知る必要があります。
パフォーマンスを比較する前にベースラインを取得する
オンプレミスのパフォーマンスのベースライン
- オンプレミスのハードウェアスペックは何ですか？
- オンプレミスでDBテストはしましたか？
- DOPを使用していますか？
- DBはミドル層とどのように通信していますか？
- SQL Azureへマイグレーションした後何かを変更しますか？
新しいDBアプリケーションのための柔軟な対応
従来の容量設計 ＝ 十分なハードウェアを購入する
SQL Azureの容量設計 = 必要なデータベース数を計測する
DB作成 ＝ リソースがさらに必要
DB削除 ＝ リソースの解放
いつ作成したり削除しますか？
DBコピーを使用して、読込書込みのワークロードを分離する。
Federation・スケールアウトの為に2つのレベルのコンポジットキーで構成する
顧客ID
年月
コンポジットキー＝(bigint) [customer_ID + MonthYear_Key]