SE の雑記

今朝、ぺんぎんししょーからこの辺についてのお手紙を頂いたので軽くまとめておこうかと思います。

データベースミラーリングについては
データベース ミラーリングのベスト プラクティスおよびパフォーマンスに関する考慮事項
SQL Server 2005 データ ベース ミラーリング検証
がまとまっています。

2006 年の Tech Ed で T3-404 SQL Server Always On Technologies 「データベース ミラーリング」 徹底検証 というセッションがあり、以前はこの内容が公開されていたのですが、さすがに 6 年もたつとリンク先がなくなってしまったようです…。
# このセッション、ミラーリングについての情報が凄くまとまっていたセッションでした。

CQI 以外の書籍では、アドバンストMicrosoft SQL Server 2008構築・管理 にミラーリングの運用について詳細に記載されていますので、こちらがおススメです。

■同期モードによる遅延について

---

ミラーリングには同期モードと非同期モードの二種類があり、設定によりトランザクションに遅延が発生する可能性があるのは同期モードを設定した場合です。

非同期モードの場合はプリンシパルのトランザクションログに書き込みが行われるとトランザクションが完了しますが、同期モードに関してはミラー側のトランザクションログに書き込みが行われプリンシパルでミラーからの ACK が受信されたタイミングでトランザクションが完了します。

この差が同期モードと非同期モードのトランザクション遅延の差となります。

 

■ネットワーク遅延を発生させ同期モードと非同期モードを比較

---

同期モードと非同期モードの遅延の違いをネットワークの遅延 (Network Delay) を使用して見てみたいと思います。

今回は以下のような環境を用意しています。

プリンシパルとミラーの間に Vyatta をはさみ、帯域制御 (トラフィックポリシー) でネットワークの遅延を 500ms 設定しています。

この状態で、非同期モードを設定してプリンシパルのミラーしているデータベースに INSERT を行います。

実行には 1 ミリ秒程度かかっています。

それでは同期モードにして同様のクエリを実行してみます。

同期モードではプリンシパルとミラーの両方のトランザクションログに書き込みが行われ、ACK がプリンシパルで受信するまでが一つのトランザクションとなります。
この場合、トランザクションを完了させるためには帯域制御を行っているネットワーク間の通信が発生しますので、その分の遅延 (500ms) が処理時間として加算されているのが確認できます。

この遅延はプライマリのパフォーマンスモニターで Transaction Delay を取得することでも確認ができます。

パフォーマンスモニターの Transaction Delay ですが、これは実行されている全トランザクションの合計値となります。
そのため、500ms の遅延を発生させている状態で、2 つのトランザクションを同時に実行した場合、Transaction Delay は 1,000 程度の値となります。
単一のトランザクションでどれだけの遅延が発生しているかを確認するためには Transaction Delay を SQLServer:Transactions の Transactions で割ることで 1 トランザクションあたりの平均遅延 ms を算出することができます。

同期モードによる遅延ですが、SQL Server Profiler の Dulation にも現れてきます。

• 非同期モード (ネットワーク遅延なし)
• 同期モード (ネットワーク遅延なし)
• 同期モード (ネットワーク遅延あり)
• 非同期モード (ネットワーク遅延あり)

の Dulation を比較した場合、非同期モードの場合はネットワーク遅延の影響を受けませんが、同期モードの場合はネットワーク遅延の影響を受けていることが確認することができます。
また、同期モードと非同期モードを比較するとネットワーク遅延により Dulation の値に変化があることが確認できます。

なお、この時の待ち事象を確認すると以下のような情報を取得できます。

ネットワークの遅延の影響が DBMIRROR_DBM_EVENT としてあがっていることが確認できます。

なお DBM 用にシステムテーブルや DMV が用意されており、以下の情報を検索することで、エンドポイントの状態や同期の状況 (LSN) を確認することができます。

• sys.database_mirroring_endpoints
• sys.database_mirroring
• sys.database_mirroring_witness
• sys.dm_db_mi
  rroring_connections

また、ミラーリングの LSN 情報に関してはブートページ (Boot Page) にも格納されており、この辺の情報も使用できるかと。
# これは AlwaysOn 可用性グループでも同様だったはずです。

 

■Standard Edition と Enterprise Edition の違い

---

Standard Edition でもデータベースミラーリングを構築することができますが、安全性レベルが FULL (同期モード) のみが設定可能となっているほかに REDO スレッドの数に違いがあります。

これは、データベース ミラーリング セッションのために作成されるスレッド に記載されている内容になります。

• 1 つの再実行管理スレッド。再実行のためのログの送信、ページの先行読み取りの実行、ロックの再取得などを行います。

• ミラー データベースごとに 1 つの再実行スレッド (SQL Server Standard の場合)、または 4 基の CPU ごとに 1 つのミラー データベースごとの再実行スレッド (SQL Server Enterprise の場合)。実際のログの再実行はこれらのスレッドによって行われます。

Standard Edition の場合、ミラーの再実行 (REDO スレッド) がシングルスレッドで動作しますが、Enterprise Edition の場合は CPU のコア数に合わせてマルチスレッドで動作します。
# REDO スレッド数をコア数にするトレースフラグもあるようですが。

スレッドでいうと DBMParallelRedoThread になると思います。

REDO スレッドが複数起動することで、トランザクションログ → データファイルへの適用がマルチスレッドで動作するようになるため、ミラーのデータベースをプリンシパルとして使用するようになった場合 (フェールオーバー) の復旧が完了するまでの時間に影響してくると思います。

同期モードの ACK は REDO スレッドには影響しないはずですので、同期に関しての応答速度については Standard / Enterprise の影響はないかと思いますが。

 

■エンドポイント専用のネットワーク

---

データベースミラーリングではミラーリングエンドポイントを経由してログの内容が転送されます。
デフォルトでは LISTENER_IP=ALL を使用してエンドポイントが作成されます。
CREATE ENDPOINT (Transact-SQL)
ALTER ENDPOINT (Transact-SQL)

SQL Server 2008 以降のデータベースミラーリングではログが圧縮されて送信されますのでネットワークトラフィックを軽減させることができますが、エンドポイント専用のネットワークが必要になることもあると思います。
データベース ミラーリング (SQL Server)

そのような場合は LISTENER_IP で特定の IP を指定して、エンドポイント専用のネットワークを作成することができます。

構成の例としては下図のようになります。

以下は ALTER ENDPOINT を使用した、リスナーポートの変更例になります。

ALL で指定した場合はすべての IP (0.0.0.0:5022) でミラーリングのエンドポイントがリスニングされますので明示的に IP を指定する必要はあまりないかもしれないですね。

ミラーリングを設定する際に使用するエンドポイントとして、ミラーリング用の IP を指定することで、同期に使用するネットワークを固定化することができます。
# AlwaysOn 可用性グループのこの辺の考え方は同じです。

 

AlwaysOn 可用性グループでも同期モードによる遅延を確認することができます。
これについては別の投稿でまとめてみたいと思います。