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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

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チームテックリード廣瀬真輝

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. 障害の原因調査に用いたツール 1.障害発生時に手動で収集しておいた動的管理ビュー（以下、DMV）の情報 2.監視製品のSpotlight Spotlightとは・・・サーバーのメトリクスを定期的に収集＋任意の時間帯の状況を事後確認可能・CPU/メモリなどのリソース状況・秒間バッチ実行数、コネクション数・実行中のクエリテキスト etc..

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

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 → ボトルネックは物理リソースでなく、論理リソース

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. 障害の原因原因：「ワーカースレッドの枯渇により大量のTHREADPOOL waitが発生したため」 ↓ 疑問：「ワーカースレッドの最大数を増やせば解決するのでは？」 ↓ 現状でも平常時の5倍の要求を一時的に受け付けているため、そう単純でもない。疑問：「なぜワーカースレッドは枯渇したのか？」

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. ここまでのまとめ「なぜ大量のエラーが発生し障害につながった？」 ↓ 「ワーカースレッドが枯渇したため」 ↓ 「なぜワーカースレッドが枯渇した？」 ↓ 「ページラッチ待ち/ロック待ち多発でスロークエリ大量発生 →ワーカースレッド解放に時間がかかったため」 ↓ 「なぜページラッチ待ち/ロック待ちが大量に発生した？」

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. 「スリープしてるのにブロックしてる」とは https://dba.stackexchange.com/questions/41709/sleeping-spid- blocking-other-transactions 明示的なトランザクションを張ったまま途中でタイムアウトしてしまうと、クエリの実行方法によっては、ロールバックされずトランザクションが継続された状態になる。したがってロックを獲得している場合はロックも保持し続けることになる。 →ローカル環境にて確かに再現した。

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. 対策1. プロセスをSleepingかつBlockingにさせない・タイムアウト時のトランザクションを適切に処理する必要がある・オプション「SET XACT_ABORT ON」をトランザクション開始前に設定 →トランザクション内でエラーが発生すると即座にロールバック＋ロックを解放・特定のクエリがタイムアウトした途端にブロッキングが加速するリスクを無くす

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. THREADPOOL この待ち事象を減らすためにはブロッキングを減らす必要があるが、関連する対応として以下の2つを実施・ワーカースレッド数を規定値の2倍に設定・maxサーバーメモリの値を減少 →ワーカースレッドはバッファプールから確保している領域と別領域からメモリ確保が必要になるため本対応により、大量にワーカースレッドを使用する環境下においては同時実行性能の向上が期待できる

Slide 41

Slide 41 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. LCK_M_U / LCK_M_S ■ LCK_M_U ・人気商品の在庫に関するデータを分割して販売 →在庫に関するデータレコードの更新処理の分散目的 →サイト上の見え方およびユーザーには影響ない形で分割した ■ LCK_M_S ・一部のクエリにwith(nolock)というロックヒントを付与 →Sロックよりも競合が少ないSch-Sロックを取得するに留めさせた →ダーティリードを許可できる箇所のみ

Slide 42

Slide 42 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. PAGELATCH_SH / PAGELATCH_EX 前提：獲得できるページラッチは1ページあたり1つだけのため、同一ページ内の異なるレコードへの更新要求同士であってもページラッチの競合は発生前提を踏まえ、以下の対応を実施した。・1ページの中に福袋の在庫に関するレコードが1つだけ存在する状況にした →福袋商品以外は、サイト上からは見えないダミーのレコードで埋めた

Slide 43

Slide 43 text

Slide 44

Slide 44 text

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Slide 48

Slide 48 text

Slide 49

Slide 49 text

Slide 50

Slide 50 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. なぜDAC？ワーカースレッドが枯渇して大量のエラーが発生しているケースだと、 SSMSで接続して手動クエリを実行することすらできない →DACは管理者専用に1つだけ専用ワーカースレッドを確保ワーカースレッド枯渇時も確実に接続でき、情報収集できるただし、sysadminなのでクエリ実行に緊張感は伴う →普通にログインできるときは通常のログイン、だめなときはDACという切り分け

Slide 51

Slide 51 text

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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 →ワーカースレッド獲得待ち

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. リアルタイム調査のポイント・異変に気付きやすいのは「現在実行中のクエリリスト」 →平常時よりレコード数がかなり多いときはだいたい何かがおかしくなりつつある or 既におかしい →逆に、平常時とレコード数があまり変わらないときは、他のクエリで情報取得しなくても問題は起きていない可能性が高い・異変に気づけても、リアルタイムで対応できることは限られる →当日の監視は、基本的にはサーバーがポテンシャルの限界まで性能を発揮しているかを見守る作業 →ただし、sleeping/blockingなプロセスのKILLや非効率な実行プランのリコンパイルや関連テーブルの統計情報更新など、場合によっては対処も可能

Slide 58

Slide 58 text

Slide 59

Slide 59 text

Slide 60

Slide 60 text

Slide 61

Slide 61 text

Slide 62

Slide 62 text

Slide 63

Slide 63 text

Slide 64

Slide 64 text

Slide 65

Slide 65 text

Copyright © ZOZO Technologies, Inc. All Rights Reserved. まとめ - 福袋対策を通して学んだこと・原因調査の大切さ今回は物理リソースのボトルネックではなかったため、サーバーのスケールアップでは効果が無かった可能性あり。「原因特定→対応実施」を徹底すべき・当日の監視では、起こりうる障害を想定し、とれるアクションを用意「sleeping/blocking」なプロセスが原因でブロッキングが加速することに備えて、プロセスをKILLする準備をしておく等