[PHPカンファレンス2024]【ISUCONでも使える！？】お手軽にパフォーマンス改善入門 〜MySQL Performance Schema編〜

PHPカンファレンス2024で発表した「【ISUCONでも使える！？】お手軽にパフォーマンス改善入門 〜MySQL Performance Schema編〜」の資料です。
https://fortee.jp/phpcon-2024/proposal/c8b91758-f1c7-4772-865b-0cf017562342

Transcript

1. X: @for__3 mixi2: @zoe3 #phpcon #track1 お⼿軽に パフォーマンス改善⼊⾨ 〜MySQL Performance Schema編〜 PHPカンファレンス2024@zoe 1 ISUCONでも 使える！？
2. X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONやってますか！？ 2
3. X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONやってますか！？ 3
4. X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONやってますか！？ 4
5. X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンス改善 やってますか！？ 5
6. X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンスに課題 感じてますか？ 6
7. X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンス課題 7 • ネットワーク関連 • サーバーリソース関連 • データベース関連 • アプリケーションコード関連 • フロントエンド関連 • スケーラビリティ‧アーキテクチャ関連
8. X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンス課題 8 • ネットワーク関連 • サーバーリソース関連 • データベース関連 • アプリケーションコード関連 • フロントエンド関連 • スケーラビリティ‧アーキテクチャ関連
9. X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンス課題 9 • ネットワーク関連 • サーバーリソース関連 • データベース関連 • アプリケーションコード関連 • フロントエンド関連 • スケーラビリティ‧アーキテクチャ関連 アプリケーションエンジニアが まず⼿を出しやすいのは、 データベース関連‧アプリケーションコード関連 今回はデータベース関連に絞って話す
10. X: @for__3 mixi2: @zoe3 #phpcon #track1 データベース関連でのパフォーマンス改善 • 基本はスロークエリとN+1の改善 ◦ クエリ⾃体の改善 ◦ インデックスやテーブル構造などデータの持ち⽅の改善 ※これ以外にもMySQLサーバやエンジンのチューニングもある 10
11. X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONでよく使われてるスロークエリ分析ツール 11
12. X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONでよく使われてるスロークエリ分析ツール • mysqldumpslow ◦ MySQL公式が出しているスロークエリ集計ツール ◦ https://dev.mysql.com/doc/refman/8.0/ja/mysqldumpslow.html 12
13. X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONでよく使われてるスロークエリ分析ツール • mysqldumpslow ◦ MySQL公式が出しているスロークエリ集計ツール ◦ https://dev.mysql.com/doc/refman/8.0/ja/mysqldumpslow.html • pt-query-digest ◦ Perconaが出してるpercona-toolkitの⼀つ ◦ https://docs.percona.com/percona-toolkit/pt-query-digest.html 13
14. X: @for__3 mixi2: @zoe3 #phpcon #track1 スロークエリ分析の流れ 1. MySQLでスロークエリログを有効にする ◦ slow_query_log=1 ◦ （基本的には再起動が必要） 14
15. X: @for__3 mixi2: @zoe3 #phpcon #track1 スロークエリ分析の流れ 1. MySQLでスロークエリログを有効にする ◦ slow_query_log=1 2. スロークエリログファイルを分析する ◦ $ pt-query-digest slow-query.log 15
16. X: @for__3 mixi2: @zoe3 #phpcon #track1 # Overall: 628 total, 10 unique, 9.97 QPS, 3.90x concurrency _____________ # Time range: 2024-12-11T03:41:33 to 2024-12-11T03:42:36 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 246s 100ms 2s 391ms 1s 402ms 219ms # Lock time 916us 0 19us 1us 1us 1us 1us # Rows sent 1.16k 0 14 1.90 4.96 1.66 0.99 # Rows examine 9.84M 0 28.78k 16.04k 27.29k 12.01k 24.75k # Query size 2.81M 92 1011.37k 4.59k 964.41 61.78k 107.34 # Profile # Rank Query ID Response time Calls R/Call V/M I # ==== =============================== ============== ===== ====== ===== = # 1 0x6F24007454D54A130B0B44DF7F... 142.0217 57.8% 154 0.9222 0.30 SELECT chair_locations chairs chair_dist chairs distance_table # 2 0xD6AD8A431BE7CA447BE67E7F25... 90.7070 36.9% 387 0.2344 0.04 SELECT chair_locations # 3 0x1C5548DDCC027C3DFD55F75190... 10.8181 4.4% 68 0.1591 0.01 SELECT ride_statuses rides # MISC 0xMISC 2.2693 0.9% 19 0.1194 0.0 <7 ITEMS> $ pt-query-digest slow-query.log 16
17. X: @for__3 mixi2: @zoe3 #phpcon #track1 # Overall: 628 total, 10 unique, 9.97 QPS, 3.90x concurrency _____________ # Time range: 2024-12-11T03:41:33 to 2024-12-11T03:42:36 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 246s 100ms 2s 391ms 1s 402ms 219ms # Lock time 916us 0 19us 1us 1us 1us 1us # Rows sent 1.16k 0 14 1.90 4.96 1.66 0.99 # Rows examine 9.84M 0 28.78k 16.04k 27.29k 12.01k 24.75k # Query size 2.81M 92 1011.37k 4.59k 964.41 61.78k 107.34 # Profile # Rank Query ID Response time Calls R/Call V/M I # ==== =============================== ============== ===== ====== ===== = # 1 0x6F24007454D54A130B0B44DF7F... 142.0217 57.8% 154 0.9222 0.30 SELECT chair_locations chairs chair_dist chairs distance_table # 2 0xD6AD8A431BE7CA447BE67E7F25... 90.7070 36.9% 387 0.2344 0.04 SELECT chair_locations # 3 0x1C5548DDCC027C3DFD55F75190... 10.8181 4.4% 68 0.1591 0.01 SELECT ride_statuses rides # MISC 0xMISC 2.2693 0.9% 19 0.1194 0.0 <7 ITEMS> $ pt-query-digest slow-query.log 17 実行時間と全体に対するパーセント
18. X: @for__3 mixi2: @zoe3 #phpcon #track1 # Overall: 628 total, 10 unique, 9.97 QPS, 3.90x concurrency _____________ # Time range: 2024-12-11T03:41:33 to 2024-12-11T03:42:36 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 246s 100ms 2s 391ms 1s 402ms 219ms # Lock time 916us 0 19us 1us 1us 1us 1us # Rows sent 1.16k 0 14 1.90 4.96 1.66 0.99 # Rows examine 9.84M 0 28.78k 16.04k 27.29k 12.01k 24.75k # Query size 2.81M 92 1011.37k 4.59k 964.41 61.78k 107.34 # Profile # Rank Query ID Response time Calls R/Call V/M I # ==== =============================== ============== ===== ====== ===== = # 1 0x6F24007454D54A130B0B44DF7F... 142.0217 57.8% 154 0.9222 0.30 SELECT chair_locations chairs chair_dist chairs distance_table # 2 0xD6AD8A431BE7CA447BE67E7F25... 90.7070 36.9% 387 0.2344 0.04 SELECT chair_locations # 3 0x1C5548DDCC027C3DFD55F75190... 10.8181 4.4% 68 0.1591 0.01 SELECT ride_statuses rides # MISC 0xMISC 2.2693 0.9% 19 0.1194 0.0 <7 ITEMS> $ pt-query-digest slow-query.log 18 実行時間と全体に対するパーセント 呼び出し回数
19. X: @for__3 mixi2: @zoe3 #phpcon #track1 # Overall: 628 total, 10 unique, 9.97 QPS, 3.90x concurrency _____________ # Time range: 2024-12-11T03:41:33 to 2024-12-11T03:42:36 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 246s 100ms 2s 391ms 1s 402ms 219ms # Lock time 916us 0 19us 1us 1us 1us 1us # Rows sent 1.16k 0 14 1.90 4.96 1.66 0.99 # Rows examine 9.84M 0 28.78k 16.04k 27.29k 12.01k 24.75k # Query size 2.81M 92 1011.37k 4.59k 964.41 61.78k 107.34 # Profile # Rank Query ID Response time Calls R/Call V/M I # ==== =============================== ============== ===== ====== ===== = # 1 0x6F24007454D54A130B0B44DF7F... 142.0217 57.8% 154 0.9222 0.30 SELECT chair_locations chairs chair_dist chairs distance_table # 2 0xD6AD8A431BE7CA447BE67E7F25... 90.7070 36.9% 387 0.2344 0.04 SELECT chair_locations # 3 0x1C5548DDCC027C3DFD55F75190... 10.8181 4.4% 68 0.1591 0.01 SELECT ride_statuses rides # MISC 0xMISC 2.2693 0.9% 19 0.1194 0.0 <7 ITEMS> $ pt-query-digest slow-query.log 19 実行時間と全体に対するパーセント 1クエリあたりの実行時間 呼び出し回数
20. X: @for__3 mixi2: @zoe3 #phpcon #track1 # Overall: 628 total, 10 unique, 9.97 QPS, 3.90x concurrency _____________ # Time range: 2024-12-11T03:41:33 to 2024-12-11T03:42:36 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 246s 100ms 2s 391ms 1s 402ms 219ms # Lock time 916us 0 19us 1us 1us 1us 1us # Rows sent 1.16k 0 14 1.90 4.96 1.66 0.99 # Rows examine 9.84M 0 28.78k 16.04k 27.29k 12.01k 24.75k # Query size 2.81M 92 1011.37k 4.59k 964.41 61.78k 107.34 # Profile # Rank Query ID Response time Calls R/Call V/M I # ==== =============================== ============== ===== ====== ===== = # 1 0x6F24007454D54A130B0B44DF7F... 142.0217 57.8% 154 0.9222 0.30 SELECT chair_locations chairs chair_dist chairs distance_table # 2 0xD6AD8A431BE7CA447BE67E7F25... 90.7070 36.9% 387 0.2344 0.04 SELECT chair_locations # 3 0x1C5548DDCC027C3DFD55F75190... 10.8181 4.4% 68 0.1591 0.01 SELECT ride_statuses rides # MISC 0xMISC 2.2693 0.9% 19 0.1194 0.0 <7 ITEMS> $ pt-query-digest slow-query.log 20 実行時間と全体に対するパーセント 1クエリあたりの実行時間 呼び出し回数 ダイジェスト化されたクエリ
21. X: @for__3 mixi2: @zoe3 #phpcon #track1 # Overall: 628 total, 10 unique, 9.97 QPS, 3.90x concurrency _____________ # Time range: 2024-12-11T03:41:33 to 2024-12-11T03:42:36 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 246s 100ms 2s 391ms 1s 402ms 219ms # Lock time 916us 0 19us 1us 1us 1us 1us # Rows sent 1.16k 0 14 1.90 4.96 1.66 0.99 # Rows examine 9.84M 0 28.78k 16.04k 27.29k 12.01k 24.75k # Query size 2.81M 92 1011.37k 4.59k 964.41 61.78k 107.34 # Profile # Rank Query ID Response time Calls R/Call V/M I # ==== =============================== ============== ===== ====== ===== = # 1 0x6F24007454D54A130B0B44DF7F... 142.0217 57.8% 154 0.9222 0.30 SELECT chair_locations chairs chair_dist chairs distance_table # 2 0xD6AD8A431BE7CA447BE67E7F25... 90.7070 36.9% 387 0.2344 0.04 SELECT chair_locations # 3 0x1C5548DDCC027C3DFD55F75190... 10.8181 4.4% 68 0.1591 0.01 SELECT ride_statuses rides # MISC 0xMISC 2.2693 0.9% 19 0.1194 0.0 <7 ITEMS> $ pt-query-digest slow-query.log 21 実行時間と全体に対するパーセント 1クエリあたりの実行時間 呼び出し回数 ダイジェスト化されたクエリ この結果をもとに重いクエリを⾒つけ、 explainの実⾏計画などを⾒つつ改善していく
22. X: @for__3 mixi2: @zoe3 #phpcon #track1 スロークエリ分析の流れ 1. MySQLでスロークエリログを有効にする ◦ slow_query_log=1 2. スロークエリログファイルを分析する ◦ $ pt-query-digest slow-query.log 3. 重いクエリを⾒つけ、実⾏計画から改善案を考える ◦ → ここが１番重要 22
23. X: @for__3 mixi2: @zoe3 #phpcon #track1 お⼿軽に パフォーマンス改善⼊⾨ 〜MySQL Performance Schema編〜 23
24. X: @for__3 mixi2: @zoe3 #phpcon #track1 Perfomance Schemaなにができんの？ • MySQLでスロークエリログを有効にする ◦ → 有効にするステップをスキップできる ▪ デフォルトでPerfomance SchemaはONになってるのですぐ使え る！ ▪ ISUCONや業務でもサクッと試せる！ 24
25. X: @for__3 mixi2: @zoe3 #phpcon #track1 Perfomance Schemaなにができんの？ • MySQLでスロークエリログを有効にする ◦ → 有効にするステップをスキップできる • スロークエリログファイルを分析する ◦ → SQLで⾃由にスロークエリを分析できる 25
26. X: @for__3 mixi2: @zoe3 #phpcon #track1 SQLで⾃由にスロークエリを分析できる 26 SELECT RANK() OVER (ORDER BY SUM_TIMER_WAIT DESC) AS No, COUNT_STAR AS calls, ROUND(SUM_TIMER_WAIT/1000000000, 2) AS sum, ROUND((SUM_TIMER_WAIT / (SELECT SUM(SUM_TIMER_WAIT) FROM performance_schema.events_statements_summary_by_digest))*100, 2) AS "%", ROUND(SUM_TIMER_WAIT/1000000000 / COUNT_STAR, 2) AS "R/C", ROUND(MAX_TIMER_WAIT/1000000000, 2) AS max_t, ROUND(MIN_TIMER_WAIT/1000000000, 2) AS min_t, ROUND(MAX_TOTAL_MEMORY/1024, 0) AS memKb, SUM_NO_INDEX_USED noIndex, SUM_NO_GOOD_INDEX_USED badIndex,DIGEST_TEXT AS query_pattern FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME IS NOT NULL ORDER BY sum DESC;
27. X: @for__3 mixi2: @zoe3 #phpcon #track1 SQLで⾃由にスロークエリを分析できる 前準備なしでほぼpt-query-digestと同等の分析ができる！！ ※なんならより細かい値も確認できる 27 実行時間と全体に対するパーセント 1クエリあたりの実行時間 呼び出し回数 ダイジェスト化されたクエリ 最大実行時間 最小実行時間 最大使用メモリ インデックス未使用の回数
28. X: @for__3 mixi2: @zoe3 #phpcon #track1 スロークエリ分析の流れ 1. MySQLでスロークエリログを有効にする ◦ → 有効にするステップをスキップできる 2. スロークエリログファイルを分析する ◦ → SQLで⾃由にスロークエリを分析できる 3. 重いクエリを⾒つけ、実⾏計画から改善案を考える ◦ → ここが１番重要 28 すぐ分析‧改善 できる！！！
29. X: @for__3 mixi2: @zoe3 #phpcon #track1 MySQL Performance Schema 使ってお⼿軽パフォーマンス改善 していきましょう！！ 29 懇親会で話しましょう！
30. X: @for__3 mixi2: @zoe3 #phpcon #track1 30 株式会社ウィルゲート 10年⽬ シニアマネージャー∕VPoE やってること - 教育∕1on1∕採⽤ - PM∕SRE∕インフラ 興味あること - オブザーバビリティ∕⾃動化∕開発⽣産性∕PHP 池添 誠(いけぞえ まこと)