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