SQL に関する Q&A: ボトルネックとトランザクション ログ
パフォーマンスのボトルネックの種類は簡単に特定できることもあれば、できないこともあります。トランザクション ログの構成についても同じことが言えます。
Paul S. Randal
ストレージのボトルネック
Q. システムのパフォーマンス ボトルネックの種類を特定しようとしています。ディスク キューの長さが 2 を超えているので、ストレージ層に問題があると考えています。これは、SQL Server がストレージに過剰な負荷をかけていることを示す有力な指標の 1 つになるという話を読んだことがあります。これは本当でしょうか。本当である場合は、どうしたらよいでしょうか。
A. 残念ながら、お読みになった話は、現代の迷信の 1 つです。SQL Server は、非同期 I/O を使用する設定になっているので、ディスク キューの長さが 2 以上になっても問題ありません。I/O を発行する各スレッドでは、(おそらく) I/O が完了するまで、他の作業を引き続き実行します。SQL Server では、同時に非同期 I/O を発行することで、I/O サブシステムのスループットの最大化を図ります。また、大容量のデータをスキャンするときには、先読みなどの操作を実行します。
実際、DBCC CHECKDB などの操作は、I/O サブシステムに過剰な負荷をかけるため、ディスク キューの長さが数百になることも珍しくありません。このディスク キューの長さにまつわる迷信の詳細については、このブログ記事 (英語) を参照してください。
そうなると問題は、「I/O サブシステムのボトルネックがあるかどうかを判断するには何を参考にするか」ということになります。それには、Physical Disk パフォーマンス オブジェクトにある次の 2 つのパフォーマンス カウンターを確認することをお勧めします。
- Avg. Disk sec/Read
- Avg. Disk sec/Write
これらのカウンターでは、I/O の完了にかかった時間 (ミリ秒単位) を確認できます。この 2 つのカウンターの値が、平常値 (5 ～ 12 ミリ秒) よりも高い状態が続く (または恒常的に高いスパイクが記録される) 場合は、物理ディスクが I/O ボトルネックになっています。もちろん、その物理ディスクが SAN の LUN である可能性もありますが、Windows からこれ以上細かい分析を行うことはできません。
問題がある物理ディスクに複数の SQL Server データとログ ファイルがある場合は、I/O 読み込みを発生させているファイルを特定しなければならないことがあります。動的管理ビュー (DMV) である sys.dm_io_virtual_file_stats を使用し、データとログ ファイルについて簡単な時系列分析を実行してください。
DMV の分析結果がその物理ディスクの負荷が高いことを示していない場合、ストレージの管理者が I/O サブシステムのこの領域 (問題の物理ディスク) に他のアプリケーションのファイルを保存し、アプリケーション ファイルのワークロードが、I/O 帯域幅を占有している要素である可能性があります。その場合、ストレージの管理者に SQL Server ファイルを I/O サブシステムの専用の領域に移動するように依頼する必要があります。
問題のファイルが紛れもなく I/O サブシステム上の SQL Server ファイルで、過剰な I/O の原因となっているファイルを特定できる場合は、次の対応策を検討してください。
- データベース クエリのワークロードを確認し、インデックスの作成方法が正しくないか、統計が古いためにクエリ プランが適切でないことによって過剰なテーブル スキャンが実行されていないかどうかを判断します。
- ファイルの一部を I/O サブシステムの別の領域に移動します。
- サーバーにメモリを追加し、SQL Server バッファ プール (データ ファイル ページのインメモリ キャッシュ) のサイズを増やして、読み取り I/O が大量に発生しないようにします。
上記のいずれの対策を実施しても問題が解決されない場合は、ワークロードが I/O サブシステムの処理能力をはるかに上回っているだけなので、より処理能力の高い I/O サブシステムに移行してください。また、Fusion-io など、エンタープライズ クラスのフラッシュ メモリの利用を検討することもできます。
サイズに関する問題
Q. いくつかの新しいサーバーのストレージ要件を洗い出しているのですが、トランザクション ログのサイズを決めかねています。これまで、トランザクションと同じサイズにしようとしてきましたが、実際に必要なサイズが倍になることがあります。妥当な見積り方法を教えてください。
A. 最適なトランザクション ログのサイズを簡単に計算できる方法はありません。また、残念ながら、ログ ファイルの自動拡張を無効にしない限り、トランザクション ログの肥大を防ぐことも困難ですが、自動拡張を無効にすることは絶対にお勧めできません。
その理由の 1 つは、基盤となる RAID のレベルです。RAID のレベルによって、パフォーマンスと冗長性に関するトレードオフが異なります。たとえば、ある程度の冗長性を提供する最も安価な RAID 構成は RAID-5 ですが、この構成では 1 つのドライブの障害にしか対応できません (ただし、RAID-6 を使用したり、ホットスペア ドライブを構成している場合は除きます)。また、配列のドライブ数によっては、大量の書き込みを伴うワークロードが発生するとパフォーマンスに影響することがあります。
トランザクション ログでは、必ず自動拡張を有効にしてください。ログ ファイルを監視できなかった場合などの緊急事態では、この機能を有効にしていることが特に重要になります。たとえば、Percent Log Used パフォーマンス カウンターの値が 90% を超えると SQL Server エージェントの警告が生成されるようにしている場合に、電子メールやポケベルで緊急事態の通知を受け取る担当者が病気で欠勤しているとします。この場合、ログが拡張できないと、データベースに変更を加える現在実行中のすべてのトランザクションは、停止してロールバックします。これは、ワークロードのダウンタイムにつながります。
ただし、簡単な方法がないというのは、少し語弊があります。トランザクション ログの領域を使用する操作は多数あります。このような操作のサイズを使用して、トランザクション ログの要件を見積もることができます。たとえば、日常的なワークロードの一環で発生する操作もあれば、データベースのメンテナンスなど、毎日発生するわけではない操作もあります。このようなすべての操作を考慮する必要があります。このような操作には次のものがあります。
- ワークロードが実行する最大の挿入、更新、または削除トランザクション (数百万のテーブル行を対象とする 1 つのステートメントによる暗黙のトランザクション、または多数の操作を実行する明示的なトランザクション)。
- ワークロードが実行する最大の一括操作 (BULK INSERT など)。完全復旧モードを使用している場合、BULK_LOGGED 復旧モデルを使用することで、生成されるトランザクション ログの量を削減できることがあります。ただし、特定の操作について最小限のログしか記録しない BULK_LOGGED 復旧モデルを使用している場合は、障害が発生したときに復旧できる範囲に影響することがあります。詳細については、私のブログ記事「A SQL Server DBA myth a day: BULK_LOGGED recovery model (今日の SQL Server DBA 伝説: BULK_LOGGED 復旧モデル、英語)」を参照してください。
- 最大のクラスター化インデックスの再構築。ここでも、BULK_LOGGED 復旧モデルを使用してログのサイズを削減できることがあります。
上記の操作をすべて踏まえると、考慮しなければならないのはトランザクション ログのサイズだけでなく、適切なトランザクション ロールバックを実行できるようにトランザクション ログ管理システムが "予約" する領域も考慮する必要があることがわかります。トランザクションで 100 MB のトランザクション ログ レコードが生成される場合、トランザクションを中止して適切にロールバックできるように、システムによって約 100 MB の領域がトランザクション ログ内に予約されます。これは、データベースの整合性が失われることを防ぐメカニズムです。最大のトランザクションに十分対応できる領域を確保していると思っても、トランザクション ログが拡張することがあるのは、このためです。
もう 1 つ検討しなければならないのは、トランザクション ログ レコードをログに保持しなければならない理由があるかどうかです。必要な場合は、トランザクション ログがさらに拡張せざるを得ない可能性があります。レコードを保持する必要がある理由として考えられることには、次のようなものがあります。
- データベースが FULL または BULK_LOGGED 復旧モデルを使用していて、トランザクション ログのバックアップを実行していない (またはバックアップの実行頻度が少ない)。ログ レコードは削除する前にバックアップする必要があります。
- 桁はずれに実行時間が長いトランザクションがある。この場合、この長時間のトランザクションが開始されてから生成されたすべてのトランザクション ログ レコードが破棄されるのを防ぎます。
- データベース ミラーリングを使用していて、プリンシパル サーバーからミラー サーバーに送信されていないトランザクション ログ レコードがある。このようなレコードは送信されないと破棄できません。
- トランザクション レプリケーション (またはピア ツー ピア レプリケーション) を使用していて、ログ リーダー エージェント ジョブが処理していないトランザクション ログ レコードがある。
トランザクション ログが拡張されていて、その原因がわからない場合は、SQL Server に対して次のクエリを実行して原因を確認できます。
SELECT [log_reuse_wait_desc] FROM sys.databases
WHERE [name] = 'dbmaint2008';
GO
このクエリの結果が、一部のログ レコードを破棄できず、ログ領域を再利用できない理由です (このような操作は、ログの "消去" または "切り捨て" と呼びます)。
このように、トランザクション ログのサイズには、さまざまな要素が影響しますが、tempdb データベースの利用も検討している場合は、さらに多くの要素が絡んできます。このトピックの詳細については、私のブログ記事「Importance of proper transaction log size management (適切なトランザクション ログのサイズ管理の重要性、英語)」と TechNet マガジンのコラム「SQL Server のログ記録と復旧について」を参照してください。
ログ記録の必須性
Q. SQL Server 操作をログ記録の対象外にすることができない理由を説明してもらえませんか。テーブルの切り捨てはログに記録されないということを読んだのですが、SQL Server の処理速度を上げるために、すべての操作をログに記録しないようにする設定がないのはなぜですか。障害が発生したときにデータを復旧できなくてもかまわない場合はどうでしょうか。特に、tempdb のトランザクション ログを無効にできるとよいのですが。
A. テーブルの切り捨て操作についてお読みになったことは事実ではありません。すべてのデータベースで行ったあらゆる操作は、ある程度ログに記録されます。ですが、テーブルの切り捨てなど、"最小限のログ" しか記録されない操作もあります。簡単に言うと、最小限のログしか記録されない操作では、データ ファイル ページの割り当てと割り当て解除のみがログに記録されます。データ ファイル ページのテーブル レコードやインデックス レコードの操作は、ログに記録されません。このようにすることで、操作の処理速度が上がり、生成されるトランザクション ログの量も抑えられますが、それでもログに記録される情報はあります。
テーブルの切り捨ては、どの復旧モデルでも、常に最小限のログ記録しか行われません。その他の最小限のログ記録しか行われない操作 (インデックスの構築や再構築、一括読み込みなど) で、実際に最小限のログしか記録されないのは、復旧モデルに SIMPLE または BULK_LOGGED が使用された場合のみです。
SQL Server で、まったくログに記録されない操作は、スナップショット分離やオンライン インデックス操作などの機能をサポートする、tempdb のバージョン ストアを変更する操作です。バージョン ストアに関する操作のロールバックや tempdb データベースに対するクラッシュ回復の実行が必要になることはないため、このような操作はログに記録されません。
ここからが、ご質問の核心部分にかかわるところです。SQL Server の操作をログ記録の対象外にできないのは、何か問題が発生した場合に、SQL Server では常に操作をロールバックできる必要があるためです。操作内容の説明 (トランザクション ログ レコードなど) がなければ、SQL Server ではロールバック中にどのような処理を実行すべきか判断できません。それがわかる唯一の手段がログ記録です。
クラッシュ回復が必要でなくても、データベースで領域が不足したり、破損したセクターが見つかった場合、またはクエリ実行時にメモリ不足になった場合に、SQL Server では操作をロールバックできる必要があります。SQL Server で操作をロールバックできないと、データベースは使用できなくなり、ワークロードは停止します。
これは、tempdb データベースにも当てはまります。tempdb データベースのログ記録は簡素化され、生成されるログの量は抑えられていますが、同じ理由から、完全にログ記録を無効することはできません。また、SQL Server は、各データベースの整合性が保たれるように、クラッシュが発生するたびにクラッシュ回復を実行できる必要もあります。クラッシュ回復を実行できなければ、データベースは使用できません。つまり、SQL Server では操作をログ記録の対象外にする手段はなく、この仕様は変更されないと思います。
.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 の開発時にはコア ストレージ エンジンを担当していました。Paul は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に講演を行っています。彼のブログは、SQLskills.com/blogs/paul (英語) で公開しており、Twitter は Twitter.com/PaulRandal (英語) でフォローできます。