案外よくできているMySQLというアプリケーション

by yoku0825

Slide 1

Slide 1 text

案外よくできているMySQLというアプリケーション思ったよりはよくできている 2018/12/15 yoku0825 phpcon 2018

Slide 2

Slide 2 text

＼こんにちは／ 1/76

Slide 3

Slide 3 text

phpcon 2018 2/76

Slide 4

Slide 4 text

楽しんでますか？ 3/76

Slide 5

Slide 5 text

なんとこのセッション 4/76

Slide 6

Slide 6 text

PHPの話が 5/76

Slide 7

Slide 7 text

出てきま 6/76

Slide 8

Slide 8 text

せん 7/76

Slide 9

Slide 9 text

あっでも、1か所だけ PHPの気配がチラっと出てきますさがしてみてください XD 8/76

Slide 10

Slide 10 text

MySQL #とは世界で最も普及している、オープンソースデータベース https://www.mysql.com/jp/ 9/76

Slide 11

Slide 11 text

我々の生活の中でのデータストア永続化可能なサーバーまたいでアクセスできる排他・共有ロック機能付きでちょっと集計もできるグローバル変数のすごいやつ異論は認める from MySQLおじさんの逆襲 + α 10/76

Slide 12

Slide 12 text

我々の生活の中でのMySQL #とはグローバル変数のすごいやつ異論は認めるの保管と取り出しのためのアプリケーションあるいは、SQLというDSLで書けるフレームワーク ‐ 本来の用途とズレたことをすると苦戦するのは世の常 ex.) フォークでラーメンを食べる（できないとは言わないけど効率的ではない ‐ ex.) ブルドーザーでプリンをすくう（できないとは言わないけど略 ‐ ex.) Twitterアプリでピザを注文する（できないとは言略 ‐ ex.) PHPerが〇〇〇〇で開発する（で略 ‐ ただしトーストは焼ける 11/76

Slide 13

Slide 13 text

Slide 14

Slide 14 text

このトークで知ってほしいこと知らないうちにMySQLがやってくれていることを知りやってくれることは任せてやってくれないところはアプリケーションでカバーするあるいは他のミドルウェアを導入するという考え方を知ってほしい合言葉は「コイツ、案外よくできてる」「思ったよりは」 13/76

Slide 15

Slide 15 text

＼こんにちは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 14/76

Slide 16

Slide 16 text

このトークで知ってほしいこと(again) 知らないうちにMySQLがやってくれていることを知りやってくれることは任せてやってくれないところはアプリケーションでカバーするあるいは他のミドルウェアを導入するという考え方を知ってほしい合言葉は「コイツ、案外よくできてる」「思ったよりは」 15/76

Slide 17

Slide 17 text

(知らないうちに) MySQLがやってくれていることデータの永続化操作の原子化分離/一貫性の保証インデックスのメンテナンス物理的なI/Oの削減 16/76

Slide 18

Slide 18 text

(知らないうちに) MySQLがやってくれていることデータの永続化操作の原子化分離/一貫性の保証インデックスのメンテナンス物理的なI/Oの削減 17/76

Slide 19

Slide 19 text

データの永続化コミットされたデータは（ストレージが吹っ飛ばない限り）クラッシュしても起動してくれば必ずそこに残っている「基本残るけどタイミングによっては消えちゃうかも」みたいなのは（少なくともここで言っている）永続化ではない ‐ 18/76

Slide 20

Slide 20 text

データの永続化イメージです（実際の処理とは違います） 19/76

Slide 21

Slide 21 text

データの永続化コミットの成功応答を遅延させることで「成功=必ず残っている」を表現する “syslog.conf 先頭マイナス” とかわかる人いますかね… :D ‐ たまに消えてもいい(= アクセスログの出力、とかと同じ程度の信頼性で良い）なら、これを削るオプションがMySQL にはある… innodb_flush_log_at_trx_commit <> 1 とか sync_binlog <> 1 とか ‐ 無茶苦茶速くなる ‐ 20/76

Slide 22

Slide 22 text

＿人人人人人人人人人＿＞案外よくできてる＜￣Y^Y^Y^Y^Y^Y^ ￣ 21/76

Slide 23

Slide 23 text

＿人人人人人人＿＞思ったよりは＜￣Y^Y^Y^Y^Y￣ 22/76

Slide 24

Slide 24 text

(知らないうちに) MySQLがやってくれていることデータの永続化操作の原子化分離/一貫性の保証インデックスのメンテナンス物理的なI/Oの削減 23/76

Slide 25

Slide 25 text

操作の原子化トランザクションといえば「2つ以上のステートメントをあたかも1つの操作として…」と表現されることが多いけれど ex.) アイテムボックスに追加する処理とプレゼントボックスから取り除く処理を1つのトランザクションにまとめる ‐ 実際は1ステートメントの処理内容でも原子性を意識しないといけないわけで ex.) ユーザー全員に100コインプレゼントするバッチを途中で止めた時に何が起きるのか ‐ 24/76

