トラブルの原因特定率を劇的に向上させるSQL Serverロギングの仕組み作り / sql-server-logging-for-troubleshooting

トラブルの原因特定率を劇的に向上させる SQL Serverロギングの仕組み作り  株式会社ZOZO  技術本部技術戦略部アーキテクトブロックテックリード  Microsoft MVP
for Data Platform  廣瀬真輝 Copyright © ZOZO, Inc.

© ZOZO, Inc. 株式会社ZOZO  技術本部技術戦略部アーキテクトブロック  テックリード廣瀬真輝 
• 2013年に新卒で入社し9年目  • データベース領域における社内の標準化や仕組み作り  • SQL Serverに関するプロダクト横断での課題解決  • Microsoft MVP for Data Platform（2020～）  2

© ZOZO, Inc. 背景  • 数値ベースのメトリクス：監視製品の導入で容易に可視化  ◦ CPU使用率  ◦ Disk負荷 
◦ ワーカースレッド数  ◦ etc.  5

© ZOZO, Inc. 数値ベースのメトリクスから分かること  6 • クエリタイムアウト多発などの障害時に  ◦ 「CPU使用率が100％付近で推移していた」  ◦
「ワークスペースメモリの獲得待ちが多く発生していた」  などの事象を確認可能 

© ZOZO, Inc. 例：CPU高負荷問題を解決したい  • CPU高負荷な時間帯の  ◦ 実行中だったクエリのリスト  ◦ 各クエリの実行回数やCPU使用時間 
が分かれば、クエリレベルでの根本原因の特定が可能  8

© ZOZO, Inc. 原因を特定して具体的なアクションに繋げる  9 • とり得るアクションは多岐にわたる  ◦ クエリチューニング  ◦
インデックス作成  ◦ 定期的な統計情報の更新  ◦ トランザクション分離レベルの変更  ◦ システムテーブルの不要データ削除  ◦ アーキテクチャの再考  ◦ etc.  • 課題を解決するアクションをとるために原因特定が重要  • 原因特定率を向上させるためにはSQL Serverのロギングを  充実させることが重要   

© ZOZO, Inc. ロギング機能を備えた監視製品の例  • Quest社の「Spotlight」  ◦ 「現在実行中のクエリ」を約1分間ごとに保存してUI上で後追い可能  ◦ 様々なメトリクスやレポートも確認でき便利な製品 
◦ もの足りない点もある  10

© ZOZO, Inc. １．最短1分というインターバルが長い  • OLTPワークロードメインのWebサービス  ◦ クエリタイムアウト時間は数秒～数十秒  ◦ 例：5秒でタイムアウトするWebサービスの実行中クエリを1分ごとにロギング 
▪ クエリの情報がログに保存される可能性は低い  ▪ タイムアウトした原因を後追いできる可能性も低い    11

© ZOZO, Inc. ２．取得したい情報を拡充できない  • テーブルの統計情報の更新日時を後追いしたい場合  ◦ Spotlightでは調査できない  ◦ 原因調査ではこの情報が重要な手がかりになることもある 
  • ロギングの仕組みを内製すれば簡単に取得情報の拡充が可能  12

© ZOZO, Inc. ここまでのまとめ  • SQL Serverの障害対応では  ◦ CPU使用率などの数値メトリクス  ◦
実行中のクエリリストなどのログ情報  の２種類の情報を組み合わせることで原因を特定して  具体的なアクションへと繋げることができる    • ロギング機能のある監視製品もあるが  ◦ Webサービスにフィットする短いロギングのインターバル  ◦ 柔軟な取得情報の拡充  という２点を実現するためにロギングの仕組みを内製化した      13

© ZOZO, Inc. 本セッションのゴール  • SQL Serverに関連したトラブル調査を後追いで実施するための  情報収集の仕組みを理解できる  • 実装のイメージがわく 
14

© ZOZO, Inc. 17 SQL Server障害調査フロー  • 監視製品 or パフォーマンスモニタ
      • ロギングの仕組み  ◦ 拡張イベント  ◦ DMV（動的管理ビュー）  の2つを元データとして使用 

