ZOZOTOWNで最大級のトラフィックを記録する 福袋発売イベントで実施した負荷対策と、当日の監視体制について / sqlserver-luckybag

1. ZOZOTOWNの福袋イベントおよび、以前発生した障害について
2. 障害の原因調査
3. 調査結果を元に実施した負荷対策
4. 当日の監視体制

Transcript

1. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 株式会社ZOZOテクノロジーズ 開発部 基幹SREチーム テックリード 廣瀬 真輝 ZOZOTOWNで最大級のトラフィックを記録する 福袋発売イベントで実施した負荷対策と、当日の監視体制について
2. Copyright © ZOZO Technologies, Inc. All Rights Reserved. プロフィール ZOZOTOWNに関わるSQL Serverのアセスメント、チューニング、トラブルシューティング等に従事。 https://qiita.com/maaaaaaaa https://social.msdn.microsoft.com/profile/maaaaaaaa8/ https://github.com/masaki-hirose/ https://techblog.zozo.com/entry/sqlserver-tuning-luckybag https://techblog.zozo.com/entry/sqlserver-troubleshooting-statistics 株式会社ZOZOテクノロジーズ 開発部 基幹SREチーム テックリード 廣瀬 真輝
3. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 本日の内容 1. ZOZOTOWNの福袋イベントおよび、以前発生した障害について 2. 障害の原因調査 3. 調査結果を元に実施した負荷対策 4. 当日の監視体制
4. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 1. ZOZOTOWNの福袋イベントおよび、以前発生した障害について
5. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 福袋イベント「ZOZO福袋2019」とは ・ZOZOTOWNの年末の風物詩的イベント。多数のブランドの福袋が一斉に発売 ・年間を通して最も多くのトラフィックを記録するイベントの1つ
6. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 2017年の福袋イベントで発生した障害
7. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 注文数の推移からみる障害の影響 2017年（障害発生時） 2018年（負荷対策実施後）
8. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ここまでのまとめ ・2017年の福袋では、大規模な障害によって一時的に買い物し辛い状態に ・原因調査および対策を実施したことで、2018年の福袋では障害を回避した
9. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ここから登場するDBについて ・ZOZOTOWNを構成するDBのうち、障害原因となっていたDBのみに着目
10. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 2. 障害の原因調査
11. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 障害の原因調査に用いたツール 1.障害発生時に手動で収集しておいた動的管理ビュー（以下、DMV）の情報 2.監視製品のSpotlight Spotlightとは・・・ サーバーのメトリクスを定期的に収集＋任意の時間帯の状況を事後確認可能 ・CPU/メモリなどのリソース状況 ・秒間バッチ実行数、コネクション数 ・実行中のクエリテキスト etc..
12. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 秒間のバッチ実行数
13. Copyright © ZOZO Technologies, Inc. All Rights Reserved. コネクション数
14. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 上位の待ち事象（sys.dm_os_wait_stats） 平常時 障害発生中
15. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 障害発生中の上位5つのWaitType
16. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 上位の待ち事象から推定されるボトルネック CPU / Memory / DiskIOに関連した以下の待ち事象は、 障害中は無視できるほど小さな割合しか占めていなかった CPU : SOS_SCHEDULER_YIELD Memory : RESOURCE_SEMAPHORE / RESOURCE_SEMAPHORE_QUERY_COMPILE DiskIO : PAGEIOLATCH_SH / PAGEIOLATCH_EX → ボトルネックは物理リソースでなく、論理リソース
17. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 圧倒的に多かったTHREADPOOLと障害の関連調査 ・ローカル環境のDBでワーカースレッドを意図的に枯渇させてクエリを実行 →本番環境で多発していたものと同様のエラーが発生
18. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 障害の原因 原因：「ワーカースレッドの枯渇により大量のTHREADPOOL waitが発生したため」 ↓ 疑問：「ワーカースレッドの最大数を増やせば解決するのでは？」 ↓ 現状でも平常時の5倍の要求を一時的に受け付けているため、そう単純でもない。 疑問：「なぜワーカースレッドは枯渇したのか？」
19. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 秒間のバッチ実行数
20. Copyright © ZOZO Technologies, Inc. All Rights Reserved. エラー発生時に実行中だったクエリリスト
21. Copyright © ZOZO Technologies, Inc. All Rights Reserved. どのページへのページラッチ要求が集中していた？ Last Wait Resourceの値と、DBCC PAGE()を使ってページの中身をダンプ
22. Copyright © ZOZO Technologies, Inc. All Rights Reserved. どのレコードへのロック要求が集中していた？ Last Wait Resourceの値を分解して クエリにあてはめる KEY:DBID:hobt_id(%%lockres%%)
23. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 調査の結果：人気ブランドの福袋へのアクセス集中 ・ページラッチとロックは同じテーブルの特定レコードに関連する待ちと判明 ・人気福袋の購入希望者が殺到したことで読み取り要求、更新要求の競合が多発 →在庫に関するデータの更新要求や、在庫情報の読み取り要求など
24. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 障害発生時のラッチ/ロック競合のイメージ図
25. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 待ち事象同士の関係性 ・ページラッチ待ち/ロック待ちで待っているクエリは、実行中の全クエリの約半分 →通常よりワーカースレッド解放に時間がかり、徐々にワーカースレッドが枯渇
26. Copyright © ZOZO Technologies, Inc. All Rights Reserved. SQL ServerのCPUのアーキテクチャの概略図
27. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
28. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ここまでのまとめ 「なぜ大量のエラーが発生し障害につながった？」 ↓ 「ワーカースレッドが枯渇したため」 ↓ 「なぜワーカースレッドが枯渇した？」 ↓ 「ページラッチ待ち/ロック待ち多発でスロークエリ大量発生 →ワーカースレッド解放に時間がかかったため」 ↓ 「なぜページラッチ待ち/ロック待ちが大量に発生した？」
29. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ブロッキングの状況 青色 ： head blocker オレンジ色 ： blocked
30. Copyright © ZOZO Technologies, Inc. All Rights Reserved. Head Blocker が sleeping/blocking
31. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 「スリープしてるのにブロックしてる」とは https://dba.stackexchange.com/questions/41709/sleeping-spid- blocking-other-transactions 明示的なトランザクションを張ったまま途中でタイムアウトしてしまうと、 クエリの実行方法によっては、ロールバックされずトランザクションが継続 された状態になる。 したがってロックを獲得している場合はロックも保持し続けることになる。 →ローカル環境にて確かに再現した。
32. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ロールバック（＝ロック解放）のタイミングは？ 「コネクションプールに戻ったコネクションが別のクエリで再利用されるとき」 →0.1秒後かもしれないし、数分後かもしれない。 ただし、コネクションの再利用時にまずワーカースレッドを確保する必要がある。 （※検証の結果から得た結論）
33. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 調査結果から推定した障害発生までのシナリオ
34. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ここまでのまとめ 「なぜ大量のエラーが発生し障害につながった？」 ↓ 「ワーカースレッドが枯渇したため」 ↓ 「なぜワーカースレッドが枯渇した？」 ↓ 「ページラッチ待ち/ロック待ち多発でスロークエリ大量発生 →ワーカースレッド解放に時間がかかったため」 ↓ 「なぜページラッチ待ち/ロック待ちが大量に発生した？」
35. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ここまでのまとめ 「なぜページラッチ待ち/ロック待ちが大量に発生した？」 ↓ 「タイムアウトしたプロセスがロックを解放しなかったため ブロッキング状況が急激に悪化した」
36. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ここまでのまとめ ・昨年の障害発生時には、以下の五項目の待ち時間が圧倒的に多かった
37. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 3. 調査結果を元に実施した負荷対策
38. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 対策1. プロセスをSleepingかつBlockingにさせない ・タイムアウト時のトランザクションを適切に処理する必要がある ・オプション「SET XACT_ABORT ON」をトランザクション開始前に設定 →トランザクション内でエラーが発生すると即座にロールバック＋ロックを解放 ・特定のクエリがタイムアウトした途端にブロッキングが加速するリスクを無くす
39. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 対策2. 障害時に多く発生していた待ち事象を減らす 以下の5つの待ち事象を減らすための対応を実施した
40. Copyright © ZOZO Technologies, Inc. All Rights Reserved. THREADPOOL この待ち事象を減らすためにはブロッキングを減らす必要があるが、 関連する対応として以下の2つを実施 ・ワーカースレッド数を規定値の2倍に設定 ・maxサーバーメモリの値を減少 →ワーカースレッドはバッファプールから確保している領域と別領域から メモリ確保が必要になるため 本対応により、大量にワーカースレッドを使用する環境下においては 同時実行性能の向上が期待できる
41. Copyright © ZOZO Technologies, Inc. All Rights Reserved. LCK_M_U / LCK_M_S ▪ LCK_M_U ・人気商品の在庫に関するデータを分割して販売 →在庫に関するデータレコードの更新処理の分散目的 →サイト上の見え方およびユーザーには影響ない形で分割した ▪ LCK_M_S ・一部のクエリにwith(nolock)というロックヒントを付与 →Sロックよりも競合が少ないSch-Sロックを取得するに留めさせた →ダーティリードを許可できる箇所のみ
42. Copyright © ZOZO Technologies, Inc. All Rights Reserved. PAGELATCH_SH / PAGELATCH_EX 前提：獲得できるページラッチは1ページあたり1つだけのため、同一ページ 内の異なるレコードへの更新要求同士であってもページラッチの競合は発生 前提を踏まえ、以下の対応を実施した。 ・1ページの中に福袋の在庫に関するレコードが1つだけ存在する状況にした →福袋商品以外は、サイト上からは見えないダミーのレコードで埋めた
43. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 実施した対応のイメージ図
44. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 2017年との比較
45. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 負荷対策の結果 2017年（障害発生時） 2018年（負荷対策実施後） DB起因のエラーを昨対比で99.99％以上削減した。
46. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 4. 当日の監視体制
47. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 情報収集は大きく分けて2種類 ①リアルタイム調査のための情報 →手動収集 →何か起きたときにどう動くべきか決めるための情報 ②事後調査のための情報 →自動収集 →障害発生の有無に関わらず、高負荷時のサーバー状態を事後調査 次回の高負荷イベントに向けたサーバーチューニング実施に役立つ
48. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ①リアルタイム調査のための情報
49. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 事前準備 ・SSMSでDAC(管理者用の診断接続)による接続を事前に実施 １．「データベースエンジンクエリ」ボタンを押す ２．「ADMIN:サーバー名」と入力してログイン
50. Copyright © ZOZO Technologies, Inc. All Rights Reserved. なぜDAC？ ワーカースレッドが枯渇して大量のエラーが発生しているケースだと、 SSMSで接続して手動クエリを実行することすらできない →DACは管理者専用に1つだけ専用ワーカースレッドを確保 ワーカースレッド枯渇時も確実に接続でき、情報収集できる ただし、sysadminなのでクエリ実行に緊張感は伴う →普通にログインできるときは通常のログイン、 だめなときはDACという切り分け
51. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ①リアルタイム調査のための情報 ・DMVを使った情報取得 1. 現在実行中のクエリリスト 2. ブロッキングチェーンの取得 3. ワーカースレッド数 4. コネクション数 5. KILL対象のプロセスリスト
52. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 1.現在実行中のクエリリスト ↓のように、blk_spidに0以外の数字が表示される場合は、そのプロセスIDによってブロックされていると判断 ★見るべきポイント ・通常時と比べて、大量にレコードが取得できていないか ・ブロッキングは起きていないか ・wait_typeおよびlast_wait_type ・同じようなクエリが大量に取得されていないか 参考：現在実行中クエリのリアルタイムトラブルシューティング https://qiita.com/maaaaaaaa/items/83e4f984e63fee4dae34 ★クエリ https://github.com/masaki-hirose/SQLServer- Info/blob/master/%E7%8F%BE%E5%9C%A81%E7%A7%92 %E4%BB%A5%E4%B8%8A%E5%AE%9F%E8%A1%8C%E4 %B8%AD%E3%81%AE%E3%82%AF%E3%82%A8%E3%83 %AA%E3%83%AA%E3%82%B9%E3%83%88.sql
53. Copyright © ZOZO Technologies, Inc. All Rights Reserved. よくみるwait_type PAGEIOLATCH_SH →物理ディスク読み込み完了待ち RESOURCE_SEMAPHORE / RESOURCE_SEMAPHORE_QUERY_COMPILE →メモリリソースの獲得待ち SOS_SCHEDULER_YIELD → CPUリソースの獲得待ち LCK_M_*** →ロックの獲得待ち ASYNC_NETWORK_IO →プログラム側でレコードセットの処理完了待ち THREADPOOL →ワーカースレッド獲得待ち
54. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 2.ブロッキングチェーンの取得 ブロッキングが起きている場合は、以下のような情報が取得できる。 ★見るべきポイント ・HeadBlockerはどんなクエリか ・HeadBlockerの数 ・HeadBlockerのStatusは？(Sleepingならkillを検討） ★クエリ（Microsoft MVP 小澤さんのクエリを使用) https://github.com/MasayukiOzawa/SQLServer- Util/blob/master/Lock/%E3%83%96%E3%83%AD%E3%83 %83%E3%82%AD%E3%83%B3%E3%82%B0%E3%83%81 %E3%82%A7%E3%83%BC%E3%83%B3%E3%81%AE%E5 %8F%96%E5%BE%97.sql
55. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 3.ワーカースレッド数 / 4.コネクション数 ★見るべきポイント ・現在のワーカースレッド数が、最大数に近づいていっていないか ・コネクション数がSQL Serverの最大値(32676)に近づいていっていないか ★クエリ https://github.com/masaki-hirose/SQLServer- Info/blob/master/%E3%82%B3%E3%83%8D%E3%82%AF %E3%82%B7%E3%83%A7%E3%83%B3%E6%95%B0.sql https://github.com/masaki-hirose/SQLServer- Info/blob/master/%E3%83%AF%E3%83%BC%E3%82%AB %E3%83%BC%E3%82%B9%E3%83%AC%E3%83%83%E3 %83%89%E6%95%B0.sql
56. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 5.KILL対象プロセスの抽出 ブロッキング発生している場合は、sleeping/blockingなプロセスを手動でKILLすることで解消できる場合がある。 sleeping/blocking : 明示的なトランザクション開始後にタイムアウトした場合等で、トランザクション開きっぱなしになる ことがある（理想的にはコーディング時のエラーハンドリングで回避すべき） ★見るべきポイント ・blocked_process_cntが1以上のクエリがとれた場合は、該当プロセスのKILLを検討する ★クエリ https://github.com/masaki-hirose/SQLServer- Info/blob/master/sleeping%E3%81%8B%E3%81%A4blockin g%E3%81%AA%E3%82%AF%E3%82%A8%E3%83%AA.sql
57. Copyright © ZOZO Technologies, Inc. All Rights Reserved. リアルタイム調査のポイント ・異変に気付きやすいのは「現在実行中のクエリリスト」 →平常時よりレコード数がかなり多いときは だいたい何かがおかしくなりつつある or 既におかしい →逆に、平常時とレコード数があまり変わらないときは、 他のクエリで情報取得しなくても問題は起きていない可能性が高い ・異変に気づけても、リアルタイムで対応できることは限られる →当日の監視は、基本的にはサーバーがポテンシャルの限界まで性能を発揮しているかを見守る作業 →ただし、sleeping/blockingなプロセスのKILLや 非効率な実行プランのリコンパイルや関連テーブルの統計情報更新など、場合によっては対処も可能
58. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ②事後調査のための情報
59. Copyright © ZOZO Technologies, Inc. All Rights Reserved. なぜ事後調査？ ・イベント当日に問題が起きた場合 →原因調査 ・イベント当日に問題が起きなかった場合 →高トラフィックな状況下でのサーバー状態を見ることで 今後対応すべき課題が無いか調査 いずれにせよ事後調査は実施したほうが◎
60. Copyright © ZOZO Technologies, Inc. All Rights Reserved. ②事後調査のための情報 1. DVMを使ったクエリの定期ダンプ 2. パフォーマンスモニタ(perfmon) 3. サーバーサイドトレース / 拡張イベント
61. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 1.DMVを使ったクエリの定期ダンプ sys.dm_os_wait_statsなど、各種DMVの情報をselectして ローカルDBの新規テーブルに保存する仕組みを整備し、ジョブで定期実行
62. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 2.パフォーマンスモニタ(perfmon) ・パフォーマンスモニタの各種メトリクスを採取 ・ZOZO福袋時では、イベント前日と当日において採取 →平常時のベースラインとして使用するため
63. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 3.サーバーサイドトレース / 拡張イベント ・サーバーサイドトレース →負荷をかけたくないため基本的に未使用 ・拡張イベント →負荷の観点から必ず「複数のイベントの損失」を選択 スロークエリだけを取得するなど、限定的な情報取得が現実的
64. Copyright © ZOZO Technologies, Inc. All Rights Reserved. 事後調査のポイント ・問題が発生していた場合は、原因追及に焦点をあてる ・問題が発生していない場合は、今後問題となりそうな箇所がないかの 調査に焦点をあてる
65. Copyright © ZOZO Technologies, Inc. All Rights Reserved. まとめ - 福袋対策を通して学んだこと ・原因調査の大切さ 今回は物理リソースのボトルネックではなかったため、サーバーのスケールアップ では効果が無かった可能性あり。「原因特定→対応実施」を徹底すべき ・当日の監視では、起こりうる障害を想定し、とれるアクションを用意 「sleeping/blocking」なプロセスが原因でブロッキングが加速することに備えて、 プロセスをKILLする準備をしておく等