同期 SQL コミットが VPN の往復を待つ理由

可用性グループの同期コミットにはリモートログの永続化が含まれます。帯域幅を疑う前、モードを変える前に、その経路を測ってください。

レプリカを二番目のサイトへ移したあと書き込みレイテンシが跳ね上がり、トンネルが最も新しい構成要素なので責任を負わされます。トンネルは無実かもしれません。同期コミットの可用性グループでは、プライマリはリモートのログ書き込みが完了するまでコミットを承認できないため、リンクが何メガビットを運べても、すべてのトランザクションが往復を支払います。スループットは毎秒のバイト数を測り、レイテンシは一回のやり取りにかかる時間を測り、コミットを終えるのに必要なのは一回の小さなやり取りだけです。議論を決着させるのは、小さなトランザクションのコミット所要時間を可用性データベースの内側で測り、同じインスタンス上のローカルテーブルに対してもう一度測った一つの数値です。

プライマリが実際に待つもの

同期コミットでは、プライマリはパートナーがログレコードの永続化を確認するまでコミットの承認を保留します。永続化とはセカンダリのトランザクションログへ耐久的に書き込まれることで、そのデータページへ再実行されることではありません。redo は数分遅れても、プライマリのコミットに一ミリ秒も加えません。

可用性モードに関する Microsoft のドキュメント はそのハンドシェイクを説明し、パートナーが脱落したときにプラットフォームが何をするかを述べています。同期として印を付けられたレプリカは待つことに関する規則であり、すべてのコミットに対して健全なリモートコピーが存在するという恒久的な約束ではありません。切断された経路は別に定義されており、単にコミットが失敗するという話ではありません。

つまり観測される待ち時間は合計です。ログの転送、両ホストでのスケジューリング、そしてリモートストレージへの書き込みです。ログボリュームが飽和したセカンダリは、どの速度テストにも現れない時間を追加します。そのディスクは、苦情が VPN を名指しするときにはほとんど測られません。

帯域幅は往復遅延ではない

速度テストは経路を短時間あふれさせて速度を報告します。コミットが求めるのはもっと狭いものです。小さなメッセージが反対側に届き、永続化され、戻ってくるまでの時間です。経路の長さ、待ち行列、パケット損失がその数値を決め、プロバイダーが上限を上げてもどれも改善しません。

したがって「高速な」トンネルは、多数の小さな逐次トランザクションで構成される書き込み重視のワークロードに目に見えるペナルティを加えることがあります。同じ総バイト数を千のコミットに分割すれば往復を千回支払い、一括転送なら一度で済みます。

ネットワークの所有者に、代表的な負荷の下でレプリカ経路そのものの遅延と損失を測ってもらいましょう。オフィスのノート PC から VPN ゲートウェイへの ping、あるいは近くのノードへの速度テストは、ログが実際に通る経路にはほとんど何も帰属させません。測定ウィンドウが書き込みの遅い時間帯と重なるようにしてください。

何かを変える前にレプリカの状態を読む

まずインスタンスが自分について報告する内容から始めます。パートナーシップがどれほど健全かについて両側の見解が食い違うことがあり、ダッシュボードの平均はどの待ち行列が動いているかを隠します。下のクエリは別々の問いに答えます。一方はデータベースごとのレプリケーション状態を読み、もう一方は各レプリカの構成を読みます。モードに触れる前に両方を実行してください。

SELECT ag.name AS ag_name, drs.database_id, drs.synchronization_state_desc,
       drs.log_send_queue_size, drs.redo_queue_size, drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id;

SELECT ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc
FROM sys.availability_replicas AS ar;

最初の結果はログ転送と redo を分けます。log_send_queue_size が上昇する一方で redo_queue_size が横ばいなら、ログが届いていません。逆なら届いていて再生が遅れています。コミット経路に乗るのは最初の場合だけです。

二番目の結果は、どのレプリカが本当に想定どおりに構成されているかを確認します。以前のインシデントで変更されたモードは、変更されたまま文書化されずに残りがちだからです。グループの既定値ではなく、関心のある行を読んでください。availability_mode_desc と failover_mode_desc はレプリカごとです。secondary_lag_seconds は SQL Server 2016 以降にのみ存在するため、古いビルドでは送信キューと redo の列だけが残ります。

次に、ダッシュボードの平均を信じるのではなく、代表的な書き込みを計測します。下のブロックを可用性データベースで実行し、同じインスタンスのグループ外のデータベースで同じテーブル形状に対して再度実行します。二つの所要時間の差はリモートログの大まかな価格であり、ネットワークチームとの議論ではグラフよりはるかに持ちこたえます。その後は一度に一つの変数だけを変えてください。そうでなければ差は意味を失います。

IF OBJECT_ID('dbo.CommitProbe') IS NULL
    CREATE TABLE dbo.CommitProbe (probe_id int PRIMARY KEY, touched_at datetime2(3));
IF NOT EXISTS (SELECT 1 FROM dbo.CommitProbe WHERE probe_id = 1)
    INSERT INTO dbo.CommitProbe (probe_id) VALUES (1);
SET STATISTICS TIME ON;
BEGIN TRANSACTION;
UPDATE dbo.CommitProbe SET touched_at = SYSUTCDATETIME() WHERE probe_id = 1;
COMMIT TRANSACTION;
SET STATISTICS TIME OFF;

非同期コミットが買うもの、そして払うもの

非同期コミットでは、プライマリはリモートの永続化確認を待つのをやめるため、往復はコミット経路から外れます。コミットのレイテンシは下がり、セカンダリは誰も制御しない分だけ遅れる自由を得ます。それは待ち時間への実際の修正であり、約束への実際の変更です。

代償はリカバリポイントの判断です。プライマリが、実際に使えるフェールオーバー先へログが届く前に死ぬと、サービスを非同期コピーへ移すことは、アプリケーションがすでにコミット済みと報告したトランザクションを捨てます。どの構成ダイアログもそうは提示しません。これは許容できる損失についてのビジネスの問いであり、パフォーマンス設定ではないからです。許容する損失の窓を、インシデントの前に書き留めてください。インシデントの最中ではなく。

データを所有する人とモードを書面で合意し、優先ターゲットが利用できない場合のフェールオーバー手順も添えてください。設計文書の同期は運用ランブックではなく、非同期は待ち行列の監視をやめる許可でもありません。

後から弁護できる修正を選ぶ

正直な選択肢は四つあります。アプリケーションの意味論がバッチ処理を許すところでは避けられるコミットの往復を削り、測定した転送やストレージのボトルネックを直し（大きなパイプではなくセカンダリのより速いログボリュームを意味するかもしれません）、レプリカを近づけて往復を縮め、あるいは保護ポリシーを緩めてより広い損失の窓を受け入れることです。どれもレイテンシを何か別のものを費やして買い、四番目は耐久性を費やします。選ぶ前に費やすものを名指ししてください。

一方を最適化することはもう一方を静かに費やすため、ワークロードのレイテンシ目標と耐久性の要件を同時に目の前に置いて決めてください。そのうえで、変更前に計測した同じトランザクションを再実行します。その数値を動かせない修正は、変更チケットを着た推測です。

変更記録に前後の測定値を残してください。そうでなければ、遅い書き込みを見た次の人が調査を最初からやり直し、またトンネルを疑うところから始めます。

関連記事