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

Transcript

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

2. ＼こんに ちは／ 1/76

3. phpcon 2018 2/76

4. 楽しんで ますか？ 3/76

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

6. PHPの話 が 5/76

7. 出てきま 6/76

8. せん 7/76

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

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

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

    α 10/76

12. 我々の生活の中でのMySQL #とは グローバル変数のすごいやつ 異論は認める の 保管 と 取り出し のた めの

    アプリケーション あるいは、SQLというDSLで書けるフレームワーク ‐ 本来の用途とズレたことをすると苦戦するのは世の常 ex.) フォークでラーメンを食べる（できないとは言わないけど効率 的ではない ‐ ex.) ブルドーザーでプリンをすくう（できないとは言わないけど略 ‐ ex.) Twitterアプリでピザを注文する（できないとは言略 ‐ ex.) PHPerが〇〇〇〇で開発する（で略 ‐ ただしトーストは焼ける 11/76

13. 我々の生活の中でのMySQL #とは グローバル変数のすごいやつ 異論は認める の 保管 と 取り出し のた めの

    アプリケーション あるいは、SQLというDSLで書けるフレームワーク ‐ 本来の用途とズレたことをすると苦戦するのは世の常 ex.) フォークでラーメンを食べる（できないとは言わないけど効率 的ではない ‐ ex.) ブルドーザーでプリンをすくう（できないとは言わないけど略 ‐ ex.) Twitterアプリでピザを注文する（できないとは言略 ‐ ex.) PHPerが〇〇〇〇で開発する（で略 ‐ ただしトーストは焼ける 12/76

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

15. ＼こんにちは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter:

    @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 14/76

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

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

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

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

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

21. データの永続化 コミットの成功応答を遅延させることで「成功=必ず残って いる」を表現する “syslog.conf 先頭 マイナス” とかわかる人いますかね… :D ‐ たまに消えてもいい(=

    アクセスログの出力、とかと同じ程 度の信頼性で良い）なら、これを削るオプションがMySQL にはある… innodb_flush_log_at_trx_commit <> 1 とか sync_binlog <> 1 とか ‐ 無茶苦茶速くなる ‐ 20/76

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

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

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

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

    ユーザー全員に100コインプレゼントするバッチを途中で止め た時に何が起きるのか ‐ 24/76

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

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

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

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

    ‐ 28/76

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

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

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

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

34. 分離/一貫性の保証（その1） 結果セットを読み込んでいる最中は当該の範囲に対する更新 をブロックする たとえば transaction_isolation = SERIALIZABLE はそんな動きにな る ‐

    MyISAMという古いストレージエンジンもテーブル単位でロックする ことでこれを防ぐ ‐ こっちの方法なら排他ロックを使って自前で実装できるかも知れない ‐ 33/76

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

    たとえばPostgreSQLは追記型アーキテクチャーなので、新しく追加 した側の行にそれを持たせればいい ‐ 34/76

36. 分離/一貫性の保証 READ-COMMITTED トランザクションの中の、個々の ステートメント が開始された時点 でコミット済みの行を読み込む ‐ REPEATABLE-READ トランザクション が開始された時点でコミット済みの行を読み込む

    ‐ トランザクションの先頭で重い集計クエリーを流している間に更新が あっても、その間の更新を後続の集計クエリーは読まずに済む ‐ 論理バックアップでも使われる ‐ 35/76

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

38. 分離/一貫性の保証 yoku0825.age = 0x24 START TRANSACTION; START TRANSACTION; UPDATE yoku0825

    SET age = age + 1; COMMIT; was: yoku0825.age = 0x23 37/76

39. 分離/一貫性の保証 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

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

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

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

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

    ‐ 42/76

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

    しかも「永続性」「原子性」「分離性/一貫性」を保ちつつ ‐ 43/76

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

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

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

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

    訳にもいかない ‐ I/O効率、キャッシュ効率、局所性… ‐ 圧縮、暗号化とかまで考え出すと…？ ‐ 47/76

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

    ができる 48/76

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

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

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

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

    ‐ 52/76

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

    よ！ ‐ オプションなしの `mysqldump` が一貫性のあるバック アップを取る 53/76

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

    よ！ ‐ オプションなしの `mysqldump` が一貫性のあるバック アップを取る 54/76

56. 並列処理！？ 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

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

    よ！ ‐ オプションなしの `mysqldump` が一貫性のあるバック アップを取る 56/76

58. レプリケーションスレーブの整合性 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

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

60. やんなくていいのにMySQLがやってくれちゃうこと 暗黙のキャスト 文字列を「数値として解釈できるところまで数値として解釈して」数 値にキャストする 0x24 は数値型のまま36だけど、 '0x24' （文字列）は数値型でゼロ 文字列の '123AF96'

    は数値の 123 これはキャストなのか？ ‐ 勝手にパスワードを365日でEXPIREする ⇒ もうしないです よ？ :D 59/76

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

62. ところで 61/76

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

64. ( ﾟдﾟ) 63/76

65. (ﾟдﾟ) 64/76

66. (ﾟдﾟ ) 65/76

67. やんなくていいのにMySQLがやってくれちゃうこと 暗黙のキャスト 文字列を「数値として解釈できるところまで数値として解釈して」数 値にキャストする 0x24 は数値型のまま36だけど、 '0x24' （文字列）は数値型でゼロ 文字列の '123AF96'

    は数値の 123 これはキャストなのか？ ‐ 勝手にパスワードを365日でEXPIREする ⇒ もうしないです よ？ :D 66/76

68. やんなくていいのにMySQLがやってくれちゃうこと 暗黙のキャスト 文字列を「数値として解釈できるところまで数値として解釈して」数 値にキャストする 0x24 は数値型のまま36だけど、 '0x24' （文字列）は数値型でゼロ 文字列の '123AF96'

    は数値の 123 これはキャストなのか？ ‐ 勝手にパスワードを365日でEXPIREする ⇒ もうしないです よ？ :D 67/76

69. (ﾟ∀ﾟ)人(ﾟ∀ﾟ)ﾅｶｰﾏ $ php -r 'print (int) "0x24" . "\n";' 0

    $ php -r 'print (int) "123AF96" . "\n";' 123 68/76

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

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

    -e 'puts "123AF96".to_i' 123 70/76

72. ( ﾟдﾟ) あらまじめ $ python -c 'print int("123AF96")' Traceback (most

    recent call last): File "<string>", line 1, in <module> ValueError: invalid literal for int() with base 10: '123AF96' $ lua -e 'print(tonumber("123AF96"))' nil 71/76

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

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

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

    よ！ ‐ 74/76

76. 用法・用量を守っ て（？）楽しい MySQLライフを！ 75/76

77. Any Questions and/or Suggestions? 76/76