開発者向け MySQL 入門 / MySQL 101 for Developers

背景などは https://wrsn0.hatenablog.com/entry/2024/02/22/092703 へ

Transcript

1. 開発者向け MySQL 入門 2024/02/21 1
2. $ whoami @wa6sn SQL は書けません 3 年ぐらい DBA をやっていました 最近はセキュリティが気になっています 2
3. おことわり 社内の勉強会用に作成した資料であり、内容を保証するものではありません 「 MySQL を触っているが運用に自信がない」くらいの、 アプリケーションエンジニアの方に向けて書いています 「 SQL の書き方」は最後まで出てきません 出来るだけ「通常の MySQL」と「 Aurora MySQL」の事情は、 意識して書いていますが、完全ではないです 3
4. 目次 1. はじめに 2. アーキテクチャの概観 3. インデックス 4. オプティマイザと実行計画 5. チューニング 6. ケーススタディ 7. Q&A 4
5. 1. はじめに 5
6. 実施の意図 ほとんどのシステムにおいて、 DB のダウン ≒ サービスダウン となる RDS のようなマネージドサービスの台頭により、 DB を運用する負荷は下がって きているが、その内部の知識が助けになることは未だにある そこで、トラブル時の調査の勘所や、パフォーマンスの出し方について、 アーキテクチャから演繹的に理解する ことを目指す 「こういう仕組みだった気がするから、この事象の対応方針はこれでいいかな？」と 考えることができたらいいな 6
7. 運用の難易度は、概ね規模に比例する （当たり前かもしれないが）データサイズ・ QPS が小さいうちは、 運用で困ることはほとんどない サービスが成長することで問題は顕在化してくる一方、 その頃には身動きが取りづらくなっているというのが DB の悩ましいところ 7
8. MySQL とは 世界で最も利用されている OSS RDBMS の一つ Facebook, Google, Youtube など多くの利用実績 https://db-engines.com/en/ranking GPL・コマーシャルライセンスのデュアルライセンス （他 RDBMS に比べ）シンプル・高速 OL T P（ Online Transaction Processing） 向け vs OL A P (Online Analytical Processing) Redshift とか BigQuery のような DWH 近年は HTAP（ Hybrid Transaction Analytical Processing）という、 OLTP/OLAP を両立したものも出てきた 8
9. なぜ流行ったか ? https://www.slideshare.net/matsunobu/ss-28303485/8 Facebook で DB Engineer をされている松信さんという方の言葉 抜粋すると（当時は） 無償だったから 導入の障壁が低かったから 似た立場の PostgreSQL より高速だと言われていたから レプリケーションで簡単にスケールアウト出来たから 9
10. 現時点での主流なバージョン MySQL（ Community 版） https://blogs.oracle.com/mysql-jp/post/introducing-mysql-innovation-and-longterm- support-lts-versions-jp LTS（ Long Term Support） の 8.0.x シリーズ Bug fix, Security fix が主 Innovation Release の 8.1 ~ 8.3 シリーズ 機能追加をやっていくほう Aurora MySQL https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraMySQLReleaseNotes/Auror aMySQL.Updates.30Updates.html 3 系（ 8.0.x 系互換） 10
11. 2. アーキテクチャの概観 11
12. Client Connector Parser Optimizer Executor Storage Engine （InnoDB, etc） MySQL Server AST SQL Query Execution Plan Send Result Record Access クエリが実行されるまで Connector クライアントからの接続管理と認証 Parser 文法チェック・ SQL のパース Optimizer どうデータにアクセスすると効率が良いか、 主に統計情報から実行計画を立てる （インデックスの選択 , JOIN 順序等） Executor / Handler ストレージエンジンへのアクセスと、ソート等 「データを引っ張ってきてから」の色々 Storage Engine テーブルへのデータの読み書き 12
13. ストレージエンジン https://dev.mysql.com/doc/refman/8.0/ja/storage-engines.html MySQL には複数のストレージエンジンが存在する “Pluggable storage engine architecture” テーブル単位でどのストレージエンジンを使うか選択できる xxx_innodb_yyy といった各種変数名はこの名残 が、 MySQL ≒ InnoDB として、もはや InnoDB 以外は忘れていい Aurora MySQL は InnoDB のみ対応 13
14. "素朴なデータベースの実装 " を考えてみる Client Disk Memory 参照 更新 （ディスクにデータを永続化したい・ディスクよりメモリのほうが高速という前提で） 参照クエリ メモリ上にレコードがあれば、そのレコードをクライアントに返す。 メモリ上にレコードがなければ、ディスクから読み、メモリに追加しつつ返す。 更新クエリ コミット時、ディスク上のテーブルに直接書き込む。 メモリとの整合性のため、対応するメモリ上のレコードは破棄する。 14
15. "素朴なデータベースの実装 " の課題を想像してみる Client Disk Memory 参照 更新 更新のたびにテーブルへの書き込みが発生してしまい、レイテンシが大きそう メモリが十分に大きいか、賢く管理しないと、 むしろ参照系でメモリを経由するオーバーヘッドが大きくなりそう レコードという単位はそのまま扱うには細かく、スループットが出なさそう etc, といったことが考えられる 15
16. 改めて InnoDB のアーキテクチャを見る 我々の想像している「レコード」というものは Tablespaces（ *.ibd）にある In-Memory に "Buffer Pool" なる重要なコンポーネントが居る （ https://dev.mysql.com/doc/refman/8.0/ja/innodb-architecture.html より） 16
17. Tablespaces？ 複数のレコードの塊が "ページ " という単位で管理される、ということだけ知ろう "ページ " が MySQL が裏側でよしなに操作する際の主なデータ単位だから （ https://jackyfkc.github.io/cs/data_store/mysql/innodb.html より） 17
18. SELECT クエリで何が起こるか 欲しいデータ（ページ）がバッファプールにあれば、バッファプールから返す。 なければ、テーブルスペースにアクセスして バッファプールに載せてから 返す バッファプールは LRU（ Least Recently Used）を改良したアルゴリズムで管理 雑な SELECT でうっかり全てのデータが入れ替わってしまわないための工夫 https://dev.mysql.com/doc/refman/8.0/ja/innodb-buffer-pool.html Client Buffer Pool Log Buffer Tablespace Redo Log Disk Memory Server バッファプールにデータが無ければ ディスクにアクセス SELECT バッファプールにデータがあれば バッファプールからデータを返す 18
19. INSERT, UPDATE, DELETE クエリで何が起こるか (1) バッファプール上のデータを更新しつつ、更新内容をログバッファに書き込み Client Buffer Pool Log Buffer Tablespace Redo Log Disk Memory Server 3. ログバッファに書き込み 1. 更新クエリ 2. バッファプール上のデータを更新 19
20. INSERT, UPDATE, DELETE クエリで何が起こるか (2) コミット時はテーブルよりも先にログファイルが永続化される WAL: Write-Ahead-Log という手法 Redo ログが永続化されたら書き込み完了とみなす Client Buffer Pool Log Buffer Tablespace Redo Log Disk Memory Server 2. Redo ログに flush 1. コミット 3. flush 出来たら Ack コミット != テーブルへの永続化 20
21. INSERT, UPDATE, DELETE クエリで何が起こるか (3) テーブルへの永続化（チェックポイント処理）は非同期に行う これにより「 Redo ログとバッファプールには反映されているけど、 テーブルスペースには反映されていない」なる状態が存在する ある意味、バッファプールこそが最新のデータを持つとも言える Client Buffer Pool Log Buffer Tablespace Redo Log Disk Memory Server ダーティページ : テーブルに反映されていないデータ バックグラウンドでテーブルに更新される 21
22. なぜ非同期に永続化するのか？ ディスクへの I/O 量と回数を削減したいから 回転メディアを前提とした当時の設計においては、特に重要だった 都度テーブルへ同期的に更新してしまうと、毎回ランダム書き込みになり高コスト 代わりに Redo ログへ、ページの変更操作を 追記 低コストなシーケンシャル書き込みに変換している、とも 「同じレコードへの操作なら最終状態だけ反映したろ！」といった細かいテクも 22
23. クラッシュリカバリ サーバプロセスがクラッシュすると、メモリ上のページは失われる Redo ログに書かれた、ページへの変更操作を再実行し、リカバリする Client Buffer Pool Log Buffer Tablespace Redo Log Disk Memory Server Redo ログをテーブルに適⽤ ロールフォワードリカバリ 23
24. Aurora での大きな改良点 ディスク I/O の責務の多くを専用の分散ストレージシステムに移譲 チェックポイントを気にする必要がなくなり、安定したレイテンシを出せるように クラッシュリカバリも早くなった （図は https://bit.ly/31ZMsMm より抜粋） 24
25. 運用を考えたときに データ構造やクエリに手を加えていなくても、将来的に「データ量が増える → バッファ プールからあふれ、ストレージへのアクセスが増える → パフォーマンスが落ちる」とい ったことがありえる 一般的にデータ量・レコード量が多いほど、あらゆる操作のコストが高くなり、 運用の難易度は上がる（それはそう） より正確には、バッファプールは LRU ベースのため「生きたデータの量が」ではある 25
26. この節のまとめ ディスク I/O は常に高コスト という前提がある DB に限らず、あらゆるシステムで I/O 削減の工夫がされている InnoDB アーキテクチャを見ても、様々な箇所に「バッファ」が登場する 参照・更新いずれの場合も データはメモリ（バッファプール）を通る innodb_buffer_pool_size は非常に重要なパラメータである デフォルトは {DBInstanceClassMemory*3/4} バッファプールは共有リソース 非効率なクエリは全体のパフォーマンスに影響しうる 他で使いたいキャッシュを追い出しちゃう可能性があるから 26
27. 3. インデックス 27
28. MySQL のインデックス イメージ図 実際には B+Tree というデータ構造ですが、今回はこのイメージで説明します # 紙面の都合で擬似コードです CREATE TABLE `member` ( `id`, `name`, `age`, `address`, # カラム PRIMARY KEY (`id`), # プライマリキー KEY `idx_age` (`age`) # age のみのインデックス KEY `idx_name_age` (`name`, `age`), # name と age の複合インデックス ) id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga age (id) 22 5 26 2 33 1 41 3 50 4 クラスタインデックス （Primary key : id） セカンダリインデックス （ idx_name_age ） セカンダリインデックス （ idx_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 28
29. 全てはインデックス https://dev.mysql.com/doc/refman/8.0/ja/innodb-index-types.html クラスタインデックス "テーブルそのもの " と言ってしまっていい プライマリキーの順番に並んでいる 一部の例外を除き、すべてのカラムの値を持つ セカンダリインデックス "データを並べ替えた、テーブルの部分的なコピー " と言ってしまっていい クラスタインデックス以外を指す 指定したカラムの値の順番に並んでいる 指定したカラム以外のカラムを参照するときのために、プライマリキーの値を持つ 29
30. 例 1 SELECT * FROM member WHERE id < 4 クラスタインデックスは PK である id の順に並んでいるので、 その順にスキャンし全てのカラムを返す セカンダリインデックスを使う理由がないので、使わない id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga クラスタインデックス （Primary key : id） age (id) 22 5 26 2 33 1 41 3 50 4 セカンダリインデックス （ idx_name_age ） セカンダリインデックス （ idx_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 30
31. 例 2 SELECT * FROM member WHERE age < 23 クラスタインデックスは age の順には並んでいないので、 セカンダリインデックス idx_age を使って age < 23 を探す age = 22 のレコードは見つかったが、セカンダリインデックスだけでは全ての カラムの値は分からないので、 id = 5 を用いてクラスタインデックスから値を取得する id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga age (id) 22 5 26 2 33 1 41 3 50 4 クラスタインデックス （Primary key : id） セカンダリインデックス （ idx_age ） セカンダリインデックス （ idx_name_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 31
32. 例 3 SELECT age FROM member WHERE age < 23 クラスタインデックスは age の順には並んでいないので、 セカンダリインデックス idx_age を使って age < 23 を探す age = 22 のレコードが見つかり、 age カラムだけを返せばよいので、この時点でクライアン トに値を返してしまうことができる クラスタインデックスは利用されない。 covering index とも言う id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga age (id) 22 5 26 2 33 1 41 3 50 4 クラスタインデックス （Primary key : id） セカンダリインデックス （ idx_age ） セカンダリインデックス （ idx_name_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 32
33. 例 3' SELECT id, age FROM member WHERE age < 23 クラスタインデックスは age の順に並んでいないので、 セカンダリインデックス idx_age を使って age < 23 を探す age = 22 のレコードが見つかる。 id カラムも 暗黙的に セカンダリインデックスに含まれてい るので、 age カラムと共にクライアントに値を返してしまうことができる これも covering index id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga age (id) 22 5 26 2 33 1 41 3 50 4 クラスタインデックス （Primary key : id） セカンダリインデックス （ idx_age ） セカンダリインデックス （ idx_name_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 33
34. 例 4 SELECT * FROM member WHERE address = 'Mie' どのインデックスも address の順に並んでいないので、 クラスタインデックスからすべての行を探す id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga クラスタインデックス （Primary key : id） age (id) 22 5 26 2 33 1 41 3 50 4 セカンダリインデックス （ idx_name_age ） セカンダリインデックス （ idx_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 34
35. 例 5 SELECT id, name, age FROM member WHERE name LIKE 'A%' idx_name_age の name 部を使う。前方一致はインデックスが使える id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 クラスタインデックス （Primary key : id） セカンダリインデックス （ idx_name_age ） age (id) 22 5 26 2 33 1 41 3 50 4 セカンダリインデックス （ idx_age ） 35
36. 例 6 SELECT * FROM member WHERE name LIKE '%eguchi%' idx_name_age の name 部は値の順に並んでいるだけなので、 「 eguchi の中間一致・後方一致」はすべての行を見ないと分からない クラスタインデックスからすべての行を探す id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga クラスタインデックス （Primary key : id） age (id) 22 5 26 2 33 1 41 3 50 4 セカンダリインデックス （ idx_name_age ） セカンダリインデックス （ idx_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 36
37. 例 7 SELECT id FROM member WHERE age < 30 LIMIT 1 セカンダリインデックス idx_age を使って age < 30 を探す 条件を満たすレコードが見つかった時点で、 LIMIT 1 によって探索が打ち切られる id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga age (id) 22 5 26 2 33 1 41 3 50 4 クラスタインデックス （Primary key : id） セカンダリインデックス （ idx_age ） セカンダリインデックス （ idx_name_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 37
38. 例 8 SELECT id FROM member WHERE address = 'Berlin' LIMIT 1 どのインデックスも address の順に並んでいないので、 クラスタインデックスからすべての行を探す レコードが何億件あろうが address = 'Berlin' にマッチする行が見つかるまで探す id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga クラスタインデックス （Primary key : id） age (id) 22 5 26 2 33 1 41 3 50 4 セカンダリインデックス （ idx_name_age ） セカンダリインデックス （ idx_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 38
39. 例 9 DELETE FROM member WHERE address = 'Berlin' LIMIT 1 更新系クエリも「どこにレコードがあるか」を調べるためにインデックスを利用する さっきの例 8 同様、レコードが何億件あろうが address = 'Berlin' にマッチする行が見つかる まで探す id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga クラスタインデックス （Primary key : id） age (id) 22 5 26 2 33 1 41 3 50 4 セカンダリインデックス （ idx_name_age ） セカンダリインデックス （ idx_age ） name age (id) Abe 41 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 39
40. インデックスがロックの範囲も決める 例 9 のようなケースではテーブルの全ての行にロックを取ってしまう 「条件に合った行をロック」ではなく、 「インデックスに対してロック」 もっと言うとネクストキーロックだが今回のスコープ外とする そのため「 LIMIT 1 しておけばよくね？」は、よくない （イメージで説明すると） age < 30 を条件に更新したいトランザクション A と、 age > 40 を条件に更新したいトランザクション B があったとしよう。 age のインデックスが ある : age でソートされているから A, B の操作は干渉しないことが分かる。 　 A → B の順に実行しても、 B → A の順に実行しても問題ないので、並列に処理できる ない : お互いの条件に合うレコードがどこにあるか分からないので、 　全部のレコードを探す必要がある。 A, B は並列に実行できない 40
41. インデックスは更新コストとのトレードオフ セカンダリインデックスは "データを並べ替えた、テーブルの部分的なコピー " なので、レコードの更新時にはセカンダリインデックスにも更新がされる データの書き換え + データの順序を維持するためのコストがある インデックスを作成しすぎると更新性能に影響が出る、という現象は index shotgun という有名なアンチパターンで知られている 参照コストと更新コストのトレードオフだが、現実は参照重視なことが多い id name age address 1 Deguchi 33 Tokyo 2 Bessho 26 Tokyo 3 Abe 41 → 51 Kyoto 4 Deguchi 50 Mie 5 Fukuda 22 Shiga age (id) 22 5 26 2 33 1 50 4 41 → 51 3 クラスタインデックス （Primary key : id） セカンダリインデックス （ idx_age ） name age (id) Abe 41 → 51 3 Bessho 26 2 Deguchi 33 1 Deguchi 50 4 Fukuda 22 3 セカンダリインデックス （ idx_name_age ） 41
42. 原則 1 テーブルに 1 インデックスを使って探索する member テーブルには idx_age と idx_name のみが存在する。 WHERE age < ? AND name = ? のような複合条件はどう処理されるか？ MySQL は 1 テーブルに 1 インデックスを使って探索する ので、 idx_name か idx_age のどちらか一方のインデックスが利用されるのが原則 idx_age と idx_name を候補とし、 「絞り込み やすそう な方」を MySQL が判断（後述）してどちらかを利用する "インデックスマージ " という、複数のインデックスを利用して探索したあと、 それらの集合演算を行うしくみが存在ものの、初手では考慮に入れなくていい 適用できるパターンの把握も難しいので、素直に複合インデックスを作るべき 「全カラムに単一カラムのインデックスを貼って、インデックスマージを狙えばよく ね？」とはならない 42
43. 複合インデックスのカラム順序 (name, age) と (age, name) は別物ですか はい 前者ならまず name の順に並び替えて、 name が同値のものを age で並び替えています WHERE age < ? だけで絞り込みたいときに (name, age) は利用できません (name, age) と (age, name) のどちらを作るべきですか その DB に流れるクエリによりけりです 両方とも欲しい場合もありえます 雑に両方作って使ってない方を消す、という手もなくはないです どちらも使える場合、最初のカラムでより絞り込める方を使うはずです 例えば (name, age) があるなら (name) は要らないことがほとんどです 43
44. 十分なインデックスが存在するか？ 「作るべきか」を最終的に判断するのは人間なので、都度やっていく スロークエリログや APM から時間のかかるクエリを特定する スロークエリログ には log_queries_not_using_indexes のような便利そうな 変数もあるけど、フォーカスしたいのは 「クエリが遅いか」 なので、好みで long_query_time は 0.5 など 1 以下も設定できる 都度 EXPLAIN で実行計画を確認する 44
45. 不要なインデックスがないか？ performance_schema という サーバが起動してから の稼働統計の入った便利な テーブル群があり、さらにそれらを人間が読みやすくした sys スキーマがある https://dev.mysql.com/doc/refman/8.0/ja/sys-schema-views.html schema_unused_indexes は使っていないインデックスを教えてくれる schema_redundant_indexes は冗長なインデックスを教えてくれる idx_name_age が既にあるときの idx_name みたいなヤツ schema_index_statistics はインデックスに対する統計情報を教えてくれる 読み取り行数、更新行数、それらのレイテンシ etc MySQL 8.0 からは invisible index を使って、いきなり削除ではなく 「一旦使わないようにして様子を見る」ことが出来るようになった 45
46. この節のまとめ InnoDB は、クラスタインデックスというデータの持ち方をしている セカンダリインデックスは "データを並べ替えた、テーブルの部分的なコピー " インデックスは参照処理と更新のトレードオフ とはいえ、現実は参照重視なことが多く、 「足りなくて困る」ことが多い インデックスがロックの範囲も決める 適切なインデックスを作ると、バッファプールが効率よく使える バッファプールに載るのは「クライアントに返したレコード」ではなく 「スキャンしたレコード」 。 「実際に必要なデータ」を不足なく載せたい インデックスもバッファプールに載る 46
47. 4. オプティマイザと実行計画 47
48. 前節で出るであろう疑問 そもそも「どのインデックスを使うべきか」はどうやって判断しているのか？ 使えそうなインデックスが複数あるとき、どのように優先されるのか もっと広く言うと、 JOIN の順序だったり、アクセスパス全般 何らかのロジックに基づいてデータへのアクセス方法が決まるのであれば、 それは制御できるのか？ 48
49. Client Connector Parser Optimizer Executor Storage Engine （InnoDB, etc） MySQL Server AST SQL Query Execution Plan Send Result Record Access クエリが実行されるまで Connector クライアントからの接続管理と認証 Parser 文法チェック・ SQL のパース Optimizer どうデータにアクセスすると効率が良いか、主 に統計情報から実行計画を立てる （インデックスの選択 , JOIN 順序等） Executor / Handler ストレージエンジンへのアクセスと、ソート等 「データを引っ張ってきてから」の色々 Storage Engine テーブルへのデータの読み書き 49
50. オプティマイザと実行計画 https://dev.mysql.com/doc/refman/8.0/ja/explain.html SQL は「どのようにデータを取得するか」を記述しない オプティマイザがクエリ・テーブル構造・統計情報から、 どのようにデータを取得するのが最も効率がよいかを判断している 確実に 人間が正しい自信があるとき、ヒント句などで調整できる インデックスヒント https://dev.mysql.com/doc/refman/8.0/ja/index-hints.html オプティマイザヒント https://dev.mysql.com/doc/refman/8.0/ja/optimizer-hints.html JOIN 順の指定 https://dev.mysql.com/doc/refman/8.0/ja/join.html 50
51. 統計情報？ https://dev.mysql.com/doc/refman/8.0/ja/innodb-statistics-estimation.html MySQL は各テーブルのページをサンプリングして、統計情報として把握している mysql.innodb_table_stats で見えたりする データ移行等で「概算値」を見てしまい、行数が違って焦るのはあるある話 余談として、 innodb_stats_persistent_sample_pages という「何ページをサンプリング 対象にするか」というパラメータがある。 MySQL デフォルトは 20 Aurora MySQL 2 系はデフォルトで 128 だが、 3 系でまた 20 へ …… なんで？ 51
52. 実行計画 https://dev.mysql.com/doc/refman/8.0/ja/explain.html おなじみ EXPLAIN で確認できる 詳しい読み方は、都度調べましょう mysql> explain select * from employees where emp_no = 10001\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) 52
53. クエリが同じなら実行計画も同じ、とは限らない たとえば インデックスの有無 バージョンアップによるオプティマイザの変化 8.0 から入った Hash join や、新しい最適化アルゴリズム データ量、カーディナリティの変化 そもそもデータは偏るもの（大口の顧客的な） 統計情報の再取得 一定量のデータが更新されるなどを契機に、統計情報も更新される 一応、 ANALYZE TABLE という DDL で ガチャ 能動的な更新が出来る 53
54. カーディナリティ 「インデックス」の節で挙げた例は、いずれも「インデックスを使うことで十分にしぼ り込めることが期待される」という前提がある 実際には、 「セカンダリインデックスを使っても大して絞り込めないので、最初からクラ スタインデックスにアクセスしたほうがよさそう」とオプティマイザが判断することも よくある カラム内に異なる値がどれほどあるかを カーディナリティ と呼ぶ。インデックスを貼る ときは、カーディナリティの高い（値の種類の多い ≒ 絞り込みやすい）カラムを選ぶと 効果が高い。 54
55. 補足 : 「 MySQL は JOIN が遅い」という言説 おそらく、そう言われるもとになったのは （その頃の）オプティマイザが賢くなかった 最適化のアレコレが（他の DB に比べ）不足していた JOIN アルゴリズムが NLJ しかなかった いい感じのクエリの書き換えを MySQL 側でやってくれなかった 統計情報が不正確だった MySQL 5.5 で innodb_stats_persistent_sample_pages に相当するパラメータは、 デフォルト 8 ページしかなかった など、様々な由来がありそうです。 が、通説を信じるのではなく試したほうがいいです 55
56. 最大ケースを想像できるとよい 同じ構造のクエリに見えて、実行計画が変わってしまう例 LIMIT x の絞り込み件数 WHERE .. IN .. の対象の要素の数（参考 : freee さんの記事） 特定の閾値を超えるとパフォーマンスが劣化する例 GROUP BY や ORDER BY のような一時テーブルを利用するクエリで件数が多い https://dev.mysql.com/doc/refman/8.0/ja/internal-temporary-tables.html 集計操作を伴うクエリに対し、一定サイズまではメモリ上に一時テーブルを用いるが、 それより大きいとメモリからディスクに書き出す挙動をする ディスクに書き出す処理は高コストなので「特定の ID を指定したクエリでやたら遅いんだけ ど」という事象が発生する 56
57. この節のまとめ どのようにデータにアクセスするかはオプティマイザが決めている インデックスを使うべきか、どれを使うべきか、 etc 基本的にはオプティマイザ任せでいい EXPLAIN は読めるようになろう 各種ヒントや JOIN 順の指定で、オプティマイザの動作をある程度制御できる あくまで「その時点での（データ量・分布での）判断では」になるので、 ヒントによる制御は、運用（見直し）が発生するとも考えられる クリティカルなシステムなどで、実行計画を安定させたいモチベーションはあって、 Oracle や PostgreSQL はこの辺の運用がもっと細かくやれる 57
58. 5. チューニング 58
59. 性能の悩みにどう向き合うか パフォーマンス問題はワークロードによりけり システムの数だけ課題があるので、テストとモニタリングが重要 机上ですべての挙動を把握しようとするより、手軽に本番同様の環境で テスト出来るようにしたほうがコスパがいいことがよくある データ量、カーディナリティ、リクエスト量 は近くあるべき トラブルが発生した際に再現性も確保しやすくなる Aurora であれば、クローン機能などは積極的に使いたい パフォーマンステストも、なるべく本番同様のリクエストを模する キャッシュの影響を丸めるためにある程度の時間、負荷をかけ続ける 単発のクエリだけでは心もとないので、シナリオを用意するとよい 59
60. 前提知識｜ステータス変数 https://dev.mysql.com/doc/refman/8.0/ja/server-status-variables.html Connections や Uptime のように、サーバの稼働状況を表すもの show [global] status like '%{variable_name}%'; で確認できる ただ、主要なものは Performance Insights（後述）や CloudWatch Metrics でも確認出来る ので、これを直接実行する機会は（難しめの問題に当たらない限り）そうないかもしれない スコープの概念が存在する グローバル : サーバ全体で持つ値 セッション : セッション（接続）ごとに持つ値 60
61. 前提知識｜システム変数 https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html innodb_buffer_pool_size のように、そのサーバの設定を表すもの RDS のパラメータグループで変更しているのはこれ show [global] variables like '%{variable_name}%'; で確認できる スコープの概念が存在する グローバル : サーバ全体で持つ値 セッション : セッション（接続）ごとに持つ値 このことを知っていると、 「とある接続にだけ set wait_timeout = x; を実行して切り分 ける」といった所作ができるようになる 61
62. チューニングのサイクル 1. 問題を特定する show full processlist 、スロークエリログおよび pt-query-digest 、 show engine innodb status 、 sys スキーマ など 2. 遅い理由を分析する EXPLAIN 、 Performance Insights、 show status 、各種メトリクスを見る など 3. 対処（チューニング） ・検証 インデックスの作成、ヒント句付与、クエリの書き換え など 対処が正しいか効果を測定し、最初に戻る 62
63. 1. 特定｜ show full processlist https://dev.mysql.com/doc/refman/8.0/ja/show-processlist.html 「現在実行中の」 クエリを確認できる Command が Query で実行時間が長い = 時間のかかっているクエリ 障害時は数秒おきに実行して、スタックしているのかを見たりする あくまで実行時点のスナップショットを示すものなので、定点観測は別途工夫が必要 最近では p_s.processlist テーブルを使うといいらしい https://dev.mysql.com/doc/refman/8.0/ja/performance-schema-processlist-table.html 63
64. 1. 特定｜スロークエリログ https://dev.mysql.com/doc/refman/8.0/ja/slow-query-log.html long_query_time より時間のかかった 「実行完了した」 クエリを出力する 実行完了時に出力されるログなので、実行中のクエリはここに載っていない 慢性的に DB が遅いとき、一時的に long_query_time を 0.1 などの値に小さくして 記録しつつ pt-query-digest で概観を把握する、といったことをよくやる MySQL 8.0.14 以降・ Aurora 3 系であれば、 log_slow_extra 変数が追 加の情報を出力するようになる。 ON にしよう Created_tmp_disk_tables など「なぜ遅かったのか」の手がかりが増えるのが嬉しい https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0154 64
65. 2. 分析｜クエリの効率の定量的な判断 Rows sent に対し Rows examined が大きいものは、一般に効率が悪い Rows_sent : クライアントに返した行数 Rows_examined : 読み込んだ行数 MySQL は OLTP がメインターゲットなので、 「ゴソッと取ってくる」ような操作はそもそも向いていない // スロークエリログから見た例 // 1 行返したいだけなのに 370 万行読んでいる // name で絞り込むための適切なインデックスが存在すれば Rows_examined を減らせそう # Time: 2021-12-21T09:35:48.286685Z # User@Host: hoge[fuga] @ [10.30.204.100] Id: 20051 # Query_time: 6.650755 Lock_time: 0.000064 Rows_sent: 1 Rows_examined: 3759162 SET timestamp=1640079348; SELECT * FROM huge_table WHERE name = 'warashina' LIMIT 1; 65
66. 2. 分析｜ EXPLAIN https://dev.mysql.com/doc/refman/8.0/ja/explain.html おなじみ EXPLAIN 読み方を説明すると時間が足りないので、いいドキュメントを見つけてください type, key, rows, Extra 列が読めればけっこう戦えます https://nippondanji.blogspot.com/2009/03/mysqlexplain.html など 通常の EXPLAIN が示すものは見積もりだけど、 MySQL 8.0.18 からは EXPLAIN ANALYZE が追加され、 「実際にクエリを実行して」の実行時間を教えてくれ るようになった 時間がかかっているステップがどこなのかを知れるようになったが、読み方は要練習 66
67. 2. 分析｜ Performance Insights https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/USER_PerfInsig hts.Overview.html RDS 向けの DB 版 APM のようなもの 無料枠でいいのでとりあえず有効化しておくのがオススメ MySQL 側も performance_schema = ON で情報を取得できるようにしておく 多少のメモリを消費するので、これを ON にした前提でサイズを考える 「トップ SQL」 「トップ待機イベント」からもぐら叩きをしていけばいい io/table/sql/handler 等の具体的な待機イベント名の示す意味は、都度ググる Rows sent, examined, そのクエリの実行回数等の情報が出るのも嬉しい 67
68. 3. 対処｜チューニングの手段 a. パラメータチューニング innodb_buffer_pool_size など、 MySQL のパラメータを変更する いわゆる「全体最適」を目指して、スループットを向上させることが多い Aurora MySQL の場合、 （経験上）積極的に行う機会はあまりない b. クエリチューニング クエリ単体をチューニングし、レイテンシ向上を狙う 「個別最適」のように見えて効果が小さいように見えるが、現実には非常に重要 バッファプールや CPU は共有リソース 効率のよいクエリは CPU を専有しないしバッファプールも荒らさないので、 結果的に DB 全体のパフォーマンスが向上することがよくある 68
69. 3-a. 対処｜パラメータチューニング 以下の繰り返し サーバのステータス変数やスロークエリログから、稼働状況を確認する システム変数が適切かを判断する 設定値を調整する Aurora MySQL の場合、パフォーマンス関連のシステム変数については、 わりと「そのまま」でうまく動くように設計されている Amazon Aurora を使いこなすためのベストプラクティス 「チューニング指針」あたり Amazon Aurora MySQL データベース設定のベストプラクティス も参考になる 広い意味では、スケールアップ・アウトもこれに相当する 69
70. スケールアップ インスタンスサイズを変更し、単体での性能改善を図る 一部のパラメータはインスタンスサイズに比例している お手軽なので、一時的な負荷増の緩和策にも スケールアウト Reader の利用やシャーディングで、全体での性能改善を図る Reader の利用 : 読み込みのスケールアウト シャーディング : 読み込み・書き込み両方のスケールアウト それなりに工数は必要で、アプリ側の複雑性も増す トラブル時の影響範囲（ Blast radius）のコントロールにもなる 特定インスタンスのダウン = サービスダウン を回避 70
71. スケールアップが効果の無い・薄い例 事業者としては「お金で解決できる問題かどうか」を判断できると嬉しい 集計クエリの速度改善のためにコア数を増やす MySQL の SQL 実行は原則シングルスレッド（ 1 クエリ = 1 CPU）なので、 大量行スキャンの 1 クエリを高速にしたいとき、コア数を増やしても改善しない 特定の行にロックが集中するケースも、コアを増やす意味はない データサイズよりもはるかに大きいメモリを持つインスタンスサイズ もちろん CPU や NW がサチる例もあるが、 クエリチューニング不足か、ロジックの問題なパターンのほうがよく見る 71
72. 3-b. 対処｜クエリチューニング インデックスを使いこなしてほしい 実行計画の制御 インデックスヒント JOIN ヒント オプティマイザヒント 「クエリチューニングで対処すべき」という状態までたどり着けば、 既にたくさんの有用なドキュメントが存在するので、あとは手際よくやるだけ 「 Rows examined の値を小さくする」という原則に従っていけば、そうそう外さない 72
73. 6. ケーススタディ 73
74. 例題 ※ 社内情報のため非公開 74
75. 問題が発生した時に何を考えるか 例えば「 DB が遅い」と相談されたとき 1. 「遅い」という事象を確定させる DB が遅いということが、客観的事実（スロークエリログなど）により 分かっているのか、単に DB が疑わしいからそう言っているのか 2. 「遅い」ことを深堀りする クエリ単体で遅いのか、 DB が全体的に遅いのか 常に遅いのか、一定の条件で遅いのか 後者なら「たまに」 、 「とあるタイミングで」 、 「特定の顧客への処理で」 75
76. 周辺の情報を整理する 継続中の問題か、過去発生した問題なのか いつから問題が起こっているのか 対処しないとどうなるのか 対策にどこまでの工数をかけられるのか このあたりで「まず緩和策を考える」かどうかを決める 76
77. 補足 : よくある「クエリが遅い」理由いろいろ 単純に処理するレコードが多い インデックスがないか、使われていない ロック範囲の競合が発生している リソースが足りていない バッファプールが足りなくてディスク IO が発生している CPU usage が飽和している クエリが余計な処理をしている MySQL が苦手な処理をしている 一時テーブルの実体化（ Created_tmp_disk_tables のカウント）が典型的 77
78. 対応方針 ※ 社内情報のため非公開 78
79. 根本対応｜メトリクスを確認する (1/3) Performance Insights を見る io/table/sql/handler というイベントが多くを占めている ドキュメント より、テーブルへのアクセスに関するイベントであることが分かる ※ 社内情報のため画像非公開 79
80. 根本対応｜メトリクスを確認する (2/3) カウンターメトリクス から、ディスクアクセスが発生しているかを判断する db.Cache.innoDB_buffer_pool_hits はバッファプールから読めた回数 db.Cache.Innodb_buffer_pool_reads は 読めなかった（ディスクアクセス） 回数 ⇒ よって、リソース（メモリ）は足りていそう。 CPU コア数を増やしても 　 単発のクエリは早くならないので、スケールアップは効果が薄い。 ※ 社内情報のため画像非公開 80
81. 根本対応｜メトリクスを確認する (3/3) Created_tmp_disk_tables ステータス変数は増加していない スロークエリログからは、集計クエリが多数流れていることは分かる ログに出ているということは、 「全部のクエリが帰ってこない」というわけではない 一連のタスクのうち一部（のテナント？）が終わらない 適当にピックアップしたログでは Rows_examined: 14088579 とかなりの行数だが、 この時点では「本当に 1400 万レコードを読むことが集計に必要」なのかは知らない ※ 社内情報のため非公開 81
82. 補足 : よくある「クエリが遅い」理由いろいろ 単純に処理するレコードが多い インデックスがないか、使われていない ロック範囲の競合が発生している リソースが足りていない バッファプールが足りなくてディスク IO が発生している CPU usage が飽和している クエリが余計な処理をしている MySQL が苦手な処理をしている 一時テーブルの実体化（ Created_tmp_disk_tables のカウント）が典型的 82
83. 根本対応｜インデックス作成の検討 結局、素直に実行計画を見てインデックスの作成を検討すべき、となる ※ 社内情報のため非公開 83
84. 根本対応｜インデックス作成のリリース ※ 社内情報のため非公開 84
85. 対処のコツ （ DB に限った話ではないけど） 再現環境を作る 現代は clone といった機能で簡単に複製できる、ありがたい時代 最小の再現方法を探す どの WHERE id = x なら再現する、など 共有する・人に聞く 後から来た人でも分かるよう、マメに post しておく 「人に聞く」ために状況を整理しておく 85
86. ex. その他雑多な話 86
87. メンテナンスの心得 大きめのメンテであれば、これぐらいは確認したい 動作テストを行う メンテナンスの リハーサル を行う 切り戻しの判断基準 と手順 を決めておく よく寝て 2 人で参加する メンテ開始時に 確実に バックアップを取る 更新クエリが飛んでこないことを DB 側で 確認する アプリを止めたから OK としない 87
88. トラブルを後から追うのは難しい 「ある時点でどのようにクエリが動作していたか」を追いかけるのは難しい。 ゆえに APM のようなツールでメトリクスを収集しておくことは大事 ロック競合なども、その時点の状態を保存したい いつ発生するか分からない障害は手強い 問題を任意のタイミングで再現出来るのであれば、 メトリクスの収集もし放題と言えるので、再現検証もとても大事 88
89. 普段は何をモニタリングすればいいのか データベースは「クエリの結果を返す箱」のはずだから、 許容できるレイテンシでクエリが返せているか が最優先に見たいはず 接続性 エラーログ スロークエリ、各種レイテンシ CPU, RAM, 接続数といったメトリクスに機械的にアラートを設定するよりも、 ベースラインを知り、異常かどうかを把握できるようにしておきたい 大事なメトリクスだが、 CPU High それ自身でユーザに影響があるわけではない https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/MonitoringOverview.ht ml も参考に 89
90. 普段は何をモニタリングすればいいのか New Relic や Datadog のような監視 SaaS はよく出来ている 餅は餅屋 APM だけで十分なこともよくありそう 監視の考え方がドキュメントに記されていたりして参考になることも メトリックの保存期間 季節性のあるワークロードなら 1年 +1ヶ月 は保存したい 90
91. Aurora Serverless v2 よく出来ててすごいヤツ ただし、サーバがスケールする のと システムがスケールする のは別の話 投資したリソースの分だけパフォーマンスも上がってほしいが、そうでないことはよくある 今まで通りクエリチューニング etc が重要なのは変わらない 細かいことを考えたくない・初期の運用の場合に特におすすめ Provisioned は RI も使える（ 40〜 60 % オフ）ことを加味すると、 コスト削減の方面はそんなに期待できない スケールダウン時に縮小されたメモリの内容が、再度スケールアップした時に 再配置されているわけではない（多分）ので、 p99 latency は安定しないかも 試しましょう 91
92. Aurora の場合のバージョンアップ戦略 バージョンポリシーなるものが公開されています https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/Aurora.VersionP olicy.html 迷ったら最新版 or 一個前 or LTS で OK だと思います 基本的に、バージョンダウンは出来ない・やらないものと思ったほうがいいです バージョンアップのテストには、 clone を使ったり、 B/G deploy で作ったクラスタに読み込みワークロードを流すのもよいです 92
93. セオリーから外れない データベースの失敗はダメージがでかいので、手堅い方に倒す DB のやらなさそうな事をムリにさせないようにする なんとなく正規化を崩さない 画像を RDB に入れない ログを何でも全部 RDB に入れない マイナーな機能に注意する "日本語のカラム名にしてみました " は、マイナーなバグを踏みそうですよね 「機能として存在する」ことと「実運用に耐えている」ことは別物 93
94. Q&A 94
95. 参考文献（書籍） この資料を書いた人はこの本にお世話になっています : 『 MySQL徹底入門 第 4版』 『データ指向アプリケーションデザイン』 『入門 監視』 95
96. 参考文献（ Web ページ） この資料を書いた人はこのページにお世話になっています : MySQL 8.0 リファレンスマニュアル MySQL道普請便り 記事一覧 | gihyo.jp 漢のコンピュータ道 yoku0825 さんの speakerdeck Percona Database Performance Blog USE THE INDEX, LUKE! Hack MySQL 96