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

Slide 1

Slide 1 text

開発者向け MySQL 入門 2024/02/21 1

Slide 2

Slide 2 text

$ whoami @wa6sn SQL は書けません 3 年ぐらい DBA をやっていました最近はセキュリティが気になっています 2

Slide 3

Slide 3 text

おことわり社内の勉強会用に作成した資料であり、内容を保証するものではありません「MySQL を触っているが運用に自信がない」くらいの、アプリケーションエンジニアの方に向けて書いています「SQL の書き方」は最後まで出てきません出来るだけ「通常の MySQL」と「Aurora MySQL」の事情は、意識して書いていますが、完全ではないです 3

Slide 4

Slide 4 text

目次 1. はじめに 2. アーキテクチャの概観 3. インデックス 4. オプティマイザと実行計画 5. チューニング 6. ケーススタディ 7. Q&A 4

Slide 5

Slide 5 text

1. はじめに 5

Slide 6

Slide 6 text

実施の意図ほとんどのシステムにおいて、 DB のダウン ≒ サービスダウンとなる RDS のようなマネージドサービスの台頭により、DB を運用する負荷は下がってきているが、その内部の知識が助けになることは未だにあるそこで、トラブル時の調査の勘所や、パフォーマンスの出し方について、アーキテクチャから演繹的に理解することを目指す「こういう仕組みだった気がするから、この事象の対応方針はこれでいいかな？」と考えることができたらいいな 6

Slide 7

Slide 7 text

運用の難易度は、概ね規模に比例する（当たり前かもしれないが）データサイズ・QPS が小さいうちは、運用で困ることはほとんどないサービスが成長することで問題は顕在化してくる一方、その頃には身動きが取りづらくなっているというのが DB の悩ましいところ 7

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

なぜ流行ったか? https://www.slideshare.net/matsunobu/ss-28303485/8 Facebook で DB Engineer をされている松信さんという方の言葉抜粋すると（当時は）無償だったから導入の障壁が低かったから似た立場の PostgreSQL より高速だと言われていたからレプリケーションで簡単にスケールアウト出来たから 9

Slide 10

Slide 10 text

現時点での主流なバージョン 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

Slide 11

Slide 11 text

2. アーキテクチャの概観 11

Slide 12

Slide 12 text

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 へのアクセスと、ソート等「データを引っ張ってきてから」の色々 Storage Engine テーブルへのデータの読み書き 12

Slide 13

Slide 13 text

ストレージエンジン 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

Slide 14

Slide 14 text

"素朴なデータベースの実装" を考えてみる Client Disk Memory 参照更新（ディスクにデータを永続化したい・ディスクよりメモリのほうが高速という前提で）参照クエリメモリ上にレコードがあれば、そのレコードをクライアントに返すメモリ上にレコードがなければ、ディスクから読み、メモリに追加しつつ返す更新クエリコミット時、ディスク上のテーブルに直接書き込む対応するメモリ上のレコードは、メモリとの整合性のため破棄する 14

Slide 15

Slide 15 text

"素朴なデータベースの実装" の課題を想像してみる Client Disk Memory 参照更新更新のたびにテーブルへの書き込みが発生してしまい、レイテンシが大きそうメモリが十分に大きいか、賢く管理しないと、むしろ参照系でメモリを経由するオーバーヘッドが大きくなりそうレコードという単位はそのまま扱うには細かく、スループットが出なさそう etc, といったことが考えられる 15

Slide 16

Slide 16 text

改めて InnoDB のアーキテクチャを見る我々の想像している「レコード」というものは Tablespaces（*.ibd）にある In-Memory に "Buffer Pool" なる重要なコンポーネントが居る（ https://dev.mysql.com/doc/refman/8.0/ja/innodb-architecture.html より） 16

Slide 17

Slide 17 text

Tablespaces？複数のレコードの塊が "ページ" という単位で管理される、ということだけ知ろう "ページ" が MySQL が裏側でよしなに操作する際の主なデータ単位だから（ https://jackyfkc.github.io/cs/data_store/mysql/innodb.html より） 17

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

