SE の雑記

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

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

leave a comment

前回の投稿では SQLIO を使用して I/O 特性を確認してみました。

今回の投稿からは SQL Server を利用して I/O 特性を確認してみたいと思います。
まずは、「トランザクションログの書き込み」をターゲットとして、確認をしてみたいと思います。

今回はデータファイルの性能差をなくすために、データファイルは D ドライブ、トランザクションログを測定対象のディスクに配置して計測をしています。

測定対象のディスクは前回と同じで、1 本のデータディスクと 4 本のデータディスクを使用した記憶域スペースを使用し、以下のクエリによりデータベースを作成しています。

 

CREATE DATABASE [1DISKDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'1DISKDB', FILENAME = N'D:\SQLDATA\1DISKDB.mdf' , SIZE = 3072000KB , FILEGROWTH = 512000KB )
 LOG ON 
( NAME = N'1DISKDB_log', FILENAME = N'E:\SQLDATA\1DISKDB_log.ldf' , SIZE = 1024000KB , FILEGROWTH = 102400KB )
GO
GO
ALTER DATABASE [1DISKDB] SET RECOVERY SIMPLE 
GO

CREATE DATABASE [4DISKDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'4DISKDB', FILENAME = N'D:\SQLDATA\4DISKDB.mdf' , SIZE = 3072000KB , FILEGROWTH = 512000KB )
 LOG ON 
( NAME = N'4DISKDB_log', FILENAME = N'F:\SQLDATA\4DISKDB_log.ldf' , SIZE = 1024000KB , FILEGROWTH = 102400KB )
GO
GO
ALTER DATABASE [4DISKDB] SET RECOVERY SIMPLE 
GO

CREATE DATABASE [P10DB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'P10DB', FILENAME = N'D:\SQLDATA\P10DB.mdf' , SIZE = 3072000KB , FILEGROWTH = 512000KB )
 LOG ON 
( NAME = N'P10DB_log', FILENAME = N'G:\SQLDATAP10KDB_log.ldf' , SIZE = 1024000KB , FILEGROWTH = 102400KB )
GO
GO
ALTER DATABASE [P10DB] SET RECOVERY SIMPLE 
GO

 

まずはデータベースの作成時間から比較してみたいと思います。

この環境では瞬時初期化を有効にしていますので、データファイルの作成時間については、ほぼ無視できる時間となっています。

ログファイルの生成時には、今回の場合では、「8MB」単位での I/O が発生しています。

これによりログファイルが生成され、データベースが作成されるまでの時間が以下となります。

ディスク 作成時間
記憶域 : 1 4:20
記憶域 : 4 1:27
P10 0:12

 

ディスク I/O の性能が良いディスクに対しては、作成の時間に差が出てきているのが確認できます。

# CREATE DATABASE は複数のスレッドで動作しているようですので、複数スレッド+ディスク性能の効果が如実に表れてきています。

それでは、以下のようなテーブルを作成しての INSERT 性能はどうなるでしょう。

CREATE TABLE [1DISKDB]..TestTable (Col1 uniqueidentifier)
CREATE TABLE [4DISKDB]..TestTable (Col1 uniqueidentifier)
CREATE TABLE [P10DB]..TestTable (Col1 uniqueidentifier)

 

GUID のみを持つ単純なテーブル構造です。

 

■単純な INSERT 性能


先ほどのテーブルに以下のクエリでデータを INSERT し、処理時間を計測してみます。

SET NOCOUNT ON
GO
DECLARE @i int = 1
WHILE (@i <= 10000)
BEGIN
	INSERT INTO TestTable VALUES(NEWID())
	SET @i += 1
END

 

このクエリは INSERT を 1 行ずつ実施するクエリですのでスレッドとしてはシングルスレッドで処理が行われ、それぞれの INSERT に対して、4KB という少ないサイズでの I/O が発生しています。

ディスク
処理時間
記憶域 : 1 1:36
記憶域 : 4 1:42
P10 0:28

 

このような INSERT を実行した場合、ディスクへの書き込みは直列化されていますので、記憶域スペースでディスクを束ねた効果は薄く、単一のディスク性能が大きく依存してきます。

上記の INSERT 中の待ち事象も確認をしてみます。

ディスク
WRITELOG 平均待ち時間 (ms)
waiting_tasks_count wait_time_ms
記憶域 : 1 10,013 94,936 9.48
記憶域 : 4 10,021 94,983 9.48
P10 10,005 28,354 2.83

 

WRITELOG の発生状況は誤差の範囲でしか変わっていませんので、どのディスクでも同一の回数の待ちが発生していることが確認できます。