Slide 26

Slide 26 text

操作の原子化イメージです（実際の処理とは違います） 25/76

Slide 27

Slide 27 text

操作の原子化書き込みに失敗してエラー応答を返すなら、書き込み途中のものが見えてはいけない（正しく破棄されなくてはいけない）とはいえ1システムコールで1カラム1GBのデータを書くことはできない 100万行ぶんのデータを更新することもできない ‐ 26/76

Slide 28

Slide 28 text

操作の原子化イメージです（実際の処理とは違います）本当はシャドウページングは使ってない ‐ 27/76

Slide 29

Slide 29 text

操作の原子化実際にはログ先行書き込みで永続化の原始性を取りつつ、 flush_listを使ってバッファプールからibdファイルへの反映をしているこれが楽にアプリケーションで実装できないから、複数DB の苦しみは世界にあふれているし、ベストプラクティスとして「DBはスケールアウトさせるよりスケールアップさせる方が圧倒的に楽」というのがあるとはいえ「この2回書く」を愚直にやると案外2相トランザクションっぽいものは表現できる ‐ 28/76

Slide 30

Slide 30 text

＿人人人人人人人人人＿＞案外よくできてる＜￣Y^Y^Y^Y^Y^Y^ ￣ 29/76

Slide 31

Slide 31 text

＿人人人人人人＿＞思ったよりは＜￣Y^Y^Y^Y^Y￣ 30/76

Slide 32

Slide 32 text

(知らないうちに) MySQLがやってくれていることデータの永続化操作の原子化分離/一貫性の保証インデックスのメンテナンス物理的なI/Oの削減 31/76

Slide 33

Slide 33 text

分離/一貫性の保証たとえば100行の結果セットがあって先頭から50行読み込んだところで 51行目を更新するトランザクションがコミットされたとしたら何が起こるのか 32/76

Slide 34

Slide 34 text

分離/一貫性の保証（その1）結果セットを読み込んでいる最中は当該の範囲に対する更新をブロックするたとえば transaction_isolation = SERIALIZABLE はそんな動きになる ‐ MyISAMという古いストレージエンジンもテーブル単位でロックすることでこれを防ぐ ‐ こっちの方法なら排他ロックを使って自前で実装できるかも知れない ‐ 33/76

Slide 35

Slide 35 text

分離/一貫性の保証（その2）更新前の値を捨てずに取っておいて、必要なら読み取り側が更新前の値を参照しにいく参照側の小さなトランザクションに「迂回」させることで全体としてのスループットを下げないようにする ‐ 「現在の行」が「自分の1つ前のバージョン」に対する参照を持つ ‐ MySQLのInnoDBはインプレースなアーキテクチャーなので、元のデータをUNDO領域にコピーしているたとえばPostgreSQLは追記型アーキテクチャーなので、新しく追加した側の行にそれを持たせればいい ‐ 34/76

Slide 36

Slide 36 text

分離/一貫性の保証 READ-COMMITTED トランザクションの中の、個々のステートメントが開始された時点でコミット済みの行を読み込む ‐ REPEATABLE-READ トランザクションが開始された時点でコミット済みの行を読み込む ‐ トランザクションの先頭で重い集計クエリーを流している間に更新があっても、その間の更新を後続の集計クエリーは読まずに済む ‐ 論理バックアップでも使われる ‐ 35/76

Slide 37

Slide 37 text

分離/一貫性の保証 yoku0825.age = 0x23 START TRANSACTION; START TRANSACTION; 36/76

Slide 38

Slide 38 text