INSERT, UPDATE, DELETE クエリで何が起こるか (1) バッファプール上のデータを更新しつつ、更新内容をログバッファに書き込み Client Buffer Pool Log Buffer Tablespace Redo Log Disk Memory Server 3. ログバッファに書き込み 1. 更新クエリ 2. バッファプール上のデータを更新 19

Slide 20

Slide 20 text

INSERT, UPDATE, DELETE クエリで何が起こるか (2) コミット時はテーブルよりも先にログファイルが永続化される WAL: Write-Ahead-Log という手法 Redo ログが永続化されたら書き込み完了とみなす Client Buffer Pool Log Buffer Tablespace Redo Log Disk Memory Server 2. Redo ログに ﬂush 1. コミット 3. ﬂush 出来たら Ack コミット != テーブルへの永続化 20

Slide 21

Slide 21 text

INSERT, UPDATE, DELETE クエリで何が起こるか (3) テーブルへの永続化（チェックポイント処理）は非同期に行うこれにより「Redo ログとバッファプールには反映されているけど、テーブルスペースには反映されていない」なる状態が存在するある意味、バッファプールこそが最新のデータを持つとも言える Client Buffer Pool Log Buffer Tablespace Redo Log Disk Memory Server ダーティページ : テーブルに反映されていないデータバックグラウンドでテーブルに更新される 21

Slide 22

Slide 22 text

なぜ非同期に永続化するのか？ディスクへの I/O 量と回数を削減したいから回転メディアを前提とした当時の設計においては、特に重要だった都度テーブルへ同期的に更新してしまうと、毎回ランダム書き込みになり高コスト代わりに Redo ログへ、ページの変更操作を追記低コストなシーケンシャル書き込みに変換している、とも「同じレコードへの操作なら最終状態だけ反映したろ！」といった細かいテクも 22

Slide 23

Slide 23 text

クラッシュリカバリサーバプロセスがクラッシュすると、メモリ上のページは失われる Redo ログに書かれた、ページへの変更操作を再実行し、リカバリする Client Buffer Pool Log Buffer Tablespace Redo Log Disk Memory Server Redo ログをテーブルに適⽤ロールフォワードリカバリ 23

Slide 24

Slide 24 text

Aurora での大きな改良点ディスク I/O の責務の多くを専用の分散ストレージシステムに移譲チェックポイントを気にする必要がなくなり、安定したレイテンシを出せるようにクラッシュリカバリも早くなった（図は https://bit.ly/31ZMsMm より抜粋） 24

Slide 25

Slide 25 text

運用を考えたときにデータ構造やクエリに手を加えていなくても、将来的に「データ量が増える → バッファプールからあふれ、ストレージへのアクセスが増える → パフォーマンスが落ちる」といったことがありえる一般的にデータ量・レコード量が多いほど、あらゆる操作のコストが高くなり、運用の難易度は上がる（それはそう）より正確には、バッファプールは LRU ベースのため「生きたデータの量が」ではある 25

Slide 26

Slide 26 text

この節のまとめディスク I/O は常に高コストという前提がある DB に限らず、あらゆるシステムで I/O 削減の工夫がされている InnoDB アーキテクチャを見ても、様々な箇所に「バッファ」が登場する参照・更新いずれの場合もデータはメモリ（バッファプール）を通る innodb_buffer_pool_size は非常に重要なパラメータであるデフォルトは {DBInstanceClassMemory*3/4} バッファプールは共有リソース非効率なクエリは全体のパフォーマンスに影響しうる他で使いたいキャッシュを追い出しちゃう可能性があるから 26

Slide 27

Slide 27 text

3. インデックス 27

Slide 28

Slide 28 text

MySQL のインデックスイメージ図実際には B+Tree というデータ構造ですが、今回はこのイメージで説明します # 紙面の都合で擬似コードです CREATE TABLE `member` ( ìd`, `name`, àge`, àddress`, # カラム PRIMARY KEY (ìd`), # プライマリキー KEY ìdx_age` (àge`) # age のみのインデックス KEY ìdx_name_age` (`name`, àge`), # 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

