SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

可用性グループの同期コミットを設定した際のスループットへの影響の考え方

2 comments

AlwaysOn 可用性グループの可用性モードは同期コミットと非同期コミットの 2 種類のモードがあります。
可用性モード (AlwaysOn 可用性グループ)

今回の投稿では同期コミットを使用した場合のスループットへの影響を確認するための考え方についてまとめてみたいと思います。

以前投稿した データベースミラーリングの同期モードによる遅延について と近い内容ですね。

■クエリの実行数への影響


同期コミットを使用した場合、トランザクションの流れは以下のようになります。
# TechParty で私が実施したセッションスライドからの抜粋になります。
image

同期コミットを使用した場合はセカンダリでトランザクションログが書き込まれ、その ACK がプライマリに返ってトランザクションが完了します。
# どのタイミングで ACK が返るかは別の投稿でまとめる予定です。

非同期コミットの場合はプライマリのトランザクションログに書き込まれれば処理が完了します。

そのため、同期コミットと非同期コミットではプライマリ / セカンダリ間の同期の分の処理差が出てくることになります。
この処理量の差が Batch Requests/sec として現れてきます。

バッチは SQL のコマンドの集まりで

セッション A :
UPDATE hogehoge SET hogehoge=1

セッション B :
UPDATE hogehoge SET hogehoge=1
UPDATE hogehoge SET hogehoge=2

という 2 つのセッションで SQL を実行した場合は 2 バッチとして数えられます。

セッション A では 1 つのステートメントで 1 バッチ、セッション B では 2 つのステートメントで 1 バッチとしてあらわされます。
# ステートメントとしてみた場合は 3 ステートメントになります。

パフォーマンスモニターでこの値を取得することで、同期モードを設定した場合の処理実行数の影響を確認することができます。
更新がかからないクエリだけ実行した場合には影響は出てこないと思いますが、更新系のクエリを含めて実行した場合、同期モードの設定前と比較してバッチの実行数が低い値を示してきます。

 

■トランザクションの完了への影響


次にトランザクションの完了がどれくらい遅れているのかを見ていきたいと思います。

現在、実行されているトランザクション数については Transactions で確認することができます。
そのトランザクション数に対してコミット時に待機されている時間 (ミリ秒) が Transaction Delay として確認できます。
# Transaction Delay は実行中のトランザクション数に対しての遅延の合計のため、一つのトランザクションでそれだけ遅延が発生しているということではありません。

非同期コミットの場合は、セカンダリへの書き込みの完了を待たずにトランザクションが完結しますので、Transaction Delay は 0 となります。

同期コミットの場合は、セカンダリへの書き込みが完了するまで待機する必要がありますので Transaction Delay が発生してきます。プライマリとセカンダリ間でネットワークの遅延が恒常的に発生している場合などは、その遅延が更新系の処理にダイレクトに影響してくることになります。
可用性グループでは複数のセカンダリを設定し、同期コミットと非同期コミットのレプリカを混在できますので、同期コミットを使用する拠点間はできるだけ遅延が発生しないネットワークに配置し、ネットワーク遅延が発生する拠点に関しては非同期コミットを使用する等の設計が必要になってくるかと思います。

Transaction Delay 以外にもトランザクションの完了へどれだけ影響を及ぼしているかを確認するための方法があります。
それが、HADR_SYNC_COMMIT になります。
これは、sys.dm_os_wait_stats の項目になるのですが、この項目を確認することでもトランザクションの遅延に対しての影響を確認することができます。
先ほどの Transaction Delay と同様、非同期コミットの場合は数値は増加しないのですが、同期コミットを設定した場合は増加していきます。
sys.dm_os_wait_stats はサービスの再起動や明示的なクリア (DBCC SQLPERF) をしない限り加算されていきますので、サービスを起動してからどれくらい遅延が発生したかを確認することもできます。
# ただし、これは全トランザクションの遅延の合計になりますので、単純に数値の時間だけ直列に遅延が発生したということではありません。

 

同期モードを使用した場合のスループットへの影響はこれらの値を参考にすると見えてくるかと思います。

Share

Written by Masayuki.Ozawa

8月 30th, 2012 at 10:28 pm

Posted in SQL Server

Tagged with ,

2 Responses to '可用性グループの同期コミットを設定した際のスループットへの影響の考え方'

Subscribe to comments with RSS or TrackBack to '可用性グループの同期コミットを設定した際のスループットへの影響の考え方'.

  1. お世話になっております。
    AlwaysOn の同期コミットのタイミングでもしご存じでしたら教えてください。
    # どのタイミングで ACK が返るかは別の投稿でまとめる予定です。
    とありますが、こちらのコラムはありませんか?

    弊社で
    セカンダリーでRedoが遅れる事象が発生しており調査しております。
    セカンダリーでログのフラッシュは完了しプライマリー側にコミットは帰っているが、セカンダリーでのRedo(データファイルへの反映が遅れた場合)が遅れている場合、セカンダリー側ではそのコミットデータが見えなくなるのでしょうか?(古いデータを参照してしまう。)
    もしご存じでしたらご教授ください。

    mikami

    10 6月 22 at 10:54

  2. 10年前の内容なので書いたかは覚えていないのですが、セカンダリでRedo が遅れている場合、セカンダリで確認ができるのはデータ参照時点で Redo が完了しているデータとなるはずです。
    そのため、トランザクションログが連携されていても、Redo が実行されていないデータについては、セカンダリでは有効なデータとして見えません。

    プライマリでコミットされていても、セカンダリでは、セカンダリの Redo スレッドにより、メモリまたはデータファイルに対してログの内容が適用されないと、セカンダリで該当のデータを参照することはできません。

    Masayuki.Ozawa

    16 6月 22 at 22:56

Leave a Reply