分離/一貫性の保証 yoku0825.age = 0x24 START TRANSACTION; START TRANSACTION; UPDATE yoku0825 SET age = age + 1; COMMIT; was: yoku0825.age = 0x23 37/76

Slide 39

Slide 39 text

分離/一貫性の保証 yoku0825.age = 0x24 START TRANSACTION; START TRANSACTION; UPDATE yoku0825 SET age = age + 1; COMMIT; SELECT age FROM yoku0825; READ-COMMITTED: 0x24 OR REPEATABLE-READ: 0x23 was: yoku0825.age = 0x23 38/76

Slide 40

Slide 40 text

＿人人人人人人人人人＿＞案外よくできてる＜￣Y^Y^Y^Y^Y^Y^ ￣ 39/76

Slide 41

Slide 41 text

＿人人人人人人＿＞思ったよりは＜￣Y^Y^Y^Y^Y￣ 40/76

Slide 42

Slide 42 text

(知らないうちに) MySQLがやってくれていることデータの永続化操作の原子化分離/一貫性の保証インデックスのメンテナンス物理的なI/Oの削減 41/76

Slide 43

Slide 43 text

インデックスのメンテナンスインデックスとは「構造化されたデータの部分集合」辞書とその索引に例えた時に収録されいてる単語が増減したら索引も増やしたり減らしたり ‐ 収録されている単語は同じでも、辞書本文の増補や改訂でページが増減したら、索引もそれに合わせてページ番号を振りなおさないといけない ‐ しかも「永続性」「原子性」「分離性/一貫性」を保ちつつ ‐ 42/76

Slide 44

Slide 44 text

インデックスのメンテナンス InnoDBのクラスターインデックスデータそのものもインデックスと同じ構造で論理値で管理するデータが既存のページに収まらなくなってページ移動が発生してもセカンダリーインデックスは影響を受けない ‐ InnoDBのチェンジバッファ非ユニークなインデックスはアイドル時かSELECTアクセスされた時に遅延マージすることでインデックスの更新のオーバーヘッドを分散するしかも「永続性」「原子性」「分離性/一貫性」を保ちつつ ‐ 43/76

Slide 45

Slide 45 text

＿人人人人人人人人人＿＞案外よくできてる＜￣Y^Y^Y^Y^Y^Y^ ￣ 44/76

Slide 46

Slide 46 text

＿人人人人人人＿＞思ったよりは＜￣Y^Y^Y^Y^Y￣ 45/76

Slide 47

Slide 47 text

(知らないうちに) MySQLがやってくれていることデータの永続化操作の原子化トランザクションの分離/一貫性の保証インデックスのメンテナンス物理的なI/Oの削減 46/76

Slide 48

Slide 48 text

物理的なI/Oの削減ここまでのものを愚直に再発明したとしたら、1行更新するだけで一体何回のfsyncが必要になるんだ？ fsyncはストレージI/Oの中でも最も遅い類 ‐ 特にハードディスクでは致命的に遅い ‐ 100GBのテーブルから1行探すために、100GBをスキャンする訳にはいかない 1GBのユニークインデックスから1行探すために1GBをスキャンする訳にもいかない ‐ I/O効率、キャッシュ効率、局所性… ‐ 圧縮、暗号化とかまで考え出すと…？ ‐ 47/76

Slide 49

Slide 49 text

物理的なI/Oの削減 DBMSは色んなバッファ機構をOSのものとは別にプロセス内部に持っているそれはOSに依存しないバッファ管理のためだったりインデックスのチェンジバッファとか、InnoDBのログバッファとか、ダブルライトバッファとか、キャッシュのためじゃないバッファもある ‐ OSにかかる負荷を減らすためだったり ‐ 我々はRDBMSという巨人の肩に乗っかって仕事をすることができる 48/76

Slide 50

Slide 50 text

＿人人人人人人人人人＿＞案外よくできてる＜￣Y^Y^Y^Y^Y^Y^ ￣ 49/76

Slide 51

Slide 51 text

＿人人人人人人＿＞思ったよりは＜￣Y^Y^Y^Y^Y￣ 50/76

Slide 52

Slide 52 text

(知らないうちに) MySQLがやってくれていることデータの永続化操作の原子化分離/一貫性の保証インデックスのメンテナンス物理的なI/Oの削減 51/76

Slide 53

Slide 53 text

