MS SQL Server: 強力なバックアップ

待って！ 待って！ 確かに、これは SQL Server バックアップの種類に関する別の記事ではありません。 復旧モデルの違いや、肥大化したログの対処方法についても説明しません。

おそらく (おそらく)、この記事を読んだ後は、標準的な手段を使用して削除されたバックアップが明日の夜、つまり 1.5 倍の速さで削除されることを確認できるでしょう。 これは、BACKUP DATABASE パラメーターをもう少し多く使用するためです。

投稿の内容が明らかであった場合、申し訳ありません。 「habr SQL サーバー バックアップ」というフレーズについて Google が調べたものをすべて読みましたが、パラメーターを使用してバックアップ時間に何らかの影響を与える可能性があるという事実についての言及は XNUMX つも見つかりませんでした。

アレクサンダー・グラドチェンコ氏のコメントに直ちに注目していただきます（@mssqlhelp):

本番環境では、BUFFERCOUNT、BLOCKSIZE、MAXTRANSFERSIZE パラメータを決して変更しないでください。 このような記事を書くためだけに作られています。 実際に、記憶の問題はすぐに解決されます。

もちろん、最も賢く独占的なコンテンツを投稿できれば素晴らしいのですが、残念ながらそうではありません。 このトピックに特化した英語とロシア語の両方の記事/投稿があります (正確に何と呼ぶべきかいつも迷っています)。 私が見つけたもののいくつかを以下に示します。 時間, два, XNUMX (sql.ru 上).

そこで、まず、少し簡略化した BACKUP 構文を添付します。 MSDN (ところで、BACKUP DATABASE について上で書きましたが、これはすべてトランザクション ログ バックアップと差分バックアップの両方に当てはまりますが、おそらくそれほど明らかな影響はありません):

BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  <...>
  [ WITH { <...>
           | <general_WITH_options> [ ,...n ] } ]
[;]

<general_WITH_options> [ ,...n ]::=
<...>
--Media Set Options
 <...>
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
<...>

<…> - これは、そこに何かがあったことを意味しますが、トピックとは関係がなくなったため削除しました。

普段どのようにバックアップを取っていますか? 何十億もの記事でバックアップの取り方をどのように「教えている」のでしょうか？ 一般に、それほど大きくないデータベースの XNUMX 回限りのバックアップを作成する必要がある場合、次のようなものを自動的に作成します。

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

また、一般に、バックアップに関する記事で通常言及されるすべてのパラメータのおそらく 75 ～ 90% がここにリストされています。 まあ、INIT、SKIPもあります。 MSDN にアクセスしたことがありますか? XNUMX 画面用のオプションがあることをご存知ですか? 私も見ました...

おそらくすでにお気づきかと思いますが、コードの最初のブロックに残っている XNUMX つのパラメーター、BLOCKSIZE、BUFFERCOUNT、および MAXTRANSFERSIZE についてさらに説明します。 MSDN からの説明は次のとおりです。

ブロックサイズ = { ブロック化する | @ ブロックサイズ変数 } - 物理ブロック サイズをバイト単位で示します。 サポートされるサイズは、512、1024、2048、4096、8192、16、384、および 32 バイト (768 KB) です。 デフォルト値は、テープ デバイスの場合は 65、その他のデバイスの場合は 536 です。 BACKUP ステートメントによってデバイスに適切なブロック サイズが自動的に選択されるため、通常、このパラメーターは必要ありません。 ブロック サイズを設定すると、自動ブロック サイズ選択が明示的にオーバーライドされます。

バッファカウント = { バッファ数 | @ バッファカウント変数 } - バックアップ操作に使用される I/O バッファの総数を定義します。 任意の正の整数値を指定できますが、バッファーの数が多いと、Sqlservr.exe プロセスの仮想アドレス空間が過剰になり、メモリ不足エラーが発生する可能性があります。

バッファーによって使用されるスペースの合計量は、次の式で決定されます。 BUFFERCOUNT * MAXTRANSFERSIZE.

