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

by Z.O.E.

Slide 1

Slide 1 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 お⼿軽にパフォーマンス改善⼊⾨〜MySQL Performance Schema編〜 PHPカンファレンス2024@zoe 1 ISUCONでも使える！？

Slide 2

Slide 2 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONやってますか！？ 2

Slide 3

Slide 3 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONやってますか！？ 3

Slide 4

Slide 4 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONやってますか！？ 4

Slide 5

Slide 5 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンス改善やってますか！？ 5

Slide 6

Slide 6 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンスに課題感じてますか？ 6

Slide 7

Slide 7 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンス課題 7 ● ネットワーク関連 ● サーバーリソース関連 ● データベース関連 ● アプリケーションコード関連 ● フロントエンド関連 ● スケーラビリティ‧アーキテクチャ関連

Slide 8

Slide 8 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンス課題 8 ● ネットワーク関連 ● サーバーリソース関連 ● データベース関連 ● アプリケーションコード関連 ● フロントエンド関連 ● スケーラビリティ‧アーキテクチャ関連

Slide 9

Slide 9 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 パフォーマンス課題 9 ● ネットワーク関連 ● サーバーリソース関連 ● データベース関連 ● アプリケーションコード関連 ● フロントエンド関連 ● スケーラビリティ‧アーキテクチャ関連アプリケーションエンジニアがまず⼿を出しやすいのは、データベース関連‧アプリケーションコード関連今回はデータベース関連に絞って話す

Slide 10

Slide 10 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 データベース関連でのパフォーマンス改善 ● 基本はスロークエリとN+1の改善 ○ クエリ⾃体の改善 ○ インデックスやテーブル構造などデータの持ち⽅の改善 ※これ以外にもMySQLサーバやエンジンのチューニングもある 10

Slide 11

Slide 11 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 ISUCONでよく使われてるスロークエリ分析ツール 11

Slide 12

Slide 12 text

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 スロークエリ分析の流れ 1. MySQLでスロークエリログを有効にする ○ slow_query_log=1 ○ （基本的には再起動が必要） 14

Slide 15

Slide 15 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 スロークエリ分析の流れ 1. MySQLでスロークエリログを有効にする ○ slow_query_log=1 2. スロークエリログファイルを分析する ○ $ pt-query-digest slow-query.log 15

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

Slide 22

Slide 22 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 スロークエリ分析の流れ 1. MySQLでスロークエリログを有効にする ○ slow_query_log=1 2. スロークエリログファイルを分析する ○ $ pt-query-digest slow-query.log 3. 重いクエリを⾒つけ、実⾏計画から改善案を考える ○ → ここが１番重要 22

Slide 23

Slide 23 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 お⼿軽にパフォーマンス改善⼊⾨〜MySQL Performance Schema編〜 23

Slide 24

Slide 24 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 Perfomance Schemaなにができんの？ ● MySQLでスロークエリログを有効にする ○ → 有効にするステップをスキップできる ■ デフォルトでPerfomance SchemaはONになってるのですぐ使える！ ■ ISUCONや業務でもサクッと試せる！ 24

Slide 25

Slide 25 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 Perfomance Schemaなにができんの？ ● MySQLでスロークエリログを有効にする ○ → 有効にするステップをスキップできる ● スロークエリログファイルを分析する ○ → SQLで⾃由にスロークエリを分析できる 25

Slide 26

Slide 26 text

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;

Slide 27

Slide 27 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 SQLで⾃由にスロークエリを分析できる前準備なしでほぼpt-query-digestと同等の分析ができる！！ ※なんならより細かい値も確認できる 27 実行時間と全体に対するパーセント 1クエリあたりの実行時間呼び出し回数ダイジェスト化されたクエリ最大実行時間最小実行時間最大使用メモリインデックス未使用の回数

Slide 28

Slide 28 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 スロークエリ分析の流れ 1. MySQLでスロークエリログを有効にする ○ → 有効にするステップをスキップできる 2. スロークエリログファイルを分析する ○ → SQLで⾃由にスロークエリを分析できる 3. 重いクエリを⾒つけ、実⾏計画から改善案を考える ○ → ここが１番重要 28 すぐ分析‧改善できる！！！

Slide 29

Slide 29 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 MySQL Performance Schema 使ってお⼿軽パフォーマンス改善していきましょう！！ 29 懇親会で話しましょう！

Slide 30

Slide 30 text

X: @for__3 mixi2: @zoe3 #phpcon #track1 30 株式会社ウィルゲート 10年⽬シニアマネージャー∕VPoE やってること - 教育∕1on1∕採⽤ - PM∕SRE∕インフラ興味あること - オブザーバビリティ∕⾃動化∕開発⽣産性∕PHP 池添誠(いけぞえまこと)