(知らないうちに) MySQLがやってくれていること自分で再発明するとなるとかなり大変やってくれるとはいえ要らない機能があるなら、それを持たない代わりに高速化された何かを選ぶことができるトランザクションの分離性は要らない/アプリケーション側で担保するからredisを使う ‐ redisを使うならトランザクションの分離性は捨てるか別のところで担保しないといけない ‐ 52/76

Slide 54

Slide 54 text

知ってか知らずかMySQLがやってくれない/なかったから自前で用意しないといけない/なかったもの並列処理フラッシュバック（データのバージョニング）論理的なI/Oの削減（一応やってはくれるんだけど上手くはないというか） N+1問題の畳み込みとか ‐ 分散トランザクションレプリケーションスレーブとの一貫性/整合性は担保してくれないよ！ ‐ オプションなしの `mysqldump` が一貫性のあるバックアップを取る 53/76

Slide 55

Slide 55 text

Slide 56

Slide 56 text

並列処理！？ As of MySQL 8.0.14, InnoDB supports parallel index reads, which improves performance of non- locking SELECT COUNT(*) FROM tbl_name queries and CHECK TABLE operations. MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 55/76

Slide 57

Slide 57 text

Slide 58

Slide 58 text

レプリケーションスレーブの整合性 Note that some underlying consensus and Paxos related messages are missing from this picture for the sake of clarity. MySQL :: MySQL 8.0 Reference Manual :: 18.1.1.2 Group Replication 57/76

Slide 59

Slide 59 text

マイエスキューエルは進化しているやってくれることが増えているそれはつまり自分でやらなきゃいけなかったことが減っている迷ったら最新版でいいと思うの 58/76

Slide 60

Slide 60 text

やんなくていいのにMySQLがやってくれちゃうこと暗黙のキャスト文字列を「数値として解釈できるところまで数値として解釈して」数値にキャストする 0x24 は数値型のまま36だけど、 '0x24' （文字列）は数値型でゼロ文字列の '123AF96' は数値の 123 これはキャストなのか？ ‐ 勝手にパスワードを365日でEXPIREする ⇒ もうしないですよ？ :D 59/76

Slide 61

Slide 61 text

まとめ知らないうちにMySQLがやってくれていることを知りやってくれることは任せてやってくれないところはアプリケーションでカバーするあるいは他のミドルウェアを導入するという考え方を知ってほしい合言葉は「コイツ、案外よくできてる」「思ったよりは」 60/76

Slide 62

Slide 62 text

ところで 61/76

Slide 63

Slide 63 text

PHPの気配、見つかりましたか？ 62/76

Slide 64

Slide 64 text

( ﾟдﾟ) 63/76

Slide 65

Slide 65 text

(ﾟдﾟ) 64/76

Slide 66

Slide 66 text

(ﾟдﾟ ) 65/76

Slide 67

Slide 67 text

Slide 68

Slide 68 text

Slide 69

Slide 69 text

(ﾟ∀ﾟ)人(ﾟ∀ﾟ)ﾅｶｰﾏ $ php -r 'print (int) "0x24" . "\n";' 0 $ php -r 'print (int) "123AF96" . "\n";' 123 68/76

Slide 70

Slide 70 text

つまり MySQL = PHP 感じ方には個人差があります 69/76

Slide 71

Slide 71 text

というかコイツらも (ﾟ∀ﾟ)人(ﾟ∀ﾟ)ﾅｶｰﾏ $ perl -E 'say int("123AF96")' 123 $ ruby -e 'puts "123AF96".to_i' 123 70/76

Slide 72

Slide 72 text

( ﾟдﾟ) あらまじめ $ python -c 'print int("123AF96")' Traceback (most recent call last): File "", line 1, in ValueError: invalid literal for int() with base 10: '123AF96' $ lua -e 'print(tonumber("123AF96"))' nil 71/76

Slide 73

Slide 73 text

このトークで知ってほしかったこと知らないうちにMySQLがやってくれていることを知りやってくれることは任せてやってくれないところはアプリケーションでカバーするあるいは他のミドルウェアを導入するという考え方を知ってほしい合言葉は「コイツ、案外よくできてる」「思ったよりは」 72/76

Slide 74

Slide 74 text

(知らないうちに) MySQLがやってくれていることデータの永続化操作の原子化分離/一貫性の保証インデックスのメンテナンス I/O量の削減 73/76