SQL に関する Q&A: 障害回復とデータベース ミラーリング
バックアップ、障害回復、およびデータベース ミラーリングには、さまざまなシナリオに対応した無限の種類があります。
Paul S. Randal
一時的な解決策
Q. tempdb でページラッチの競合を減らすためにサーバーで構成するデータ ファイル数について、矛盾したアドバイスを数多く目にしますが、適切なアドバイスをいただけないでしょうか。
A. ご指摘のとおり、tempdb の構成に関しては不適切なアドバイスが数多く見られます。tempdb でページラッチが競合する原因は、多数の同時接続で小さなテーブルが作成および削除されるワークロードにあります。これらのワークロードでは、tempdb でデータ ファイル ページの割り当てと割り当ての解除が必要になります。そのためには、メモリ内のアロケーション ビットマップ データ ファイル ページ (使用中のデータ ファイル ページと未使用のデータ ファイル ページを記録する特殊なページ) に排他的にアクセスする必要があります。
同じタイミングで割り当てと割り当ての解除を実行しようとする同時接続が多数存在する場合、アロケーション ビットマップにアクセスできるのは一度に 1 つの接続だけなので、これが競合とパフォーマンスの低下を引き起こす原因になります。
トレース フラグ 1118 を有効にすると、この競合を少し軽減できます (詳細については、私の SQLskills.com ブログ (英語) を参照してください)。複数の tempdb データ ファイルを作成すると、さらに効果的に競合を軽減できます。複数のデータ ファイルを作成すると、SQL Server では、データ ファイルの割り当て (および割り当ての解除) がラウンド ロビン方式で実行されるようになります。このようにすると、アロケーション ビットマップの数が増加し (1 つのデータ ファイルにつき 1 つ以上が存在するようになり)、システムで発生する競合が全体的に減少します。
ここで問題となるのは、作成するデータ ファイルの数です。長い間語られてきた最良のアドバイスは、各論理プロセッサ コア (たとえば、それぞれ 4 つのコアがあり、ハイパースレッディングが有効になった 2 基の CPU は、8 つの論理コアに相当します) に対して 1 つの tempdb データ ファイルを作成するというマイクロソフトの公式な方針が間違いであるというものでした。この考え方では、8 つ以上のコアが搭載されているサーバーでメモリ リークにより処理が遅くなる可能性があります。広く信じられているもう 1 つの考え方は、プロセッサ コア数の 4 分の 1 ～ 2 分の 1 の数のデータ ファイルを作成するのが適切な開始点であるというものでした。
2011 年後半に開催された SQL PASS Summit カンファレンスで、マイクロソフト製品サポートの Bob Ward が、作成するファイル数を判断できる、より優れた式を提示しました。この式は、サーバーに搭載されている論理コア数が 8 つよりも少なければ論理コアと同じ数の tempdb データ ファイルを作成し、8 つよりも多ければ、まず、8 つの tempdb データ ファイルを作成し、競合が発生する状態が続く場合は、4 つずつファイルを増やすというものです。
これは一般的なアドバイスであることに注意してください。競合を軽減するのに、64 つのコアが搭載されたサーバーで (コア数の 2 倍に相当する) 128 個の tempdb データ ファイルが必要になる状況は少なくとも 3 つありますが、実際に必要なファイル数は、状況によって異なります。
完全な計画
Q. 先日、障害回復の計画を見直したところ、システム データベースを定期的にバックアップしていないことがわかりました。バックアップは行った方が良いでしょうか。バックアップを行わない場合に起こり得る最悪の事態はどのようなものでしょうか。
A. 定期的に障害回復の計画を見直すのは良いことですが、障害回復の計画を検証することをお勧めします。ベアメタル回復の検証を行うと、システム データベースが存在していないために、SQL Server 環境の機能が完全には復旧しないことが把握できます。
多くのデータベース管理者は、障害回復手順を計画またはテストする際、システム データベース (master、model、msdb、およびあらゆるレプリケーション ディストリビューション データベース) については考慮しませんが、これは大きな間違いです。これらのデータベースは、SQL Server のインスタンスに不可欠であるため、ユーザー データベースと同様に保護して、整合性を確認する必要があります。
SQL Server のインスタンスに接続できなければ、データが使用できる状態でも意味がありません。
master データベースが存在せず、すべての必要なログイン情報がないためにインスタンスを使用可能な状態にできない場合も、同様に意味がありません。master データベースのバックアップがなければ、アプリケーションをオンライン状態に戻す前に、すべてのデータベースのログイン情報の再作成を検討することになります。
msdb データベースには、すべての SQL エージェント ジョブ (バックアップや整合性チェックなど)、SQL エージェントの警告 (重大度の高いエラーや、I/O サブシステムが正常でないことを示す早期の警告など)、SSIS パッケージ、およびバックアップ履歴テーブルが格納されているため、msdb データベースをバックアップすることは重要です。データベースの障害回復を簡略化する一連の RESTORE ステートメントを生成するなんらかの自動システムがある場合は、おそらく msdb データベースのバックアップ履歴テーブルが使用されます。msdb データベースのコピーがない (障害によって I/O サブシステム全体が失われた) 場合は、RESTORE ステートメントを手動でつなぎ合わせる必要があります。これは手間のかかる作業で、ダウンタイムも長くなります。
すべての新しいデータベースにレプリケートする構成がある場合は、モデル データベースが不可欠です。たとえば、ホストされているクライアントごとに個別のデータベースがある環境では、モデルが必要になります。モデルがなければ、構成オプションを再設定する必要が生じます。
時間がかかるサブスクリプション データベースの再初期化を実行することなくレプリケーション データ ストリームを再確立するには、レプリケーション ディストリビューション データベースが重要です。概して、ユーザー データベースだけでなく、システム データベースのバックアップも作成しておかなければ、障害回復の戦略があるとは言えません。
まず、システム データベースのバックアップと復元に関する次の SQL Server オンライン ブックを参照することをお勧めします。
- システム データベースのバックアップと復元 (SQL Server) (SQL Server 2005 以降)
- システム データベースの移動 (SQL Server 2005 以降)
- コマンド プロンプトから SQL Server 2005 をインストールする方法 (SQL Server 2005 以降)
- システム データベースの再構築 (SQL Server 2008 以降)
止まらない拡張
Q. ログを手動で圧縮してもトランザクション ログが拡張し続けるという問題に頭を悩ませています。内部トランザクションで処理をコミットし、ログのバックアップも行っていますが、なぜログが拡張し続けるのでしょうか。
A. ここで問題となっているのは、入れ子になったトランザクションが見た目どおりに動作しないことを開発者が理解しないまま、コードで入れ子になったトランザクションを使用していることにあるようです。実際に行われていると思われるコード フローの例は、次のとおりです。
BEGIN TRAN; Do some work … BEGIN TRAN; Do some more work … COMMIT TRAN Continue with more work …
入れ子になったトランザクションを開始する 2 つ目の BEGIN TRAN ステートメントでは、ストレージ エンジンに関する限り、実際にサブトランザクションを開始していません。このコードで行われているのは、@@TRANCOUNT の値を 1 ずつ増加させていることだけです。トランザクション ログには、新しいトランザクションが開始したことを示すものは何も書き込まれていません。実際のところ、入れ子になったトランザクションで実行される処理は、すべて最初のトランザクションに含まれています。
つまり、入れ子になったトランザクションに対して COMMIT TRAN ステートメントを発行しても、入れ子になったトランザクションが実際に存在しているわけではないので、@@TRANCOUNT の値が 1 つ減るだけです。最初のトランザクションがコミットされ、@@TRANCOUNT の値が 0 に戻るまで、何もコミットされません。これが、トランザクション ログが拡張し続ける原因です。実行しているのは、実行時間の長い 1 つのトランザクションです。
それから、ログの圧縮操作は定期的に行うべきではありません。トランザクション ログを拡張する必要があるときには、新しいログがゼロで初期化され、以前 NTFS ボリュームを構成していた部分はゼロで上書きされます。これは、今後のクラッシュの回復処理でエラーが発生しないようにするために行われる処理です (詳細については、私の SQLskills.com ブログ (英語) を参照してください)。
トランザクション ログの新しい部分がゼロで初期化されている間、データベースのログ記録アクティビティはすべて一時停止し、ワークロードが少しの間停止します。トランザクション ログの自動拡張サイズを大きく設定している場合、この停止時間は長くなることがあります。
トランザクション ログは、可能な限り自動拡張しないことをお勧めします。圧縮するたびに再び拡張される場合は、そのままにしてください。この減少が発生する場合、トランザクション ログで、圧縮したサイズよりも大きなサイズが必要であることは明らかです。
ミラーリング
Q. 先日、データベースのミラーリングを実装したところ、一部のテーブルでインデックスが再構築できなくなりました。生成される大量のトランザクション ログがネットワークを圧迫し、データベース ミラーリングの速度が低下しています。このようなことが起こる理由と、対処法を教えていただけますか。
A. この問題は、データベース ミラーリングを実装する多くの人が直面しています。原因は、データベース ミラーリングを実装した運用中のデータベースで行われるパフォーマンスと信頼性に関するテストに、定期的なデータベースのメンテナンス作業が含まれていないことにあります。
インデックスの再構築操作を実行する際、多くの人は一括ログ復旧モデルを使用します。一括ログ復旧モデルでは、生成されるトランザクション ログの量が制限されるので、操作中にトランザクション ログが拡張することはありません。データベース ミラーリングで使用できるのは、インデックスの再構築操作がすべてログ記録される完全復旧モデルのみです。そのため、生成されるトランザクション ログのサイズは、再構築されるインデックスのサイズと同じになります。
完全復旧モデルでインデックスの再構築を実行すると、大量のトランザクション ログ レコードが追加で生成され、プリンシパル データベース サーバーとミラー データベース サーバー間のネットワーク リンクが飽和状態になることがあります。この状態が発生すると、プリンシパル データベースの送信キューにデータが蓄積され、同時に実行されるアプリケーション ワークロードにより、トランザクションの処理速度が遅くなる場合があります。
つまり、多くの場合、データベース ミラーリングを使用しているときには、インデックスの再構築操作を行うことができません。これは、SQL Server 2008 以降のバージョンに用意されているデータベース ミラーリングのログ ストリーム圧縮を使用した場合も同様です。
その代わりとして、インデックスのメンテナンス戦略では、ALTER INDEX … REBUILD ではなく ALTER INDEX … REORGANIZE を使用します。インデックスを再編成したときに行われるのは、既存のインデックスの断片化の解消のみです。この処理は中断しても、既に完了した処理が元に戻ることはありません。一方、インデックスを再構築すると、断片化の状態に関係なく、新しいインデックスが構築されます。処理を中断すると、処理が完了することはなく、すべての処理がロールバックされます。
再構築を行うのが実用的でない大きなインデックスに対しては、次の手順を実行してください。
- 初日: メンテナンス時間枠で ALTER INDEX … REORGANIZE を実行します。1 時間ほど実行したら、コマンドを強制終了します。ロールバックは行われず、インデックスの断片化の解消に関する処理の一部が完了します。
- 2 日目: 再編成を再度実行します。初日に関する記録はありませんが、初日に完了した部分の処理は素通りされ、次のインデックス部分から断片化の解消が開始されます。今度も 1 時間程度でコマンドの実行を強制終了します。
- 3 日目以降: 断片化のレベルが設定したしきい値より低くなるまで同じ処理を繰り返すか、毎日同じ処理を無期限に実行し続けます。
これで、定期的なインデックスのメンテナンスで生成される (データベース ミラーリングを使って転送される) トランザクション ログの量を抑えることができます。より高度な処理を行う場合は、一定の時間が経過した後に再編成プロセスを強制するのではなく、生成されるトランザクション ログの量を監視して、一定のしきい値に達したら再編成プロセスを強制終了するようにします (詳細については、私の SQLskills.com ブログ (英語) を参照してください)。
.jpg)
Paul S. Randal は SQLskills.com の代表取締役であり、Microsoft Regional Director でもあり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、SQL Server 2005 では DBCC CHECKDB/repair コードを記述し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Randal は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に講演を行っています。彼のブログは、SQLskills.com/blogs/paul (英語) で公開しており、Twitter は twitter.com/PaulRandal (英語) でフォローできます。