Slide 29

Slide 29 text

全てはインデックス https://dev.mysql.com/doc/refman/8.0/ja/innodb-index-types.html クラスタインデックス "テーブルそのもの" と言ってしまっていいプライマリキーの順番に並んでいる一部の例外を除き、すべてのカラムの値を持つセカンダリインデックス "データを並べ替えた、テーブルの部分的なコピー" と言ってしまっていいクラスタインデックス以外を指す指定したカラムの値の順番に並んでいる指定したカラム以外のカラムを参照するときのために、プライマリキーの値を持つ 29

Slide 30

Slide 30 text

例 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

Slide 31

Slide 31 text

例 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

Slide 32

Slide 32 text

例 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

Slide 33

Slide 33 text

例 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

Slide 34

Slide 34 text

例 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

Slide 35

Slide 35 text

例 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

Slide 36

Slide 36 text

例 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

Slide 37

Slide 37 text

例 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

Slide 38

Slide 38 text

例 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

Slide 39

Slide 39 text

例 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

Slide 40

Slide 40 text

インデックスがロックの範囲も決める例 9 のようなケースではテーブルの全ての行にロックを取ってしまう「条件に合った行をロック」ではなく、「インデックスに対してロック」もっと言うとネクストキーロックだが今回のスコープ外とするそのため「 LIMIT 1 しておけばよくね？」は、よくない（イメージで説明すると） age < 30 を条件に更新したいトランザクション A と、 age > 40 を条件に更新したいトランザクション B があったとしよう。 age のインデックスがある : age でソートされているから A, B の操作は干渉しないことが分かる。　A → B の順に実行しても、B → A の順に実行しても問題ないので、並列に処理できるない : お互いの条件に合うレコードがどこにあるか分からないので、　全部のレコードを探す必要がある。A, B は並列に実行できない 40

Slide 41

Slide 41 text

インデックスは更新コストとのトレードオフセカンダリインデックスは "データを並べ替えた、テーブルの部分的なコピー" なので、レコードの更新時にはセカンダリインデックスにも更新がされるデータの書き換え + データの順序を維持するためのコストがあるインデックスを作成しすぎると更新性能に影響が出る、という現象は 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

Slide 42

Slide 42 text

原則 1 テーブルに 1 インデックスを使って探索する member テーブルには idx_age と idx_name のみが存在する。 WHERE age < ? AND name = ? のような複合条件はどう処理されるか？ MySQL は 1 テーブルに 1 インデックスを使って探索するので、 idx_name か idx_age のどちらか一方のインデックスが利用されるのが原則 idx_age と idx_name を候補とし、「絞り込みやすそうな方」を MySQL が判断（後述）してどちらかを利用する "インデックスマージ" という、複数のインデックスを利用して探索したあと、それらの集合演算を行うしくみが存在するものの、初手では考慮に入れなくていい適用できるパターンの把握も難しいので、素直に複合インデックスを作るべき「全カラムに単一カラムのインデックスを貼って、インデックスマージを狙えばよくね？」とはならない 42

Slide 43

Slide 43 text

複合インデックスのカラム順序 (name, age) と (age, name) は別物ですかはい前者ならまず name の順に並び替えて、name が同値のものを age で並び替えています WHERE age < ? だけで絞り込みたいときに (name, age) は利用できません (name, age) と (age, name) のどちらを作るべきですかその DB に流れるクエリによりけりです両方とも欲しい場合もありえます雑に両方作って使ってない方を消す、という手もなくはないですどちらも使える場合、最初のカラムでより絞り込める方を使うはずです例えば (name, age) があるなら (name) は要らないことがほとんどです 43

Slide 44

Slide 44 text

十分なインデックスが存在するか？「作るべきか」を最終的に判断するのは人間なので、都度やっていくスロークエリログや APM から時間のかかるクエリを特定するスロークエリログには log_queries_not_using_indexes のような便利そうな変数もあるけど、フォーカスしたいのは「クエリが遅いか」なので、好みで long_query_time は 0.5 など 1 以下も設定できる都度 EXPLAIN で実行計画を確認する 44