INSERT 性能の差は待ち事象が発生した場合の、「待ち時間」により差が発生しているのが、この情報から確認できます。

記憶域スペースで 1 ディスク / 4 ディスク を使用しているディスクでは、どちらのパターンも 1 回の待ち事象で、平均して「9.48ms」の待ちが発生しています。

P10 では、平均の待ち時間が「2.83ms」となっています。

SQLIO で 1 スレッドでシーケンシャルな書き込みを行った際の性能は、8KB の情報ではありますが以下のようになっています。

スレッド数 ブロックサイズ ディスク 操作 IOPS MB/sec
1 8K 記憶域 : 1 Seq : Write 119.16 0.93
記憶域 : 4 Seq : Write 107.47 0.83
P10 (1 ディスク) Seq : Write 338.78 2.64

 

単純な INSERT では、この傾向がそのまま出ており、性能については SQLIO で測定した場合と同じ 3 倍程度の差が出ています。

 

■複数のスレッドでの INSERT 性能


それでは、先ほどのクエリを  SQLQueryStress を使用して、20 スレッドで実行してみたいと思います。

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

 

ディスク
処理時間
記憶域 : 1 3:04
記憶域 : 4 3:31
P10 1:29

 

複数スレッドから実行はしていますが、トランザクションログは基本的にシーケンシャルに書き込まれていきますので、ここでも 1 スレッドのディスク性能の差が処理時間に大きく影響を与えてきています。

こちらは待ち事象も同様の傾向があります。

ディスク
WRITELOG 平均待ち時間 (ms)
waiting_tasks_count wait_time_ms
記憶域 : 1 210,198 3,361,004 16.0
記憶域 : 4 211,638 3,889,936 18.4
P10 206,312 1,585,726 7.7

 

1 スレッドでの単純 INSERT と同様の傾向となっており、ログレコードの待ち時間が処理時間に直結していることがここからも確認することができます。

 

■一括のログ書き込みによる性能の違い


次に 200 万件のデータを BULK INSERT した際の性能傾向を見てみたいと思います。

今回は各データベースの復旧モデルは「単純」を設定しています。
今回は BCP のバッチサイズは 0 で指定しており、この場合、ログファイルには 60KB 単位で書き込みが行われています。

この際の処理時間は以下のようになります。

BULK INSERT を実行した場合は、複数のディスクを束ねた効果が表れています。

ディスク
処理時間
記憶域 : 1 1:00
記憶域 : 4 0:16
P10 0:08

 

待ち事象も同様の傾向となっています。

ディスク
WRITELOG 平均待ち時間 (ms)
waiting_tasks_count wait_time_ms
記憶域 : 1 7 5,402 771.7
記憶域 : 4 2 146 73.0
P10 9 465 51.7

 

それでは、先ほどクエリを以下のよう変更して 200 万件のデータを INSERT してみたいと思います。

DECLARE @i int = 1
BEGIN TRAN
WHILE (@i <= 2000000)
BEGIN
	INSERT INTO TestTable VALUES(NEWID())
	SET @i += 1
END
COMMIT TRAN

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

 

ディスク
処理時間
記憶域 : 1 1:06
記憶域 : 4 0:33
P10 0:32

 

まとめてコミットをするようなログの書き込みにおいては、ディスクの基本性能+ディスクを束ねた効果が出てきます。

このようなコミットをする場合にはコード側の変更が必要となるため、SQL Server の設定で回避できるわけではありませんが、カーソルやループで INSERT をしている場合などは、まとめたレコード単位でデータの変更をすることを検討することで処理性能を改善できる可能性があります。

# これは、通常のハードウェアで SQL Server を実行した場合も同じですが。

 

■P10 と P20 の性能比較


単純 INSERT の連続の場合は以下のような結果となりました。

SQLIO で実施した場合と同じで、P10  / P20 ともにショートトランザクションの INSERT については処理時間に差は見られませんでした。

– 単純 INSERT –

ディスク 処理時間
P10 0:29
P20 0:29

 

ディスク WRITELOG 平均待ち時間 (ms)
waiting_tasks_count wait_time_ms
P10 10,003 27,898 2.79
P20 10,014 28,261 2.82

 

次に複数スレッドの比較をしてみます。

こちらも差はほとんどないですね。

ディスク 処理時間
P10 1:28
P20 1:35

 

ディスク WRITELOG 平均待ち時間 (ms)
waiting_tasks_count wait_time_ms
P10 203,823 1,471,442 7.22
P20 206,385 1,692,593 8.20

 

– 一括操作 –

BCP による実行時間を比較してみます。

こちらについても大幅な差は見られませんでした。