© ZOZO, Inc. DMVを元にしたログ  • DMV（Dynamic Management View / 動的管理ビュー） 
◦ SQL Serverの状態情報が格納されたView  ◦ 2016で約240種類 / 2019で約280種類  • スコープが2種類  ◦ サーバースコープ（＝どのDBでSELECTしても結果が同じ）  ◦ DBスコープ（＝各DBによってSELECTの結果が変わる）  • 内部状態を管理しているViewなので、SELECTしか実行できない  ◦ 更新はSQL Server内部で自動的に行われる  • SELECTするときは、スキーマ名（sys）をつける  ◦ 例：SELECT * FROM sys.dm_os_wait_stats  18

© ZOZO, Inc. DMVクエリの制約  • 取得できる情報はあくまで「現在の情報」  ◦ 過去に遡った分析はできない  ◦ 「08/01
22:00時点で1秒間実行中だったクエリ」は分からない  • DMVクエリごとに専用のテーブルを作成  ◦ 1分間隔など定期的に実行結果をINSERT  ◦ 特定日時のDBの状態を後追いできるようになる  20

© ZOZO, Inc. 拡張イベントを元にしたログ  • 拡張イベント  ◦ SQL Serverに関する様々なイベントの発生を収集できる機能  ◦
ログイン / リコンパイル / クエリの中断 / ブロッキングの発生 etc.  • ブロッキングの検出に拡張イベントを使用  ◦ ブロッキングイベントはxmlフォーマットで保存  ◦ xmlをパースすることでクエリベースでの解析も可能  ◦ イベント数が多い環境下ではクエリ実行に数分以上かかることも  • DMVのロギングと同様に専用のテーブルを作成  ◦ xmlをパースしたクエリ実行結果を定期的に保存  ◦ クエリの実行時間が劇的に短縮化され調査スピードが向上  21

© ZOZO, Inc. 工夫した点①：情報ごとに適切な取得間隔を設定  • DBのデータサイズ  ◦ 1日ごとの推移が確認できればOK  • ブロッキングイベント 
◦ パースクエリは実行時間が長くなるため、1時間に1回まとめてデータ保存  • 現在実行中のクエリ  ◦ OLTPワークロードの後追い用に5秒間隔  ◦ バッチ処理の後追い用に1分間隔でそれぞれ取得    • 適切な間隔でロギング → 欲しい情報を取得できる確率が向上  25

© ZOZO, Inc. 工夫した点②：解析の精度向上  • BEFORE：2点間の差分で計算  ◦ ある程度の精度はでる  ▪ Snapshot①と②の間隔が数分など短い場合 
▪ メモリに余裕がありほぼキャッシュアウトされない環境    ◦ 精度低下の懸念があるケースも  ▪ 1時間や1日単位でボトルネック調査をしたい場合  ▪ 頻繁にキャッシュアウトされる環境  • Snapshotタイミングの間でキャッシュインとキャッシュアウトが発生（A）  • Snapshot②を取得する前にキャッシュアウトされる（B）  27 A B

© ZOZO, Inc. オーバーヘッド  • DMVを使った定期的なクエリ実行も拡張イベントの取得も  オーバーヘッドはかかる  ◦ 弊社では許容できる範囲内と判断  ◦
環境ごとに状況は異なるため、パフォーマンスへの影響は要確認  • 推奨の適用順序  　１．パフォーマンスカウンタを収集してパフォーマンスのベースラインを設定  　２．DMVクエリや拡張イベントを順次有効化  　３．有効化前後でCPU、メモリ、I/Oなどのリソース変化を確認し、  　　　オーバーヘッドが許容できるかを判断  　  　SQL Server:Batch Resp Statisticsを使って実行時間とCPUに関する  　クエリ分布の変化を確認するのも有効  29

© ZOZO, Inc. クエリストアとの使い分け  • クエリストア（2016以降）で以下のような調査が可能  ◦ クエリプランの後退が発生しているクエリの特定とプランの修正  ◦ CPUや実行時間、I/Oなどリソース消費量の多いクエリの特定 
▪ クエリストアで取得可能な情報についてはクエリストア使用したほうが簡単かつ高精度    • 内製ロギングの情報を使用するシナリオ  ◦ クエリストアの設定値よりも短い時間枠で調査したい  ▪ クエリストア：15分や30分などの時間枠を設定することが多い  ▪ 「特定の5分間で最もCPUを消費したクエリを特定したい」→ 内製ロギングが役立つ可能性  ◦ クエリストアでは取得されない情報を確認したい  ▪ 各インデックスごとのseek、scan、lookup回数など  30

