スロークエリ撲滅委員会

「ココナラ×レアジョブテクノロジーズ+AWS 少人数エンジニア組織でサービス成長させるには？座談会」のLT資料。
https://coconala.connpass.com/event/296649/

Transcript

1. Copyright coconala Inc. All Rights Reserved. スロークエリ 撲滅委員会
2. Copyright coconala Inc. All Rights Reserved. 自己紹介 橋本　穂高（はしもと　ほたか） スマホのネイティブアプリ開発、ゲームのバックエンド開発など を経て2021年5月にココナラへ入社。 現在はパフォーマンス改善やレガシー機能の移行に注力してい る。 2
3. Copyright coconala Inc. All Rights Reserved. 背景 3
4. Copyright coconala Inc. All Rights Reserved. 4 毎月1日18時 あるユーザーが 特定のページを開く と サービスが不安定になる
5. Copyright coconala Inc. All Rights Reserved. 5 管理画面で あるユーザーのページを開く と APサーバが落ちる
6. Copyright coconala Inc. All Rights Reserved. 6 さすがにやばい
7. Copyright coconala Inc. All Rights Reserved. 1月：55万件 7
8. Copyright coconala Inc. All Rights Reserved. 8 スロークエリ撲滅委員会 パフォーマンス委員会
9. Copyright coconala Inc. All Rights Reserved. 10月：9万件(見込み) 9 80% OFF!!
10. Copyright coconala Inc. All Rights Reserved. なにをした？ 10
11. Copyright coconala Inc. All Rights Reserved. 1. スロークエリを分析、サービスやユーザーへの 影響度が大きなものから優先度を設定 2. SQLをヒントに発生箇所を絞り込む 3. 実行計画とデータ量や偏り、コードの目的から 適切な改善方法を決定 4. 実際に改善を行い、リリース 5. 想定通り改善されているかモニタリング 11 改善フロー
12. Copyright coconala Inc. All Rights Reserved. ログ分析(CloudWatch) 12 parse @message /Query_time: (?<query_time>\d+\.\d+)[^\n]*\nSET timestamp=\d+;\n(?<sql_tmp>.*)$/ | parse @message /\]\s+@\s+\[(?<host>[\d\.]+)\]/ | filter host != 'バッチサーバの IP' and host != ‘管理画面サーバの IP' # 直接ユーザーの操作に影響を与える箇所を優先する | fields substr(sql_tmp, 0, 64) as sql # 集計のため、適当な長さに切る | filter strlen(sql) > 1 # ゴミデータの除去 | stats count(*) as cnt, pct(query_time, 70) as p70, pct(query_time, 95) as p95, max(query_time) as max_time, min(query_time) as min_time by sql | sort cnt desc
13. Copyright coconala Inc. All Rights Reserved. 発生箇所の特定 SELECT `hoges`.`id`, `hoges`.`name` FROM `hoges` WHERE (`hoges`.`type`) IN (1, 3) ORDER BY `hoges`.`id` ASC LIMIT 1000; Hoge.where(type: [:type_a, :type_b]) .select(:id, :name) .find_in_batches do 13 生SQL Rails/ActiveRecord キーワードでgrepしても簡単には特定できない……気合でなんとかする
14. Copyright coconala Inc. All Rights Reserved. 改善方法の検討 14
15. Copyright coconala Inc. All Rights Reserved. • SQL修正 ◦ インデックスが十分機能していない場合 • インデックス追加 ◦ SQL修正だけでは解決できない場合 • BigQueryで集計 ◦ バッチ実行、リアルタイム性が不要な場合 • レプリカの活用 ◦ 若干のレプリカ遅延が許容できる場合 • DynamoDBの活用 ◦ 集計不要、TTLなどが有効な場合 • 中間データの作成 ◦ リアルタイムな集計結果が必要な場合 15 改善方法
16. Copyright coconala Inc. All Rights Reserved. • ヘビーユーザほどメッセージ送信が遅い • ヘビーユーザほど画面表示が遅い 16 例）メッセージ未読件数_背景
17. Copyright coconala Inc. All Rights Reserved. 1. 未読件数管理テーブルAの作成 2. メッセージの書き込み時にAの未読件数を更新 3. 既存メッセージの未読件数データを一括生成 4. 未読件数はAから取得するように変更 1、2をリリース → 3を実行 → 4をリリースという流れ 17 例）メッセージ未読件数_対応
18. Copyright coconala Inc. All Rights Reserved. 例）メッセージ未読件数_結果 18 ※テーブル単位で集計した数字のため、他のスロークエリの分が残っています
19. Copyright coconala Inc. All Rights Reserved. 次の戦いへ…… 19
20. Copyright coconala Inc. All Rights Reserved. まとめ、感想 20
21. Copyright coconala Inc. All Rights Reserved. ココがよかった 21 • 改善結果が自然に数値として見えるため、実感が湧きやすかった • 調査過程で過去の遺産を整理することができた • スロークエリ以外の改善ポイントも見えてきた • CloudWatch Logs Insightsにちょっとだけ詳しくなった
22. Copyright coconala Inc. All Rights Reserved. ココがつらかった 22 • 生SQLからコードを特定するには気合が必要 • ヘビーユーザーを救うとそれ以外のユーザーが悪化する • テストデータの不完全さによってテストが通らない
23. Copyright coconala Inc. All Rights Reserved. 改善の秋、撲滅の秋💪 みんなもやろう、たのしい撲 滅。 23
24. Copyright coconala Inc. All Rights Reserved. 例）古いデータの削除バッチ_背景 24 • 特定のページで表示する内容をバッチで作成していた • 作成した内容は、古くなったらバッチで削除していた • 削除バッチでスロークエリ、レプリカ遅延が発生していた • 対象のテーブルは集計やページングが不要だった
25. Copyright coconala Inc. All Rights Reserved. 例）古いデータの削除バッチ_対応 25 1. DynamoDBにテーブルAを作成 2. バッチの書き込み先をAに変更し、TTLを設定 3. Aにデータがあれば返し、無ければ旧テーブルを見るように変更 4. バッチを停止
26. Copyright coconala Inc. All Rights Reserved. 例）古いデータの削除バッチ_結果 26 • レコード削除起因のスロークエリがなくなった • レコード削除起因のレプリカ遅延がなくなった • バッチが不要になった