SE の雑記

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

Azure Virtual Machine 上で稼働している SQL Server の I/O 特性を確認する Part3

leave a comment

今回はデータファイルの I/O 特性を確認してみたいと思います。

テストで使用するデータは 5,000 万件 / 1.2GB のデータが入っている GUID のみで構成されたテーブルを使用しています。

CREATE TABLE TestTable (
Col1 uniqueidentifier DEFAULT NEWSEQUENTIALID()
, INDEX CIX_TestTable CLUSTERED (Col1)
)

 

このテーブルを 1 / 4 ディスクで構成された記憶域スペース、P10 に配置し性能を測定してみます。

 

■シングルスレッドのシーケンシャル読み込み


まずは、シングルスレッドで読み込みを行わせるため、MAXDOP を 1 にして検索を行ってみます。

SET NOCOUNT ON
DBCC DROPCLEANBUFFERS
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(*) FROM TestTable OPTION(MAXDOP 1)
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
SELECT  * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC

 

この際の実行プランは以下のようになります。

image

この場合、大量の先行読み取りが実行されますがこの際の I/O は「512KB」単位で行われているため、ブロックサイズとしてはかなり大きめのサイズでディスクにアクセスが行われています。

この時の処理時間が以下になります。

ディスク 処理時間 CPU 時間 (ミリ秒)
記憶域 : 1 5:11 10,297
記憶域 : 4 1:18 9,718
P10 0:17 8,609

 

通常のデータディスクでは「5:11」かかっていますが、データディスクを 4 本使っているものに関しては、大幅に処理時間が短縮されています。

ストライプサイズでうまくディスクアクセスが分散されているようですね。

プレミアムストレージに関しては 1 本のディスクで構成していますが、処理時間は 1 本構成でも圧倒的に短いですね。

言い方が雑ではありますがプレミアムストレージを使用すると、ディスクの構成をあまり考えないでもディスクアクセスを効率化することができています。

この際の待ち時間と読み取りページ数が以下になります。

ディスク PAGEIOLATCH_SH 読み取りページ数
waiting_tasks_count wait_time_ms
記憶域 : 1 432 345,219 155,329
記憶域 : 4 407 72,900 155,329
P10 361 8,947 155,329

 

すべてのケースで同一ののページ数が読み取られていますが、「PAGEIOLATCH_SH」(ディスクからメモリへのデータ読み取りが発生した場合の待ち時間) が、ディスク性能に応じて変化していることが確認できます。

データベースのデータファイルの分割は、先行読み取りが発生した場合などに、大きいブロックサイズでの読み込みが発生した場合に効果が出ていることが確認できます。

 

■マルチスレッドのシーケンシャル読み込み


 

先ほどは MAXDOP を 1 に設定して読み込みを行っていました。

次は MAXDOP を 0 に設定して読み込みを行ってみたいと思います。

DS13 は 8 コアの 1  NUMA 構成ですので MAXDOP を 0 にした場合、最大で 8 スレッドで処理を行うことができます。

imageimage

 

処理時間に関しては以下になっています。

ディスク 処理時間 CPU 時間 (ミリ秒)
記憶域 : 1 5:12 12,702
記憶域 : 4 1:18 11,623
P10 0:12 9,234


P10 では、マルチスレッドで処理をすることで、処理時間が短縮されましたがその他のディスクについては処理時間は 1 スレッドと同様でした。

1 スレッドの状態で、1 ディスクの場合は、「9IOPS / 4MB/sec」4 ディスクの場合は、「35IOPS / 16MB/sec」 の I/O が発生しており、この傾向は 8 スレッドの状態と変わっていませんでした。

このことからディスク性能の上限に達してしまい、スレッド数が増加しても処理性能が向上しなかったと考えらられます。

P10 を使用した場合、1 スレッドでは「140IOPS / 70MB/sec」、8 スレッドでは「200 IOPS / 100MB/sec」発生させることができています。

ディスク性能が高い場合には、マルチスレッドでアクセスした際の効果が出てくることが確認できます。

この傾向は SQLIO で 512KB の結果を取得した時と同じになっています。

今回の先行読み取りは、きちんとデータが整列されている状態で発生していますので、シーケンシャルリードの特性を持っています。

この時の SQLIO の結果が以下になります。

ブロックサイズ スレッド数 ディスク 操作 IOPS MB/sec
512K 1 記憶域 : 1 Seq : Read 7.8 3.9
記憶域 : 4 Seq : Read 25.96 12.98
P10 (1 ディスク) Seq : Read 94.6 47.3
4 記憶域 : 1 Seq : Read 7.85 3.92
記憶域 : 4 Seq : Read 31.29 15.64
P10 (1 ディスク) Seq : Read 194.53 97.26
8 記憶域 : 1 Seq : Read 7.88 3.94
記憶域 : 4 Seq : Read 31.31 15.65
P10 (1 ディスク) Seq : Read 194.69 97.34

 

512KB I/O を発生させた場合、1 ディスクについてはスレッド数を変更しても差はなく、4 スレッドについてはも誤差程度の差しかありませんでした。

# 測定された値と、SQL 実行中に確認した値も同程度となっています。

P10 を使用した場合は、倍近くのディスク I/O が発生しており、スレッドを増やしたことによる性能の向上が、実際に SQL を実行して発生させた I/O にも表れてきていると考えられます。

■データファイルの分割の効果


次にデータファイルの分割の効果を見てみたいと思います。

現状のデータベースは 1 データファイルで構成されていますのでこれを 4 データファイルにして、データを分散配置します。

MAXDOP が 1 の場合は、以下になります。

ディスク 処理時間 CPU 時間 (ミリ秒)
記憶域 : 1 5:11 9,734
記憶域 : 4 1:18 10,156
P10 0:18 8,734

 

ディスク PAGE_IO_LATCH 読み取りページ数
waiting_tasks_count wait_time_ms
記憶域 : 1 406 397,841 155,327
記憶域 : 4 368 71,811 155,327
P10 55 743 155,327


MAXDOP を 0 にした場合は以下になります。

ディスク 処理時間 CPU 時間 (ミリ秒)
記憶域 : 1 5:11 12,903
記憶域 : 4 1:18 11,889
P10 0:12 9,750

 

ディスク PAGE_IO_LATCH 読み取りページ数
waiting_tasks_count wait_time_ms
記憶域 : 1 2,814 2,447,500 155,329
記憶域 : 4 2,476 618,654 155,328
P10 767 91,091 155,328

 

データファイルを分散させた場合に効果が得られるケースも多々あるのですが、基本的なディスク性能を担保するのが、効果としては一番あらわれている形になっています。

 

また、以下のようなデータ圧縮により、データページが減る場合には、CPU 負荷 / 変更処理のスループットへの影響が懸念されますが、I/O 数が減りますので、データ取得の際のディスク負荷の負荷を低減させることが可能となります。

ALTER INDEX CIX_TestTable ON TestTable REBUILD WITH (DATA_COMPRESSION=PAGE)
Share

Written by Masayuki.Ozawa

3月 15th, 2015 at 5:08 pm

Leave a Reply