© ZOZO, Inc. ロギングする情報の継続的な拡充  • 様々な情報を収集しているが、原因特定できないケースもある  ◦ 「どんな情報があれば原因を後追いできたか？」という疑問を出発点にして  取得する情報を拡充  ◦
ログを使った調査と取得情報の拡充というサイクルを回し続けることで  原因特定に至る確率が向上  31

© ZOZO, Inc. 仕組みの改善  • 現在抱えている課題：各DBに直接ログを保存しているので  ◦ ロギングの仕組みの修正コストが高い  ◦ ログ容量を気にする必要がある 
◦ 多少の負荷増につながっている  • ログの収集基盤を別途構築して解決したい  33

© ZOZO, Inc. 本セッションのまとめ  • SQL Serverに関連したトラブル調査を後追いで実施するための  情報収集の仕組みについて説明  ◦ この仕組みでSQL
Server起因のトラブルの原因特定率が劇的に向上  • 使用しているロギング用のクエリをOSSとしてGitHubに公開中  36

© ZOZO, Inc. https://zozo.jp/  38 • ファッション通販サイト  • 1,500以上のショップ、8,400以上のブランドの取り扱い  •
常時83万点以上の商品アイテム数と毎日平均2,900点以上の新着商品を掲載（2021年9月末時点）  • ブランド古着のファッションゾーン「ZOZOUSED」や  コスメ専門モール「ZOZOCOSME」、靴の専門モール  「ZOZOSHOES」、ラグジュアリー＆デザイナーズゾーン  「ZOZOVILLA」を展開  • 即日配送サービス  • ギフトラッピングサービス  • ツケ払いなど 

© ZOZO, Inc. https://wear.jp/  39 • ファッションコーディネートアプリ  • 1,500万ダウンロード突破、コーディネート投稿総数は1,100万件以上（2021年9月末時点） 
• ピックアップタグから最新のトレンドをチェック  • コーディネート着用アイテムをブランド公式サイトで購入可能  • WEAR公認の人気ユーザーをWEARISTAと認定。モデル・タレント・デザイナー・インフルエンサーといった各界著名人も参加 

© ZOZO, Inc. 40 https://zozo.jp/multisize/    • ZOZOSUITで得た100万件以上の体型データを活用することで実現した、身長と体重を選択するだけで理想のサイズが購入できる新しい洋服の買い方 
• 2019年秋冬アイテムから、ブランドと協働でマルチサイズ（MS）展開したアイテムをZOZOTOWN上で販売開始    【参加ブランド】  Audrey and John Wad、Casper John、CIAOPANIC、FREAK’S STORE、FREE’S MART、GALLARDAGALANTE、 KASHIYAMA、Levi’s、LOWRYS FARM、nano・universe、NATURAL BEAUTY BASIC、Ungrid、w closet　等 

© ZOZO, Inc. 41 https://zozo.jp/zozomat/    • 自宅にいながら簡単に高精度な足の3D計測ができる計測マット  • 計測したデータをもとに、自分の足型と靴の“相性度”
を表示  • NIKEやCONVERSEなど3,000型以上のアイテムに対応  （2021年6月末時点）   

© ZOZO, Inc. 42 https://fbz.zozo.com/    • ZOZOTOWN出店企業の自社ECのフルフィルメント支援サービス  • 自社EC運営のための撮影・採寸・梱包・配送などの各種フルフィルメント業務
を、ZOZOTOWNの物流センター「ZOZOBASE」が受託  • 設備投資・人件費・在庫保管料などの負担なしで、自社ECの運営が可能  • 各販売チャネル（自社EC・店舗・ZOZOTOWN）の在庫連携が可能。  これにより、商品欠品による販売機会の損失を最小化   

トラブルの原因特定率を劇的に向上させるSQL Serverロギングの仕組み作り / sql-server-logging-for-troubleshooting

トラブルの原因特定率を劇的に向上させるSQL Serverロギングの仕組み作り / sql-server-logging-for-troubleshooting

More Decks by p2sk

Other Decks in Technology

Featured

Transcript