Slide 45

Slide 45 text

不要なインデックスがないか？ 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

Slide 46

Slide 46 text

この節のまとめ InnoDB は、クラスタインデックスというデータの持ち方をしているセカンダリインデックスは "データを並べ替えた、テーブルの部分的なコピー" インデックスは参照処理と更新のトレードオフとはいえ、現実は参照重視なことが多く、「足りなくて困る」ことが多いインデックスがロックの範囲も決める適切なインデックスを作ると、バッファプールが効率よく使えるバッファプールに載るのは「クライアントに返したレコード」ではなく「スキャンしたレコード」。「実際に必要なデータ」を不足なく載せたいインデックスもバッファプールに載る 46

Slide 47

Slide 47 text

4. オプティマイザと実行計画 47

Slide 48

Slide 48 text

前節で出るであろう疑問そもそも「どのインデックスを使うべきか」はどうやって判断しているのか？使えそうなインデックスが複数あるとき、どのように優先されるのかもっと広く言うと、JOIN の順序だったり、アクセスパス全般何らかのロジックに基づいてデータへのアクセス方法が決まるのであれば、それは制御できるのか？ 48

Slide 49

Slide 49 text

Slide 50

Slide 50 text

オプティマイザと実行計画 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

Slide 51

Slide 51 text

統計情報？ 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

Slide 52

Slide 52 text

実行計画 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

Slide 53

Slide 53 text

クエリが同じなら実行計画も同じ、とは限らないたとえばインデックスの有無バージョンアップによるオプティマイザの変化 8.0 から入った Hash join や、新しい最適化アルゴリズムデータ量、カーディナリティの変化そもそもデータは偏るもの（大口の顧客的な）統計情報の再取得一定量のデータが更新されるなどを契機に、統計情報も更新される一応、 ANALYZE TABLE という DDL でガチャ能動的な更新が出来る 53

Slide 54

Slide 54 text

カーディナリティ「インデックス」の節で挙げた例は、いずれも「インデックスを使うことで、十分に絞り込めることが期待される」という前提がある実際には、「セカンダリインデックスを使っても大して絞り込めないので、最初からクラスタインデックスでフルスキャンしたほうがよさそう」とオプティマイザが判断してしまうこともよくあるカラム内に異なる値がどれほどあるかをカーディナリティと呼ぶ。インデックスを貼るときは、カーディナリティの高い（値の種類の多い ≒ 絞り込みやすい）カラムを選ぶと効果が高い。 54

Slide 55

Slide 55 text

補足: 「MySQL は JOIN が遅い」という言説おそらく、そう言われるもとになったのは（その頃の）オプティマイザが賢くなかった最適化のアレコレが（他の DB に比べ）不足していた JOIN アルゴリズムが NLJ しかなかったいい感じのクエリの書き換えを MySQL 側でやってくれなかった統計情報が不正確だった MySQL 5.5 で innodb_stats_persistent_sample_pages に相当するパラメータは、デフォルト 8 ページしかなかったなど、様々な由来がありそうです。が、通説を信じるのではなく試したほうがいいです 55

Slide 56

Slide 56 text

最大ケースを想像できるとよい同じ構造のクエリに見えて、実行計画が変わってしまう例 LIMIT x の絞り込み件数 WHERE .. IN .. の対象の要素の数（参考: freee さんの記事）特定の閾値を超えるとパフォーマンスが劣化する例 GROUP BY や ORDER BY のような一時テーブルを利用するクエリで件数が多い https://dev.mysql.com/doc/refman/8.0/ja/internal-temporary-tables.html 集計操作を伴うクエリに対し、一定サイズまではメモリ上の一時テーブルを用いるが、それより大きいとメモリからディスクに書き出す挙動をするディスクに書き出す処理は高コストなので「特定の ID を指定したクエリでやたら遅いんだけど」という事象が発生する 56

Slide 57

Slide 57 text

