SQL Serverでのデータの信頼性を拡張するログ記録アルゴリズムとデータ ストレージ アルゴリズムの説明
元の製品バージョン: SQL Server 2014、SQL Server 2012、SQL Server 2008、SQL Server 2005
元の KB 番号: 230785
まとめ
この記事では、Microsoft SQL Server のログ記録とデータ アルゴリズムによってデータの信頼性と整合性がどのように拡張されるかについて説明します。
エンジンの基になる概念と、復旧と分離の悪用セマンティクス (ARIES) のアルゴリズムの詳細については、次の ACM Transactions on Database Systems ドキュメント (ボリューム 17、Number 1、1992 年 3 月) を参照してください。
外部リンク: ARIES: 先書きログを使用した細分性ロックと部分ロールバックをサポートするトランザクション復旧方法
このドキュメントでは、エラーに関連するデータの信頼性と整合性を拡張するための SQL Server の手法について説明します。
キャッシュと代替エラー モードの説明の詳細については、Microsoft サポート技術情報の次の記事を参照することをお勧めします。
この記事で使用される用語
詳細な説明を開始する前に、この記事全体で使用される用語の一部を次の表で定義します。
任期 | 定義 |
---|---|
バッテリバックアップ | データ損失を防ぐために、キャッシュメカニズムによって直接利用可能で制御される独立したローカライズされたバッテリバックアップ機能。 これは無停電電源装置 (UPS) ではありません。 UPS は書き込みアクティビティを保証せず、キャッシュ デバイスから切断できます。 |
キャッシュ | 物理 I/O 操作を最適化し、パフォーマンスを向上させるために使用される中間ストレージ メカニズム。 |
ダーティ ページ | 安定ストレージにまだフラッシュされていないデータ変更を含むページ。 ダーティ ページ バッファーの詳細については、SQL Server オンライン ブック ページの書き込み を参照してください。 コンテンツは、Microsoft SQL Server 2012 以降のバージョンにも適用されます。 |
失敗 | SQL Server プロセスの予期しない停止を引き起こす可能性のあるもの。 たとえば、停電、コンピューターのリセット、メモリ エラー、その他のハードウェアの問題、不良セクター、ドライブの停止、システム障害などです。 |
フラッシュ | キャッシュ バッファーを安定したストレージに強制する。 |
ラッチ | リソースの物理的な整合性を保護するために使用される同期オブジェクト。 |
不揮発性ストレージ | システム障害が発生しても使用可能なメディア。 |
ピン留めされたページ | データ キャッシュに残り、関連するすべてのログ レコードが安定したストレージの場所でセキュリティで保護されるまで、安定したストレージにフラッシュできないページ。 |
安定したストレージ | 不揮発性ストレージと同じです。 |
揮発性ストレージ | 障害が発生してもそのまま残らないメディア。 |
先書きログ記録 (WAL) プロトコル
プロトコルという用語は、WAL を記述する優れた方法です。 これは、データが正しく格納および交換され、障害が発生した場合に既知の状態に回復できることを確認するために必要な、特定の定義済みの実装手順のセットです。 ネットワークに一貫性のある保護された方法でデータを交換するための定義済みのプロトコルが含まれているのと同様に、WAL もデータを保護するためのプロトコルを記述します。
ARIES ドキュメントでは、WAL は次のように定義されています。
WAL プロトコルは、変更されたデータが不揮発性ストレージ内の以前のバージョンのデータを置き換えることを許可する前に、一部のデータへの変更を表すログ レコードが既に安定ストレージに存在している必要があることをアサートします。 つまり、ページの更新が安定ストレージに書き込まれるログ レコードの少なくとも元に戻す部分が記述されるまで、システムはページの不揮発性ストレージ バージョンに更新されたページを書き込むことが許可されません。
先書きログの詳細については、SQL Server オンライン ブックの Write-Ahead トランザクション ログ トピックを参照してください。
SQL Server と WAL
SQL Server は WAL プロトコルを使用します。 トランザクションが正しくコミットされるようにするには、トランザクションに関連付けられているすべてのログ レコードを安定したストレージで保護する必要があります。
この状況を明確にするために、次の具体的な例を考えてみましょう。
Note
この例では、インデックスがなく、影響を受けるページが 150 ページであるとします。
BEGIN TRANSACTION
INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION
次に、次の表で説明するように、アクティビティを単純なログ記録手順に分割します。
ステートメント | 実行されたアクション |
---|---|
BEGINTRANSACTION | ログ キャッシュ領域に書き込まれます。 ただし、SQL Server は物理的な変更を行っていないので、安定したストレージにフラッシュする必要はありません。 |
INSERT INTO tblTest | 1. データ ページ 150 は、SQL Server データ キャッシュに取得されます (まだ使用できない場合)。 2. ページが ラッチ、 ピン留め、および マーク付きダーティ、適切なロックが取得されます。 3. ログの挿入レコードが作成され、ログ キャッシュに追加されます。 4. 新しい行がデータ ページに追加されます。 5. ラッチが解放されます。 6. トランザクションまたはページに関連付けられているログ レコードは、すべての変更が揮発性ストレージに残っているため、この時点でフラッシュする必要はありません。 |
COMMIT TRANSACTION | 1. コミット ログ レコードが作成され、トランザクションに関連付けられているログ レコードを安定したストレージに書き込む必要があります。 トランザクションは、ログ レコードが安定したストレージに正しく割り当てられるまでコミットされたとは見なされません。 2. データ ページ 150 は SQL Server データ キャッシュに残り、安定したストレージにすぐにフラッシュされません。 ログ レコードが正しくセキュリティで保護されている場合、必要に応じて、回復によって操作をやり直すことができます。 3. トランザクション ロックが解放されます。 |
"ロック" と "ログ記録" という用語に混同しないでください。重要ですが、WAL に対処する場合、ロックとログ記録は別の問題です。 前の例では、SQL Server は通常、トランザクションの全時間ではなく、ページで物理挿入の変更を実行するために必要な時間、ページ 150 のラッチを保持します。 必要に応じて、行、範囲、ページ、またはテーブルを保護するために、適切なロックの種類が確立されます。 ロックの種類の詳細については、SQL Server オンライン ブックのロックに関するセクションを参照してください。
この例を詳しく見ると、LazyWriter または CheckPoint プロセスの実行時に何が起こるかを確認できます。 SQL Server は、ダーティ およびピン留めされたページに関連付けられているトランザクション ログ レコードに対して、すべての適切なフラッシュを安定ストレージに発行します。 これにより、関連するトランザクション ログ レコードがフラッシュされるまで、WAL プロトコル データ ページを安定ストレージに書き込むことができなくなります。
SQL Server と安定ストレージ
SQL Server では、ディスク セクター サイズ (通常は 4,096 バイトまたは 512 バイト) に関する知識を含めることで、ログとデータ ページの操作が強化されます。
トランザクションの ACID プロパティを維持するには、SQL Server が障害ポイントを考慮する必要があります。 障害が発生した場合、多くのディスク ドライブ仕様では、セクター書き込み操作の数が限られているだけです。 ほとんどの仕様では、障害発生時に 1 つのセクター書き込みが完了します。
SQL Server では、セクター サイズの倍数で 8 KB のデータ ページとログ (フラッシュされた場合) が使用されます。 (ほとんどのディスク ドライブでは、既定のセクター サイズとして 512 バイトが使用されます)。障害が発生した場合、SQL Server では、ログ パリティと破損書き込み手法を使用して、セクターよりも大きい書き込み操作を考慮できます。
ページの破損検出
このオプションを使用すると、SQL Server は、電源障害やその他のシステム障害によって発生する不完全な I/O 操作を検出できます。 true の場合、ページがディスクに書き込まれるたびに、8 KB (KB) のデータベース ページ内の 512 バイトセクターごとにビットが反転されます。 ページが後で SQL Server によって読み取られたときにビットが間違った状態にある場合、ページは正しく書き込まれなかった。破損したページが検出されました。 誤って書き込まれたページは回復によって読み取られる可能性が高いため、回復中に破損ページが検出されます。
SQL Server データベース ページは 8 KB ですが、ディスクは 512 バイトのセクターを使用して I/O 操作を実行します。 そのため、データベース ページごとに 16 個のセクターが書き込まれます。 破損ページは、オペレーティング システムが最初の 512 バイトセクターをディスクに書き込んでから 8 KB の I/O 操作が完了するまでに、システムが失敗した場合 (電源障害など) に発生する可能性があります。 障害が発生する前にデータベース ページの最初のセクターが正常に書き込まれた場合、ディスク上のデータベース ページは更新済みとして表示されますが、成功していない可能性があります。
バッテリーベースのディスク コントローラー キャッシュを使用すると、データがディスクに正常に書き込まれるか、まったく書き込まれていないことを確認できます。 この状況では、ページの破損検出を "true" に設定しないでください。これは必要ないためです。
Note
SQL Server では、破損ページ検出は既定では有効になっていません。 詳細については、「ALTER DATABASE SET オプション (Transact-SQL)」を参照してください。
ログ パリティ
ログ パリティ チェックは、破損ページの検出に似ています。 各 512 バイト セクターにはパリティ ビットが含まれています。 これらのパリティ ビットは常にログ レコードで書き込まれ、ログ レコードが取得されたときに評価されます。 512 バイト境界でログ書き込みを強制することで、SQL Server はコミット操作が物理ディスク セクターに確実に書き込まれるようにすることができます。
パフォーマンスへの影響
すべてのバージョンの SQL Server では、Win32 CreateFile 関数を使用してログ ファイルとデータ ファイルが開きます。 dwFlagsAndAttributes メンバーには、SQL Server によって開かれたときに FILE_FLAG_WRITE_THROUGH
オプションが含まれます。
FILE_FLAG_WRITE_THROUGH
は、中間キャッシュを介して書き込み、ディスクに直接移動するようにシステムに指示します。 システムは書き込み操作をキャッシュすることはできますが、遅延フラッシュすることはできません。
FILE_FLAG_WRITE_THROUGH
オプションを使用すると、書き込み操作で正常な完了が返されたときに、データが安定したストレージに正しく格納されます。 これは、データを保証する WAL プロトコルと一致します。
多くのディスク ドライブ (SCSI および IDE) には、512 KB、1 MB 以上のオンボード キャッシュが含まれています。 ただし、ドライブ キャッシュは通常、バッテリベースのソリューションではなく、コンデンサに依存します。 これらのキャッシュ メカニズムでは、電源サイクルまたは同様の障害ポイント間での書き込みを保証することはできません。 セクター書き込み操作の完了のみが保証されます。 これが特に、破損した書き込みとログのパリティ検出が SQL Server 7.0 以降のバージョンに組み込まれた理由です。 ドライブのサイズが増え続けるにつれてキャッシュが大きくなり、障害発生時に大量のデータが公開される可能性があります。
多くのハードウェア ベンダーは、バッテリを使用したディスク コントローラー ソリューションを提供しています。 これらのコントローラー キャッシュは、キャッシュ内のデータを数日間保持でき、キャッシュ ハードウェアを 2 台目のコンピューターに配置することもできます。 電源が正しく復元されると、書き込まれたデータは、それ以上のデータ アクセスが許可される前にフラッシュされます。 その多くは、最適なパフォーマンスを得るために、読み取りキャッシュと書き込みキャッシュの割合を確立できます。 一部には、大きなメモリ記憶域が含まれています。 実際、市場の特定のセグメントでは、一部のハードウェア ベンダーは、6 GB のキャッシュを備えたハイエンドのバッテリベースのディスク キャッシュ コントローラー システムを提供しています。 これらは、データベースのパフォーマンスを大幅に向上させることができます。
高度なキャッシュ実装では、システム リセット、電源障害、またはその他の障害点が発生した場合に真の書き換え機能を提供できるため、コントローラー キャッシュを無効にしないことで、 FILE_FLAG_WRITE_THROUGH
要求が処理されます。
キャッシュを使用しない I/O 転送は、ドライブ ヘッド、スピン レート、およびその他の制限要因の移動に必要な機械的時間が長くなる可能性があります。
セクターの順序付け
I/O パフォーマンスを向上させるために使用される一般的な手法は、セクターの順序付けです。 機械的なヘッド移動を回避するために、読み取り/書き込み要求が並べ替えられます。これにより、ヘッドのより一貫性のあるモーションでデータを取得または格納できます。
キャッシュは、複数のログとデータ書き込み要求を同時に保持できます。 WAL プロトコルと WAL プロトコルの SQL Server 実装では、ページ書き込みを発行する前に、安定したストレージへのログ書き込みをフラッシュする必要があります。 ただし、キャッシュを使用すると、データが実際のドライブに書き込まれず (つまり、安定したストレージに書き込まれる) ログ書き込み要求から成功が返される可能性があります。 これにより、SQL Server がデータ ページの書き込み要求を発行する可能性があります。
書き込みキャッシュの関与により、データは揮発性ストレージ内にあると見なされます。 ただし、Win32 API WriteFile 呼び出しから、SQL Server でアクティビティがどのように表示されるかを正確に確認すると、成功したリターン コードが取得されました。 SQL Server または WriteFile API 呼び出しを使用するプロセスは、データが安定したストレージを正しく取得した場合にのみ判断できます。
ディスカッションの目的で、データ ページのすべてのセクターが、一致するログ レコードのセクターの前に書き込まれるよう並べ替えられているものとします。 これはすぐに WAL プロトコルに違反します。 キャッシュは、ログ レコードの前にデータ ページを書き込みます。 キャッシュが完全にバッテリでバックアップされていない限り、障害が発生すると致命的な結果が発生する可能性があります。
データベース サーバーの最適なパフォーマンス要因を評価する場合は、考慮すべき多くの要因があります。 これらの中で最も重要なのは、"システムで有効な FILE_FLAG_WRITE_THROUGH
機能が許可されていますか?
Note
使用しているキャッシュは、バッテリを使用したソリューションを完全にサポートする必要があります。 他のすべてのキャッシュ メカニズムでは、データの破損やデータ損失が発生しやすくなります。 SQL Server では、 FILE_FLAG_WRITE_THROUGH
を有効にすることで、WAL を確保するためにあらゆる努力を行います。
テストでは、多くのディスク ドライブ構成に、適切なバッテリ バックアップなしで書き込みキャッシュが含まれている可能性があることが示されています。 SCSI、IDE、および EIDE ドライブは、書き込みキャッシュを最大限に活用します。 SSD と SQL Server の連携の詳細については、次の CSS SQL Server エンジニアのブログ記事を参照してください。
SQL Server と SSD - RDORR の学習ノート - パート 1
多くの構成では、IDE または EIDE ドライブの書き込みキャッシュを正しく無効にする唯一の方法は、特定の製造元ユーティリティを使用するか、ドライブ自体にあるジャンパーを使用することです。 ドライブ自体の書き込みキャッシュが無効になっていることを確認するには、ドライブの製造元にお問い合わせください。
SCSI ドライブには書き込みキャッシュもあります。 ただし、これらのキャッシュは通常、オペレーティング システムによって無効にすることができます。 不明な点がある場合は、ドライブの製造元に連絡して適切なユーティリティを確認してください。
書き込みキャッシュのスタック
書き込みキャッシュのスタックは、セクターの順序付けと似ています。 次の定義は、主要な IDE ドライブの製造元の Web サイトから直接取得されています。
通常、このモードはアクティブです。 書き込みキャッシュ モードでは、バッファーがいっぱいになるか、ホスト転送が完了するまで、ホストがバッファーにデータを書き込みます。
ディスク書き込みタスクが、ホスト データのディスクへの格納を開始します。 ホスト書き込みコマンドは引き続き受け入れられ、書き込みコマンド スタックがいっぱいになるか、データ バッファーがいっぱいになるまで、バッファーにデータが転送されます。 ドライブは、ドライブのスループットを最適化するために書き込みコマンドを並べ替えることができます。
自動書き込み再割り当て (AWR)
データを保護するために使用されるもう 1 つの一般的な手法は、データ操作中に不良セクターを検出することです。 次の説明は、主要な IDE ドライブの製造元の Web サイトに由来します。
この機能は書き込みキャッシュの一部であり、遅延書き込み操作中のデータ損失のリスクを軽減します。 ディスク書き込みプロセス中にディスク エラーが発生した場合、ディスク タスクは停止し、疑わしいセクターはドライブの末尾にある代替セクターのプールに再割り当てされます。 再割り当て後、ディスク書き込みタスクは完了するまで続行されます。
これは、キャッシュにバッテリ バックアップが提供されている場合に強力な機能になる可能性があります。 これにより、再起動時に適切な変更が提供されます。 ディスク エラーを検出することをお勧めしますが、WAL プロトコルのデータ セキュリティでは、遅延ではなくリアルタイムで行う必要があります。 WAL パラメーター内では、AWR 手法では、セクター エラーが原因でログ書き込みが失敗し、ドライブがいっぱいになっている状況を考慮することはできません。 トランザクションを正しく中止し、管理者に警告を表示し、データをセキュリティで保護し、メディア障害の状況を修正するための正しい手順を実行できるように、データベース エンジンはエラーを直ちに認識する必要があります。
データの安全性
データの安全性を確保するために、データベース管理者が行う必要がある予防策がいくつかあります。
- 致命的な障害から復旧するには、バックアップ戦略で十分であることを確認することをお勧めします。 オフサイトストレージやその他の予防措置が適切です。
- セカンダリ データベースまたはテスト データベースのデータベース復元操作を頻繁にテストします。
- すべてのキャッシュ デバイスが、すべての障害状況 (停電、不良セクター、不良ドライブ、システム停止、ロックアップ、電源スパイクなど) を処理できることを確認します。
- キャッシュ デバイスが次の点を確認します。
- バッテリーバックアップを統合しました
- 電源投入時に書き込みを再発行可能
- 必要に応じて完全に無効にできます
- 不良セクターの再マッピングをリアルタイムで処理する
- ページの破損検出を有効にします。 (これはパフォーマンスにほとんど影響しません)。)
- 可能であれば、RAID ドライブを構成して、不良ディスク ドライブのホット スワップを許可します。
- OS を再起動せずにディスク領域を追加できる新しいキャッシュ コントローラーを使用します。 これは理想的なソリューションです。
ドライブのテスト
データを完全にセキュリティで保護するには、すべてのデータ キャッシュが正しく処理されていることを確認する必要があります。 多くの場合、ディスク ドライブの書き込みキャッシュを無効にする必要があります。
Note
別のキャッシュ メカニズムで、複数の種類の障害を正しく処理できることを確認します。
Microsoft では、 SQLIOSim
ユーティリティを使用して、複数の SCSI ドライブと IDE ドライブのテストを実行しました。 このユーティリティは、シミュレートされたデータ デバイスとログ デバイスに対する大量の非同期読み取り/書き込みアクティビティをシミュレートします。 テスト パフォーマンスの統計情報は、書き込みキャッシュが無効になっているドライブの 1 秒あたりの平均書き込み操作数が 5,200 ~ 7,200 の RPM 範囲を示しています。
SQLIOSim
ユーティリティの詳細については、Microsoft サポート技術情報の次の記事を参照してください。
SQLIOSim ユーティリティを使用してディスク サブシステム上の SQL Server アクティビティをシミュレートする方法
多くのコンピューター製造元は、書き込みキャッシュを無効にしてドライブを注文します。 ただし、テストでは、これが常に当てはまるとは限らない可能性があることを示しています。 そのため、常に完全にテストしてください。
データ デバイス
ログに記録されていない状況では、SQL Server ではログ レコードのみをフラッシュする必要があります。 ログに記録されていない操作を実行する場合、データ ページも安定したストレージにフラッシュする必要があります。失敗した場合にアクションを再生成する個々のログ レコードはありません。
データ ページは、LazyWriter または CheckPoint プロセスによって安定したストレージにフラッシュされるまでキャッシュに残ることができます。 WAL プロトコルを使用してログ レコードが正しく格納されていることを確認すると、復旧によってデータ ページを既知の状態に回復できるようになります。
これは、キャッシュされたドライブにデータ ファイルを配置することをお勧めします。 SQL Server がデータ ページを安定ストレージにフラッシュすると、ログ レコードをトランザクション ログから切り捨てることができます。 データ ページが揮発性キャッシュに格納されている場合は、障害が発生した場合にページを回復するために使用されるログ レコードを切り捨てることが可能です。 データ デバイスとログ デバイスの両方が安定したストレージに正しく対応していることを確認します。
パフォーマンスの向上
最初に発生する可能性がある質問は、「キャッシュしていた IDE ドライブがあります。 しかし、私がそれを無効にしたとき、私のパフォーマンスは予想よりも少なくなりました。 なぜでしょうか?
Microsoft によってテストされる IDE ドライブの多くは 5,200 RPM で実行され、SCSI ドライブは 7,200 RPM で実行されます。 IDE ドライブの書き込みキャッシュを無効にすると、機械的なパフォーマンスが要因になる可能性があります。
パフォーマンスの違いに対処するために、従う方法は明確です:"トランザクションレートに対処する"。
多くのオンライン トランザクション処理 (OLTP) システムでは、高いトランザクション レートが必要です。 これらのシステムでは、適切に書き込みキャッシュをサポートできるキャッシュ コントローラーを使用することを検討し、データの整合性を確保しながら必要なパフォーマンスを向上させます。
キャッシュ ドライブ上の SQL Server で発生するパフォーマンスの大幅な変更を確認するために、トランザクションレートは小さなトランザクションを使用して増加しました。
テストでは、512 KB 未満または 2 MB を超えるバッファーの書き込みアクティビティが高いと、パフォーマンスが低下する可能性があることを示しています。
次の例を確認してください。
CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO
SET NOCOUNT ON
GO
INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')
SQL Server のテスト結果の例を次に示します。
SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)
IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds
一連の INSERT
操作全体を 1 つのトランザクションにラップするプロセスは、すべての構成で約 4 秒で実行されます。 これは、必要なログ フラッシュの数が原因です。 1 つのトランザクションを作成しない場合、すべての INSERT
は個別のトランザクションとして処理されます。 そのため、トランザクションのすべてのログ レコードをフラッシュする必要があります。 各フラッシュのサイズは 512 バイトです。 これには、大幅な機械的なドライブの介入が必要です。
1 つのトランザクションを使用する場合、トランザクションのログ レコードをバンドルでき、収集されたログ レコードをフラッシュするために 1 つのより大きな書き込みを使用できます。 これにより、機械的介入が大幅に減少します。
警告
トランザクションスコープを増やさないようにすることをお勧めします。 実行時間の長いトランザクションは、過剰で不要なブロックやオーバーヘッドの増加を引き起こす可能性があります。 SQL Server:Databases SQL Server パフォーマンス カウンターを使用して、トランザクション ログベースのカウンターを表示します。 具体的には、フラッシュされたログ バイト数/秒は、高い機械的ディスク アクティビティを引き起こす可能性のある多数の小さなトランザクションを示すことができます。
ログ フラッシュに関連付けられているステートメントを調べて、Log Bytes Flushed/sec 値を削減できるかどうかを判断します。 前の例では、1 つのトランザクションが使用されました。 ただし、多くのシナリオでは、これは望ましくないロック動作を引き起こす可能性があります。 トランザクションの設計を確認します。 次のコードのようなコードを使用してバッチを実行し、ログフラッシュの頻度と量を減らすことができます。
BEGIN TRAN
GO
INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
BEGIN
INSERT INTO tblTest VALUES ('Test')
if(0 = cast(@@IDENTITY as int) % 10)
BEGIN
PRINT 'Commit tran batch'
COMMIT TRAN
BEGIN TRAN
END
END
GO
COMMIT TRAN
GO
SQL Server では、SQL Server I/O 信頼性プログラムのレビュー要件に関するドキュメントで説明されているように安定したメディアへの保証された配信をサポート必要があります。 SQL Server データベース エンジンの入力と出力の要件の詳細については、「Microsoft SQL Server データベース エンジン入力/出力の要件を参照してください。