SQL Server 2016/2017 可容性グループ セカンダリレプリカ redoモデルと性能

2018-09-25

MSSQL Tiger Teamが投稿したBlogを基に整理した内容です。

可容性グループはSQL Server 2012で初めてリリースされました。
可容性グループセカンダリレプリカの各データベースではトランザクションログのredoは一つのredoスレッドで制御していました。
このredoモデルは、serial redoと呼ばれていました。

SQL Server 2016で、redoモデルが強化され、redo操作を分けるためにデータベース毎に複数の並列redoワーカースレッドになりました。さらに各データベースは、ダーティページディスクフラッシュIOを管理する新しいヘルパーワーカースレッドがあります。
この新しいredoモデルは、parallel redoと呼ばれます。

新しいparallel redoは、SQL Server 2016から既定の設定で、小さいトランザクションが並列に大量に実行されるケースではredo性能の改善ができました。データの暗号化、データ圧縮の有功などのCPU負荷の高いトランザクションredo操作は、serial redoと比較してparallel redoの方が高いスループット（Redone Bytes/sec）が得られます。
さらに、間接的チェックポイントがparallel redoがヘルパーワーカースレッドにディスクIOや遅いディク用のIO waitを委任し、メインのredoスレッドがセカンダリレプリカでログレコードをより受信できるようにします。redo性能を向上させます。

しかし、parallel redoはマルチスレッドモデルでコストが高いです。

  • メインredoスレッドは、各トランザクションログredo操作の実行を停止しますが、各トランザクションログを列挙し、parallel redoワーカースレッドにディスパッチすることを担当します。テーブル（行数の小さい）の狭い範囲の行へのDMLトランザクションのredoのようなCPU負荷がかからないログredo操作のケースでは、それらのログをディスパッチするコストは非常に重いです。
  • 新しいレコードを挿入するためにページを分割するシステムトランザクションは、parallel redoワーカースレッド間でPARALLEL_REDO_TRAN_TURN waitを利用します。頻繁にinsert操作をする場合、かなりparallel redo性能を遅くします
  • 読み取りセカンダリレプリカでread-onlyクエリを実行すると、クエリスレッドはログredo操作を一時停止しようとし、DIRTY_PAGE_TABLE_LOCKでredoワーカースレッドと連携する必要があります。redoとクエリ性能が両方遅くなります。

性能研究に基づくと、次のトランザクションワークロードまたはSQL設定が既定のparallel redo モデルより良い結果となります。

  • 多くの並列の小さいトランザクション
  • 間接的なチェックポイントを伴う負荷のかかるログredo操作（データ暗号化またはデータ圧縮）
  • 読み取りをしないセカンダリレプリカ、またはたまにread-onlyクエリを読み取りセカンダリレプリカで実行する場合

次の場合は、serial redoに切り替えた方がredoスループットがよくなります。

  • 一般的なInsertや同時実効性が限られている長い時間実行するトランザクション：典型的な例は、大きなテーブルのクラスター化インデックスのオンラインIndex再構築
    • parallel redoモデルでの兆候
      • PARALLEL_REDO_TRAN_TURN waitがinsert処理量に比例して生成されます。多くのInsertが多くのpage splitを起こし、PARALLEL_REDO_TRAN_TURN waitを呼びます。
      • 性能カウンターで監視できます。（SQLServer:General Statistics、User Connections）または、プライマリレプリカのDMV（sys.dm_exec_connection、sys.dm_exec_sessions）
  • 頻繁で、長時間のread-onlyクエリをセカンダリレプリカのデータベースに実行する必要がある場合
    • parallel redoでの兆候
      • 頻繁なDIRTY_PAGE_TABLE_LOCK wait
      • 性能カウンターで監視できます（SQLServer:Database Replica、Redone Bytes/sec）クエリを実行している時としてない時でredoスループットを比較します。
  • 小さなデータセットのデータページにセカンダリレプリカでread-onlyクエリが高頻度でスキャンし、プライマリレプリカで頻繁に同じデータセットに変更を加える。クエリとredoスレッド両方に劇的な性能劣化を与えます
    • parallel redoでの兆候
      • 大量のDPT_ENTRY_LOCK wait
      • 最悪のケースでは、Latch timeoutによりSQL ErrorログにTimeout occurred while waiting for latchと記録されます。

新しいWaitの種類

いくつか紹介されていますが、個人的に興味があるものをピックアップ。

  • DPT_ENTRY_LOCK
    • ユーザークエリスレッドから追いつくためのREDOがあるdirty page entryへのアクセスを制御するロックがある時に発生します。
    • parallel redoワーカースレッドとユーザークエリが同時に同じdirty page entryにredo処理をした時のみ発生します。

補足

SQL Serverの事例として紹介されていますが、Azure SQL Database のgeo replicationを使用してセカンダリデータベースを使用しているケースでも同様の問題が発生します。
P15を使っていると、ジオレプリケーションはparallel redoが採用されているので、ここで紹介されたことと同じ問題が発生するのです。

SQL Azure

Posted by 大和屋貴仁