この節のまとめどのようにデータにアクセスするかはオプティマイザが決めているインデックスを使うべきか、どれを使うべきか、etc 基本的にはオプティマイザ任せでいい EXPLAIN は読めるようになろう各種ヒントや JOIN 順の指定で、オプティマイザの動作をある程度制御できるあくまで「その時点での（データ量・分布での）判断では」になるので、ヒントによる制御は、運用（見直し）が発生するとも考えられるクリティカルなシステムなどで、実行計画を安定させたいモチベーションはあって、 Oracle や PostgreSQL はこの辺の運用がもっと細かくやれる 57

Slide 58

Slide 58 text

5. チューニング 58

Slide 59

Slide 59 text

性能の悩みにどう向き合うかパフォーマンス問題はワークロードによりけりシステムの数だけ課題があるので、テストとモニタリングが重要机上ですべての挙動を把握しようとするより、手軽に本番同様の環境でテスト出来るようにしたほうがコスパがいいことがよくあるデータ量、カーディナリティ、リクエスト量は近くあるべきトラブルが発生した際に再現性も確保しやすくなる Aurora であれば、クローン機能などは積極的に使いたいパフォーマンステストも、なるべく本番同様のリクエストを模するキャッシュの影響を丸めるためにある程度の時間、負荷をかけ続ける単発のクエリだけでは心もとないので、シナリオを用意するとよい 59

Slide 60

Slide 60 text

前提知識｜ステータス変数 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

Slide 61

Slide 61 text

前提知識｜システム変数 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

Slide 62

Slide 62 text

チューニングのサイクル 1. 問題を特定する show full processlist 、スロークエリログおよび pt-query-digest 、 show engine innodb status 、 sys スキーマなど 2. 遅い理由を分析する EXPLAIN 、Performance Insights、 show status 、各種メトリクスを見るなど 3. 対処（チューニング）・検証インデックスの作成、ヒント句付与、クエリの書き換えなど対処が正しいか効果を測定し、最初に戻る 62

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

3. 対処｜チューニングの手段 a. パラメータチューニング innodb_buffer_pool_size など、MySQL のパラメータを変更するいわゆる「全体最適」を目指して、スループットを向上させることが多い Aurora MySQL の場合、（経験上）積極的に行う機会はあまりない b. クエリチューニングクエリ単体をチューニングし、レイテンシ向上を狙う「個別最適」のように見えて効果が小さいように見えるが、現実には非常に重要バッファプールや CPU は共有リソース効率のよいクエリは CPU を専有しないしバッファプールも荒らさないので、結果的に DB 全体のパフォーマンスが向上することがよくある 68

Slide 69

Slide 69 text

3-a. 対処｜パラメータチューニング以下の繰り返しサーバのステータス変数やスロークエリログから、稼働状況を確認するシステム変数が適切かを判断する設定値を調整する Aurora MySQL の場合、パフォーマンス関連のシステム変数については、わりと「そのまま」でうまく動くように設計されている Amazon Aurora を使いこなすためのベストプラクティス「チューニング指針」あたり Amazon Aurora MySQL データベース設定のベストプラクティスも参考になる広い意味では、スケールアップ・アウトもこれに相当する 69

Slide 70

Slide 70 text

スケールアップインスタンスサイズを変更し、単体での性能改善を図る一部のパラメータはインスタンスサイズに比例しているお手軽なので、一時的な負荷増の緩和策にもスケールアウト Reader の利用やシャーディングで、全体での性能改善を図る Reader の利用 : 読み込みのスケールアウトシャーディング : 読み込み・書き込み両方のスケールアウトそれなりに工数は必要で、アプリ側の複雑性も増すトラブル時の影響範囲（Blast radius）のコントロールにもなる特定インスタンスのダウン = サービスダウンを回避 70

Slide 71

Slide 71 text

スケールアップが効果の無い・薄い例事業者としては「お金で解決できる問題かどうか」を判断できると嬉しい集計クエリの速度改善のためにコア数を増やす MySQL の SQL 実行は原則シングルスレッド（ 1 クエリ = 1 CPU）なので、大量行スキャンの 1 クエリを高速にしたいとき、コア数を増やしても改善しない特定の行にロックが集中するケースも、コアを増やす意味はないデータサイズよりもはるかに大きいメモリを持つインスタンスサイズもちろん CPU や NW がサチる例もあるが、クエリチューニング不足か、ロジックの問題なパターンのほうがよく見る 71

