SE の雑記

Azure の IaaS 環境の仮想マシン (VM : Virtual Machine) 上にインストールされた、SQL Server でパフォーマンスを考慮してバックアップを取得する方法を軽くまとめてみたいと思います。

今回は Azure VM としていますがこれらの方法はオンプレミスの SQL Server でバックアップを取得する際にも有効な方法となります。

詳細については えろす師匠 (Master Eros / えろす財閥総帥) が詳しいですので、ご意見ご要望はこちらに。

今回は 12GB 程度のデータが格納されているデータベースのバックアップを 1 データディスクで構成されているドライブに取得するときのパフォーマンスをベースラインとしてみます。
なお、データベースに関しては 4 本のデータディスクを記憶域スペースで束ねたものを使用しています。
# バックアップの取得先としては、500IOPS / 60MB/sec が性能上限となります。

バックアップは以下のクエリの実行時間をベースラインとします。

BACKUP DATABASE [TESTDB] TO
DISK = N'G:\Backup\TESTDB_01.bak'
WITH STATS = 10
GO

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

バックアップが完了するまで 13.5 分程度かかっています。
それでは、このバックアップ取得時間を改善するためにはどのような方法があるかを考えてみます。

SQL Server 2008 以降ではバックアップの取得時に、COMPRESSION オプションを使用することが可能となっています。
# 2008 R2 から Standard 以降で使用できるようになっているかと。

このオプションを使用することで、バックアップを圧縮して取得することが可能となります。
圧縮をした場合、圧縮をした状態でディスクに書き込みが行われるため、CPU 負荷は上昇しますが、ディスク I/O の回数を減らすことができます。
# 圧縮されたデータを書き込むため、データのサイズが減り書き込み回数も抑えられるため。

それでは先ほどのクエリを COMPRESSION オプションを指定して実行してみたいと思います。

BACKUP DATABASE [TESTDB] TO
DISK = N'G:\Backup\TESTDB_01.bak'
WITH STATS = 10, COMPRESSION
GO

圧縮をすることで、処理時間は以下のようになります。

812 秒が 305 秒になりましたので、圧縮をすることでバックアップ時間が倍以上短縮できていますね。

それでは、このバックアップ時間をさらに短縮する方法を考えてみたいと思います。

SQL Server のバックアップは複数のバックアップファイルに分散して書き込むことができます。
具体的には以下のようなクエリになります。

BACKUP DATABASE [TESTDB] TO
DISK = N'G:\Backup\TESTDB_01_01.bak',
DISK = N'G:\Backup\TESTDB_01_02.bak'
WITH STATS = 10, COMPRESSION
GO

先ほどまでは、「TESTDB_01.bak」としてバックアップを取得していたのですが、これを「TESTDB01_01.bak」「TESTDB01_02.bak」として取得を行っています。
# バックアップのコピーではなく、二つのファイルで一つのバックアップとなります。

これにより、バックアップの書き込みを分散させ、バックアップの取得時間をさらに向上させることができます。
圧縮のみでは「305秒」だったものが、複数のファイルにバックアップを取得することで、「193秒」に処理時間が短縮されています。

それでは、4 ファイル / 8 ファイルとバックアップファイルを増やしてバックアップを取得してみるとどうでしょう。
バックアップファイル数を調整しながら取得した結果が下表となります。

今回、バックアップの取得先のディスクはすべての計測において同等のもの (1 データディスク) を使用していますが、取得方法によってバックアップのパフォーマンスを向上することができています。

今回のケースでは、何も指定しない場合は「812 秒」、圧縮をして 8 ファイルで取得した場合は「145 秒」でバックアップを取得できていますので、パフォーマンスを考慮してバックアップを取得することで考慮しない場合と比較して、「5.6」倍程度、処理時間を短縮することができています。
ディスクの構成は変更せずに取得の方法のみを変えて、これだけ性能の差が出ていますので、バックアップを取得する際には取得方法を考慮するのは重要となってきます。

Azure 上のデータディスクですが、単一スレッドで I/O 性能を向上させるのはプレミアムストレージ (DS シリーズ) や D シリーズのような SSD が使用できるディスク自体が高速なものを使用する必要があると考えています。

通常のデータディスクでディスクアクセスを効果的に実施するためには、

• 複数スレッドでデータディスクに同時に I/O を行う

必要があり、データディスクを複数束ねたとしても、単一スレッドからの I/O では I/O 性能は向上しないと私は考えています。
# 厳密には違うのかもしれませんので私の考えとしてです。

今回のバックアップでは、圧縮なしで 8 本、圧縮ありで 4 本程度で処理時間が飽和していますが、バックアップ系のマルチスレッドの I/O であれば、データディスクの本数を上げることで処理時間をさらに短縮することは可能です。

データディスクを 2 本に増やして、ストライプされたディスクに取得した場合の処理時間が以下になります。

今回のディスク構成では、データベースの読み取り性能のほうが上限に達ししてしまい、バックアップの取得先のディスク本数を増やしても効果が薄いという結果になってしまっています。
この辺はデータベースとバックアップ取得先のバランスが大事になってくるかと。

今回はバックアップを例としてますが、SQL Server のさまざまな処理で、「何スレッド」で「どのようなI/Oを発生させれているか」はディスク性能を考慮するうえでポイントとなってくるかと。

SQL Server 2012 SP1 CU4 以降では、BACKUP DATABASE TO URL で Azure ストレージに直接取得することができます。
SQL Server Backup to URL

BLOB に直接アックアップを取得した場合の制約として、

1 回のバックアップ操作で複数の BLOB にバックアップすることはサポートされていません。  たとえば、次のコードではエラーが返されます。 

BACKUP DATABASE AdventureWorks2012
TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012_1.bak'
   URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012_2.bak'
      WITH CREDENTIAL = 'mycredential'
     ,STATS = 5;
GO

があり、BACKUP TO URL では、複数のバックアップファイルを指定することがサポートされていません。
# 2 個眼の URL が構文エラーとなります。(BOL のサンプルの URL を , 区切りにしてもエラーとなります)

そのため、パフォーマンスを重視したバックアップを実行する場合、データディスクを割り当てる必要が出て来ますので、これについても注意が必要かもしれないですね。