最大転送サイズ = { 最大転送サイズ | @ maxtransfersize_variable SQL Server とバックアップ セット メディア間で交換する最大データ パケット サイズをバイト単位で指定します。 65 バイト (536 KB) の倍数から最大 64 バイト (4 MB) までがサポートされます。

誓いますが、私は以前にこれを読んだことがありますが、それらが生産性にどれほどの影響を与えるか考えたこともありませんでした。 さらに、どうやら私は一種の「カミングアウト」をする必要があるようで、今でも彼らが正確に何をしているのか完全に理解していないことを認めます。 おそらくバッファリングされた I/O とハードドライブの操作について詳しく読む必要があります。 いつかこれを行うつもりですが、今のところは、これらの値がバックアップの取得速度にどのような影響を与えるかをチェックするスクリプトを書くだけです。

サイズが約 10 GB の小さなデータベースを作成し、SSD に配置し、バックアップ用のディレクトリを HDD に配置しました。

結果を保存するための一時テーブルを作成します (一時的なテーブルは作成していないので、結果をより詳細に調べることができますが、自分で決めることができます)。

DROP TABLE IF EXISTS ##bt_results; 

CREATE TABLE ##bt_results (
    id              int IDENTITY (1, 1) PRIMARY KEY,
    start_date      datetime NOT NULL,
    finish_date     datetime NOT NULL,
    backup_size     bigint NOT NULL,
    compressed_size bigint,
    block_size      int,
    buffer_count    int,
    transfer_size   int
);

スクリプトの原理は単純です。ネストされたループのそれぞれが XNUMX つのパラメーターの値を変更し、これらのパラメーターを BACKUP コマンドに挿入し、msdb.dbo.backupset からの履歴を含む最後のレコードを保存し、バックアップ ファイルと次の反復を削除します。 。 バックアップ実行データはバックアップセットから取得されるため、精度は多少失われますが（秒の端数はありません）、これは耐えます。

まず、xp_cmdshell を有効にしてバックアップを削除する必要があります (その後、必要がない場合は無効にすることを忘れないでください)。

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  
GO

さて、実際:

DECLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH 
    COMPRESSION,
    BLOCKSIZE = {bs},
    BUFFERCOUNT = {bc},
    MAXTRANSFERSIZE = {ts}';

DECLARE @sql AS nvarchar(max);

/* BLOCKSIZE values */
DECLARE @bs     int = 4096, 
        @max_bs int = 65536;

/* BUFFERCOUNT values */
DECLARE @bc     int = 7,
        @min_bc int = 7,
        @max_bc int = 800;

/* MAXTRANSFERSIZE values */
DECLARE @ts     int = 524288,   --512KB, default = 1024KB
        @min_ts int = 524288,
        @max_ts int = 4194304;  --4MB

SELECT TOP 1 
    @bs = COALESCE (block_size, 4096), 
    @bc = COALESCE (buffer_count, 7), 
    @ts = COALESCE (transfer_size, 524288)
FROM ##bt_results
ORDER BY id DESC;

WHILE (@bs <= @max_bs)
BEGIN
    WHILE (@bc <= @max_bc)
    BEGIN       
        WHILE (@ts <= @max_ts)
        BEGIN
            SET @sql = REPLACE (REPLACE (REPLACE(@tmplt, N'{bs}', CAST(@bs AS nvarchar(50))), N'{bc}', CAST (@bc AS nvarchar(50))), N'{ts}', CAST (@ts AS nvarchar(50)));

            EXEC (@sql);

            INSERT INTO ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
            SELECT TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size,  @bs, @bc, @ts 
            FROM msdb.dbo.backupset
            ORDER BY backup_set_id DESC;

            EXEC xp_cmdshell 'del "D:SQLServerbackupbt.bak"', no_output;

            SET @ts += @ts;
        END
        
        SET @bc += @bc;
        SET @ts = @min_ts;

        WAITFOR DELAY '00:00:05';
    END

    SET @bs += @bs;
    SET @bc = @min_bc;
    SET @ts = @min_ts;
END

ここで何が起こっているのか突然説明が必要な場合は、コメントまたは PM に書き込んでください。 ここでは、BACKUP DATABASE に設定したパラメータについてのみ説明します。

BLOCKSIZE については、「閉じられた」値のリストがあり、BLOCKSIZE < 4KB ではバックアップを実行しませんでした。 MAXTRANSFERSIZE 64KB から 64MB までの 4KB の倍数の任意の数。 私のシステムのデフォルトは 1024KB ですが、私は 512 - 1024 - 2048 - 4096 をとりました。