Slide 72

Slide 72 text

3-b. 対処｜クエリチューニングインデックスを使いこなしてほしい実行計画の制御インデックスヒント JOIN ヒントオプティマイザヒント「クエリチューニングで対処すべき」という状態までたどり着けば、既にたくさんの有用なドキュメントが存在するので、あとは手際よくやるだけ「 Rows examined の値を小さくする」という原則に従っていけば、そうそう外さない 72

Slide 73

Slide 73 text

6. ケーススタディ 73

Slide 74

Slide 74 text

例題 ※ 社内情報のため非公開 74

Slide 75

Slide 75 text

問題が発生した時に何を考えるか例えば「DB が遅い」と相談されたとき 1. 「遅い」という事象を確定させる DB が遅いということが、客観的事実（スロークエリログなど）により分かっているのか、単に DB が疑わしいからそう言っているのか 2. 「遅い」ことを深堀りするクエリ単体で遅いのか、DB が全体的に遅いのか常に遅いのか、一定の条件で遅いのか後者なら「たまに」、「とあるタイミングで」、「特定の顧客への処理で」 75

Slide 76

Slide 76 text

周辺の情報を整理する継続中の問題か、過去発生した問題なのかいつから問題が起こっているのか対処しないとどうなるのか対策にどこまでの工数をかけられるのかこのあたりで「まず緩和策を考える」かどうかを決める 76

Slide 77

Slide 77 text

補足: よくある「クエリが遅い」理由いろいろ単純に処理するレコードが多いインデックスがないか、使われていないロック範囲の競合が発生しているリソースが足りていないバッファプールが足りなくてディスク IO が発生している CPU usage が飽和しているクエリが余計な処理をしている MySQL が苦手な処理をしている一時テーブルの実体化（ Created_tmp_disk_tables のカウント）が典型的 77

Slide 78

Slide 78 text

対応方針 ※ 社内情報のため非公開 78

Slide 79

Slide 79 text

根本対応｜メトリクスを確認する (1/3) Performance Insights を見る io/table/sql/handler というイベントが多くを占めているドキュメントより、テーブルへのアクセスに関するイベントであることが分かる ※ 社内情報のため画像非公開 79

Slide 80

Slide 80 text

根本対応｜メトリクスを確認する (2/3) カウンターメトリクスから、ディスクアクセスが発生しているかを判断する db.Cache.innoDB_buffer_pool_hits はバッファプールから読めた回数 db.Cache.Innodb_buffer_pool_reads は読めなかった（ディスクアクセス）回数 ⇒ よって、リソース（メモリ）は足りていそう。CPU コア数を増やしても　単発のクエリは早くならないので、スケールアップは効果が薄い。 ※ 社内情報のため画像非公開 80

Slide 81

Slide 81 text

根本対応｜メトリクスを確認する (3/3) Created_tmp_disk_tables ステータス変数は増加していないスロークエリログからは、集計クエリが多数流れていることは分かるログに出ているということは、「全部のクエリが帰ってこない」というわけではない一連のタスクのうち一部（のテナント？）が終わらない適当にピックアップしたログでは Rows_examined: 14088579 とかなりの行数だが、この時点では「本当に 1400 万レコードを読むことが集計に必要」なのかは知らない ※ 社内情報のため非公開 81

Slide 82

Slide 82 text

Slide 83

Slide 83 text

根本対応｜インデックス作成の検討結局、素直に実行計画を見てインデックスの作成を検討すべき、となる ※ 社内情報のため非公開 83

Slide 84

Slide 84 text

根本対応｜インデックス作成のリリース ※ 社内情報のため非公開 84

Slide 85

Slide 85 text

対処のコツ（DB に限った話ではないけど）再現環境を作る現代は clone といった機能で簡単に複製できる、ありがたい時代最小の再現方法を探すどの WHERE id = x なら再現する、など共有する・人に聞く後から来た人でも分かるよう、マメに post しておく「人に聞く」ために状況を整理しておく 85

