SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Azure 仮想マシンの SQL Server のデータファイルのスケーリング

leave a comment

Azure の仮想マシンで SQL Server を動作させる場合のデータベースのデータファイルのスケーリングについて考えてみたいと思います。

ログファイルのスケーリングについては、Azure VM で記憶域プールを使用した際の列数の影響について の考え方になるかと思います。

■データファイルのスケーリング方法


データファイルのスケーリングですが基本的には、[データファイルを複数で構成する] になるかと思います。
スケーリングをさせるためにはデータベースのデータファイルを複数で構成していきます。

この際にポイントとなるのが [同一のサイズでデータファイルを構成する] という点になります。

以下はデータファイルが均一になっているデータベース (BalancedDB) と均一になっていないデータベース (NonBalancedDB) に対して SELECT を実行した際のデータベースの I/O の状況となります。

image

SQL Server のデータファイルは空きサイズが多いファイルに対して書き込みが集中します。
そのため、データファイルのサイズが均一でない状態でデータを投入すると、各ファイルの使用率に差が出て、I/O が均一にならなくなります。

 

■データファイルの配置場所


SQL Server 2014 からデータファイルを BLOB ストレージに直接配置できるようになりました。
image

これによりデータファイルの配置場所として、

  • ローカルディスク (データディスク)
  • BLOB ストレージ

が候補として考えられます。

以下は Microsoft Azure Universal Storage for SQL Server 2014 の情報になります。

データディスクも BLOB に配置した場合も最大で [60MB/sec, 500IOPS] が各オブジェクト (データファイル) に対しての性能上限となります。
# BLOB ストレージへの配置は Windows Azure 内の SQL Server データ ファイル を参照ください。

性能上限に達した場合は複数のデータファイルでデータベースを構成する必要があります。
ポイントとしては [あとからデータファイルを追加した場合はデータファイルの空き状況が均一にならない] ということになります。

あとから追加したデータファイルは当然のことですが、使用されていない状態のため、データが格納されていません。

そのため、

  • 新規のデータを挿入する際のターゲットとして頻繁に利用
  • データの格納に偏りがあるため既存データの読み取り時には利用される確度が低い

という傾向になります。

この状況を解消するためにはデータの配置状況をリバランスする必要があります。
データの配置状況をリバランスするためにはインデックスの再構築をして均等にデータを再配置させます。

 

■データの配置場所を考える際のポイント


データの配置場所を考える際のポイントとしては、

  • ログファイルは複数のデータディスクを束ねることでスケーリング
  • データファイルは複数のデータファイルを配置することでスケーリング

となるかと思います。

ディスク I/O が BLOB の上限に収まるようでしたら、

  • データファイル / ログファイル を BLOB ストレージに配置
  • データファイル / ログファイルを同一のデータディスクに配置
    • ジオレプリケーションがサポートされる構成
  • データファイル / ログファイルを異なるデータディスクに配置

を選択することができます。

データファイル / ログファイルを BLOBストレージに配置する際のメリットとしては、[接続可能なデータディスクの本数に依存しない] ところがあるかと思います。
これは複数のデータベースを作成する必要があるときには大きなメリットとなります。
参照系の頻度が高いデータベースでは、ログファイルの書き込みが少ない場合がありますので、このような時には BLOB ストレージにデータファイルを配置するとよいのかと。

ログファイルの I/O がネックとなった場合には BLOB ストレージに配置する方法ではスケールすることができません。
書き込みの頻度が多い場合には、

  • データファイルを BLOB ストレージに配置
  • ログファイルを複数のデータディスクで構成されたディスクに配置

をする必要が出てきます。
この場合は、データベースの作成上限は [インスタンスに接続可能なデータディスクの本数に依存する] ことになります。
各データベースでデータディスクを 2 本使用したログファイルを使用する必要がある場合には、最大で 8 データベースまでしか作成できないことになります。

データベースの利用傾向に応じて以下のようなファイル配置を検討する必要が出てくるかと。

利用傾向 データベースの作成方法 最大データベース数
アクセスが少ない 同一のデータディスクにデータファイルとログファイルを配置 一つのデータディスクに何個データベースを作成するかに依存
BLOB ストレージにデータファイルとログファイルを配置 BLOB ストレージのオブジェクト上限
書き込み傾向が高い ログファイルを複数のデータディスクで構成したドライブに配置
データファイルを BLOB ストレージに配置
8 (2 本のデータディスクでドライブを構成した場合)
ログファイルを専用のデータディスクで構成したドライブに配置
データファイルを BLOB ストレージに配置
16
読み取り傾向が高い ログファイルを BLOB ストレージに配置
データファイルを複数のデータファイルで構成して BLOB ストレージに配置
BLOB ストレージのオブジェクト上限
ログファイルを BLOB ストレージに配置
データファイルを複数のデータディスクで構成したドライブに配置
8 (2 本のデータディスクでドライブを構成した場合)
ログファイルを BLOB ストレージに配置
データファイルを複数のデータディスクに分散して配置
8 (2 本のデータディスクに分散させた場合)

 

また、tempdb についても考慮が必要になってくるかと。
C ドライブにファイルが配置されているケースが多いかと思いますが、tempdb の使用率が高い場合には専用のディスクを設けることで I/O 効率がよくなります。
tempdb についても BLOB ストレージに配置が可能なようですので、専用のディスクを設けるのではなく BLOB に配置するのもよいかと思っています。

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'https://<ストレージアカウント>.blob.core.windows.net/tempdb/tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'https://<ストレージアカウント>.blob.core.windows.net/tempdb/templog.ldf');
GO

ディスクの配置を考えるうえで、

  • データファイルに必要となる I/O 性能
  • ログファイルに必要となる I/O 性能
  • 作成するデータベースの数

を意識するのがポイントとなってきそうですね。

Written by masayuki.ozawa

7月 22nd, 2014 at 5:07 pm

Leave a Reply

*