BUFFERCOUNT の場合はさらに困難でした。任意の正の数を指定できますが、リンクには次のように書かれています。 BACKUP DATABASE ではどのように計算されますか?また、なぜ大きな値は危険なのでしょうか?。 また、バックアップが実際にどの BUFFERCOUNT で作成されているかに関する情報を取得する方法についても説明しています。私の場合は 7 です。これを減らすことに意味はありませんでした。上限は実験的に発見されました。BUFFERCOUNT = 896 および MAXTRANSFERSIZE = 4194304 の場合、バックアップは次のようになりました。エラー (これについては上記のリンクに書かれています):

メッセージ 3013、レベル 16、状態 1、行 7 BACKUP DATABASE が異常終了しています。

メッセージ 701、レベル 17、状態 123、行 7 このクエリを実行するには、リソース プール「デフォルト」にシステム メモリが不足しています。

比較のために、最初にパラメータをまったく指定せずにバックアップを実行した結果を示します。

BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;

さて、バックアップとバックアップ:

ファイル 1070072 のデータベース 'bt'、ファイル 'bt' に対して 1 ページを処理しました。

ファイル 2 のデータベース「bt」、ファイル「bt_log」の 1 ページを処理しました。

BACKUP DATABASE は、1070074 秒 (53.171 MB/秒) で 157.227 ページを正常に処理しました。

パラメータをテストするスクリプト自体は数時間で動作し、すべての測定値が得られました。 Googleスプレッドシート。 そして、これは実行時間の最も優れた XNUMX つの結果を抜粋したものです (素敵なグラフを作成しようとしましたが、投稿では表とコメントで対応する必要があります) @mixsture 彼は加えました とてもクールなグラフィック).

SELECT TOP 7 WITH TIES 
    compressed_size, 
    block_size, 
    buffer_count, 
    transfer_size,
    DATEDIFF(SECOND, start_date, finish_date) AS backup_time_sec
FROM ##bt_results
ORDER BY backup_time_sec ASC;

注意、非常に重要なお知らせです @mixsture の 注釈:

これらの値の範囲内のパラメータとバックアップ速度の関係はランダムであり、パターンはないと自信を持って言えます。 しかし、組み込みパラメータから離れることは明らかに結果に良い影響を与えました。

それらの。 標準の BACKUP パラメータを管理するだけで、バックアップの削除時間は 2 倍になり、最初は 26 秒かかっていたのに対し、53 秒になりました。 悪くないですよね？ しかし、修復で何が起こるかを見る必要があります。 回復までに 4 倍の時間がかかったらどうなるでしょうか?

まず、デフォルト設定でバックアップを復元するのにかかる時間を測定してみましょう。

RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;

まあ、あなた自身も、方法はある、交換は交換ではない、回復は回復ではないことを知っています。 そして私は次のようにします:

ファイル 1070072 のデータベース 'bt'、ファイル 'bt' に対して 1 ページを処理しました。

ファイル 2 のデータベース「bt」、ファイル「bt_log」の 1 ページを処理しました。

RESTORE DATABASE は 1070074 秒 (40.752 MB/秒) で 205.141 ページを正常に処理しました。

次に、BLOCKSIZE、BUFFERCOUNT、MAXTRANSFERSIZE を変更して取得したバックアップを復元してみます。

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE は 1070074 秒 (32.283 MB/秒) で 258.958 ページを正常に処理しました。

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE は 1070074 秒 (32.682 MB/秒) で 255.796 ページを正常に処理しました。

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE は 1070074 秒 (32.091 MB/秒) で 260.507 ページを正常に処理しました。

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE は 1070074 秒 (32.401 MB/秒) で 258.015 ページを正常に処理しました。

RESTORE DATABASE ステートメントはリカバリ中に変更されません。これらのパラメーターはステートメント内で指定されず、SQL Server 自体がバックアップから決定します。 そして、回復したとしても、ほぼ 20% 速くなるという利点があることは明らかです (正直に言うと、私はリカバリにあまり時間をかけず、いくつかの「最速」バックアップを実行し、劣化がないことを確認しました。).

念のため、これらはすべての人にとって最適なパラメーターではないことを明確にしておきます。 テストすることでのみ、自分にとって最適なパラメータを取得できます。 私はこのような結果を得ましたが、あなたは異なる結果を得るでしょう。 しかし、バックアップを「調整」することで、実際にバックアップの作成と展開をより速く行うことができることがわかりました。