# 待ち事象を誤差としてみるかの議論はありそうですが、秒単位で変わっていないので今回は誤差ととらえています。

ディスク 処理時間
P10 0:07
P20 0:07

 

ディスク WRITELOG 平均待ち時間 (ms)
waiting_tasks_count wait_time_ms
P10 7 525 75.00
P20 2 120 60.00

 

INSERT を @@TRANCOUNT を使用したパターンが以下となります。

こちらも同様の結果となっていますね。

ディスク 処理時間
P10 0:28
P20 0:28

 

ディスク WRITELOG 平均待ち時間 (ms)
waiting_tasks_count wait_time_ms
P10 2 7 3.50
P20 9 68 7.56

 

 

■非同期によるログ書き込み


 

通常、SQL Server では同期的なログ書き込みである、先行ログ書き込み (WAL) によりディスクにログが書き込まれ変更を保証します。

SQL Server 2014 では非同期のログ書き込みである、遅延持続性が使用できるようになりました。

これにより、ログの書き込みの完了をディスクではなくメモリ上のログ領域に書き込まれたタイミングで完了とし、永続化については非同期で行うことができるようになります。

先ほどのコミット単位の変更では、既存のコードに対しての変更が発生します。

非同期のログ書き込みについては、データベース単位で設定を設定を強制することができるため、これが有効に使えればコードの変更は必要がなくなります.

 

なお、遅延持続性んついては、強制的に有効にしていいかの考慮が十分に必要となります。

以下のようなクエリを実行して、DB レベルで強制化することですべてのトランザクションが遅延持続性となりますが、これでは通常の OLTP でも強制化されてしまいます。

ALTER DATABASE [1DISKDB] SET DELAYED_DURABILITY = FORCED WITH NO_WAIT

 

FORCED ではなく ALLOWED を使用することで、「COMMIT TRAN WITH (DELAYED_DURABILITY = ON)」を使用した場合のみ、そのトランザクションを遅延持続性とすることが可能となります。

DECLARE @i int = 1
BEGIN TRAN
WHILE (@i <= 2000000)
BEGIN
	INSERT INTO TestTable VALUES(NEWID())
	SET @i += 1
END
COMMIT TRAN WITH (DELAYED_DURABILITY = ON)

遅延持続性を利用する場合は、

  • DB 全体として強制
  • COMMIT TRAN をする際に任意で指定

のいずれかの対応ができるかを検討します。

今回は DB 全体で強制するようにし、以下のクエリで無効時 / 強制時の処理速度を確認します。

DECLARE @i int = 1
WHILE (@i <= 10000)
BEGIN
	INSERT INTO TestTable VALUES(NEWID())
	SET @i += 1
END

EXEC sp_flush_log

非同期のログ書き込みとすることで、冒頭で記載した処理時間を以下のように短縮することができます。

ディスク
遅延持続性無効

処理時間

遅延持続性有効

処理時間

記憶域 : 1 1:36 0:01
記憶域 : 4 1:42 0:00
P10 0:28 0:00

 

ディスク
遅延持続性無効 遅延持続性無効
WRITELOG 平均待ち時間 (ms) WRITELOG 平均待ち時間 (ms)
waiting_tasks_count wait_time_ms waiting_tasks_count wait_time_ms
記憶域 : 1 10,013 94,936 9.48 0 0 0
記憶域 : 4 10,021 94,983 9.48 0 0 0
P10 10,005 28,354 2.83

0 0 0

 

遅延持続性を実際に使用するケースとしては「OLTP のショートトランザクション」ではなく、「バッチ処理による一括した大量のトランザクション」で使用するのがマッチするかと思います。

OLTP のショートトランザクションでは、トランザクションの損失は許容されないと思います。

バッチ処理については、処理中にサーバーが停止した場合などは最初から処理が実施できるのであれば、バッチ実行の最後のタイミングで、非同期ログのフラッシュができれば、一貫性については許容できる可能性があるかと。

 

1 件毎の変更をログレコードに書き込む、ショートトランザクション特性のログ書き込みについては、複数のデータディスクを束ねても効果が低くなります。

複数のデータディスクを束ねた場合の効果は、まとまったログレコードの書き込みを行った際に顕著に表れてきていますので、ログの書き込みが遅い場合には、「どのようなログの書き込みが遅い」のかを確認し、それに応じて、

  • 1 本のディスク性能を上げる (プレミアムストレージを利用)
  • 論理ディスクを構成する物理ディスクの本数を増やす (記憶域スペースを高い列数で構成)

のどちらのアプローチをとればよいかが重要となってきます。

Written by masayuki.ozawa

3月 14th, 2015 at 8:33 pm

Leave a Reply

*