Slide 86

Slide 86 text

ex. その他雑多な話 86

Slide 87

Slide 87 text

メンテナンスの心得大きめのメンテであれば、これぐらいは確認したい動作テストを行うメンテナンスのリハーサルを行う切り戻しの判断基準と手順を決めておくよく寝て 2 人で参加するメンテ開始時に確実にバックアップを取る更新クエリが飛んでこないことを DB 側で確認するアプリを止めたから OK としない 87

Slide 88

Slide 88 text

トラブルを後から追うのは難しい「ある時点でどのようにクエリが動作していたか」を追いかけるのは難しい。ゆえに APM のようなツールでメトリクスを収集しておくことは大事ロック競合なども、その時点の状態を保存したいいつ発生するか分からない障害は手強い問題を任意のタイミングで再現出来るのであれば、メトリクスの収集もし放題と言えるので、再現検証もとても大事 88

Slide 89

Slide 89 text

普段は何をモニタリングすればいいのかデータベースは「クエリの結果を返す箱」のはずだから、許容できるレイテンシでクエリが返せているかが最優先に見たいはず接続性エラーログスロークエリ、各種レイテンシ CPU, RAM, 接続数といったメトリクスに機械的にアラートを設定するよりも、ベースラインを知り、異常かどうかを把握できるようにしておきたい大事なメトリクスだが、CPU High それ自身でユーザに影響があるわけではない https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/MonitoringOverview.ht ml も参考に 89

Slide 90

Slide 90 text

普段は何をモニタリングすればいいのか New Relic や Datadog のような監視 SaaS はよく出来ている餅は餅屋 APM だけで十分なこともよくありそう監視の考え方がドキュメントに記されていたりして参考になることもメトリックの保存期間季節性のあるワークロードなら 1年+1ヶ月は保存したい 90

Slide 91

Slide 91 text

Aurora Serverless v2 よく出来ててすごいヤツただし、サーバがスケールするのとシステムがスケールするのは別の話投資したリソースの分だけパフォーマンスも上がってほしいが、そうでないことはよくある今まで通りクエリチューニング etc が重要なのは変わらない細かいことを考えたくない・初期の運用の場合に特におすすめ Provisioned は RI も使える（ 40〜60 % オフ）ことを加味すると、コスト削減の方面はそんなに期待できないスケールダウン時に縮小されたメモリの内容が、再度スケールアップした時に再配置されているわけではない（多分）ので、p99 latency は安定しないかも試しましょう 91

Slide 92

Slide 92 text

Aurora の場合のバージョンアップ戦略バージョンポリシーなるものが公開されています https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/Aurora.VersionP olicy.html 迷ったら最新版 or 一個前 or LTS で OK だと思います基本的に、バージョンダウンは出来ない・やらないものと思ったほうがいいですバージョンアップのテストには、clone を使ったり、 B/G deploy で作ったクラスタに読み込みワークロードを流すのもよいです 92

Slide 93

Slide 93 text

セオリーから外れないデータベースの失敗はダメージがでかいので、手堅い方に倒す DB のやらなさそうな事をムリにさせないようにするなんとなく正規化を崩さない画像を RDB に入れないログを何でも全部 RDB に入れないマイナーな機能に注意する "日本語のカラム名にしてみました" は、マイナーなバグを踏みそうですよね「機能として存在する」ことと「実運用に耐えている」ことは別物 93

Slide 94

Slide 94 text

Q&A 94

Slide 95

Slide 95 text

参考文献（書籍）この資料を書いた人はこの本にお世話になっています: 『MySQL徹底入門第4版』『データ指向アプリケーションデザイン』『入門監視』 95

Slide 96

Slide 96 text

参考文献（Web ページ）この資料を書いた人はこのページにお世話になっています: MySQL 8.0 リファレンスマニュアル MySQL道普請便り記事一覧 | gihyo.jp 漢のコンピュータ道 yoku0825 さんの speakerdeck Percona Database Performance Blog USE THE INDEX, LUKE! Hack MySQL 96