また、お使いのシステムに特有のニュアンスがある可能性があるため、ドキュメントをすべて読むことを強くお勧めします。

バックアップについて書き始めたので、すぐにもう XNUMX つの「最適化」について書きたいと思います。これはパラメータの「チューニング」よりも一般的です (私が理解している限り、これは少なくともいくつかのバックアップ ユーティリティで、おそらくパラメータとともに使用されます)前述）、ハブレでもまだ説明されていません。

ドキュメントの XNUMX 行目、BACKUP DATABASE のすぐ下を見ると、次のように表示されます。

TO <backup_device> [ ,...n ]

複数のbackup_devicesを指定するとどうなると思いますか? 構文ではそれが可能です。 そして、非常に興味深いことが起こります。バックアップは単に複数のデバイスに「分散」されます。 それらの。 各「デバイス」は個別に役に立たなくなり、XNUMX つが失われ、バックアップ全体が失われます。 しかし、このような汚れはバックアップ速度にどのような影響を与えるのでしょうか?

同じフォルダー内に並べて配置されている XNUMX つの「デバイス」でバックアップを作成してみましょう。

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak'   
WITH COMPRESSION;

世界の父親の皆さん、なぜこのようなことが行われるのでしょうか？

ファイル 1070072 のデータベース 'bt'、ファイル 'bt' に対して 1 ページを処理しました。

データベース「bt」、ファイル「bt」の 2 ページを処理しましたファイル 1 にログを記録します。

BACKUP DATABASE は、1070074 秒 (40.092 MB/秒) で 208.519 ページを正常に処理しました。

バックアップが突然 25% 速くなったのでしょうか? さらにいくつかのデバイスを追加したらどうなるでしょうか?

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;

BACKUP DATABASE は、1070074 秒 (34.234 MB/秒) で 244.200 ページを正常に処理しました。

バックアップが 35 つのディスク上の 4 つのファイルに一度に書き込まれるため、合計で、バックアップを作成する時間の約 4% の利益が得られます。 もっと大きな数をチェックしました - 私のラップトップでは利益はありません、最適にはXNUMXつのデバイスです。 あなたにとっては、わかりません。確認する必要があります。 ところで、これらのデバイスをお持ちの場合、これらは実際には異なるディスクです。おめでとうございます。その効果はさらに大きくなるはずです。

では、この幸せを取り戻す方法についてお話しましょう。 これを行うには、回復コマンドを変更し、すべてのデバイスをリストする必要があります。

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

RESTORE DATABASE は 1070074 秒 (38.027 MB/秒) で 219.842 ページを正常に処理しました。

少し速いですが、近いところではありますが、重要ではありません。 一般に、バックアップはより速く削除されますが、同じ方法で復元されます。成功しましたか? 私にとって、それはかなりの成功です。 これ 重要、だから繰り返しますが、もしあなたが これらのファイルの少なくとも XNUMX つを失うと、バックアップ全体が失われます.

トレース フラグ 3213 および 3605 を使用して表示されるバックアップ情報をログで確認すると、複数のデバイスにバックアップすると、少なくとも BUFFERCOUNT の数が増加することがわかります。 おそらく、BUFFERCOUNT、BLOCKSIZE、MAXTRANSFERSIZE に対してより最適なパラメータを選択することもできますが、すぐには成功しませんでした。また、ファイル数を変えてそのようなテストを再度実行するのが面倒でした。 そして残念なのが車輪です。 このようなテストを自宅で実施したい場合は、スクリプトを作り直すのは難しくありません。

最後に価格についてお話します。 ユーザーの作業と並行してバックアップが削除される場合は、非常に責任あるアプローチでテストを行う必要があります。バックアップの削除が速いと、ディスクへの負担が大きくなり、プロセッサの負荷が増加するためです (それでも圧縮する必要があります)。オンザフライ)、それに応じてシステム全体の応答性が低下します。

冗談ですが、私が何も暴露していないことは十分に理解しています。 上記の内容は、バックアップを作成するための最適なパラメータを選択する方法を示したものに過ぎません。

あなたが行うことはすべて、あなた自身の危険とリスクの下で行われることを忘れないでください。 バックアップを確認し、DBCC CHECKDB を忘れないでください。

出所： habr.com