Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

© ZOZO, Inc. 本セッションへの質問や感想をお待ちしています
 #dbts2021
 3

Slide 4

Slide 4 text

© ZOZO, Inc. 背景
 4

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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


Slide 7

Slide 7 text

© ZOZO, Inc. 問題：どのクエリが原因でCPU高負荷になったのか
 ● 異常値を示すサーバーメトリクスを見つけることができても
 クエリレベルでの原因特定には至らない
 ○ したがって、他の情報も組み合わせて保存する必要性がある
 7

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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


Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

© ZOZO, Inc. ロギングの仕組みづくり
 15

Slide 16

Slide 16 text

© ZOZO, Inc. 16 SQL Server障害調査フロー


Slide 17

Slide 17 text

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


Slide 18

Slide 18 text

© 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

Slide 19

Slide 19 text

© ZOZO, Inc. DMVクエリデモ：1秒以上実行中のクエリリスト
 19

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

© ZOZO, Inc. 拡張イベントデモ
 22

Slide 23

Slide 23 text

© ZOZO, Inc. ロギング用クエリのご紹介
 23

Slide 24

Slide 24 text

© ZOZO, Inc. GitHubでMITライセンスでクエリを公開中
 ● https://github.com/masaki-hirose/SQLServer-Info/tree/master/sqlserver_logging
 24

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

© ZOZO, Inc. 工夫した点②：解析の精度向上
 ● BEFORE：2点間の差分で計算
 26

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

© ZOZO, Inc. 工夫した点②：解析の精度向上
 ● AFTER：抽出期間に存在する全てのSnapshotを利用
 28

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

© ZOZO, Inc. 今後の展望
 32

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

© ZOZO, Inc. より使いやすいログ解析の仕組みの提供
 ● DMVに馴染みのない開発者でも後追いできるように
 ○ より分かりやすいテーブル名に
 ○ 最小限のカラムに限定
 ○ ドキュメントの整理
 を進めていきたい
 34

Slide 35

Slide 35 text

© ZOZO, Inc. まとめ
 35

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

© ZOZO, Inc. サービス紹介
 37

Slide 38

Slide 38 text

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


Slide 39

Slide 39 text

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


Slide 40

Slide 40 text

© 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　等


Slide 41

Slide 41 text

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


Slide 42

Slide 42 text

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


Slide 43

Slide 43 text

No content