MySQLとPostgreSQLと主キー

Transcript

1. MySQL と PostgreSQL と主キー （良いタイトルが思い浮かばなかったので諦めた・その②） 第 19 回 PostgreSQL アンカンファレンス@オンライン　

   2020/12/07 まつひさ（hmatsu47）

2. 自己紹介（前回とまあまあ同じ） 松久裕保（@hmatsu47） https://qiita.com/hmatsu47 名古屋で Web インフラのお守り係をしています MySQL 8.0 の薄い本を作って配っています ◦

   Qiitaの記事： https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ◦ GitHub リポジトリの他、印刷版を勉強会などで無料配布していました ◦ 新型コロナウイルスの関係でオフライン勉強会ができなくなったので、 現在は BOOTH でも配布しています（100円＋送料）8.0.22対応版配布中 https://booth.pm/ja/items/2524481 2

3. 今日は、 • PostgreSQL アンカンファレンスの登壇枠がガラ空き だったので、予定を変更して登壇枠へ（4 回目） • 前回に続いて今回もネタは他人依存 ◦ ただのオーディエンスのつもりだったので…

   ◦ 尺も（たぶん）15 分くらいしかありません 3

4. 今日いちばん伝えたいこと • 物事を二値（二項対立）で評価・判断しないほうが良い ◦ （絶対に）正しい vs 間違い ◦ （絶対に）速い vs

   遅い … 4

5. ところでみなさん、 • 主キーは好きですか？ ◦ ©となりのふかまちさん https://www.slideshare.net/hidemifukamachi/sql-require-primarykey 5

6. ところでみなさん、 • 主キーは好きですか？ ◦ ©となりのふかまちさん https://www.slideshare.net/hidemifukamachi/sql-require-primarykey …気を取り直して 6

7. 「データベースを遅くするための８つの方法」 • koduki さんが Zenn に掲載された記事 https://zenn.dev/koduki/articles/d3e8984f420b370681f9 • とりあげるのは「シーケンスナンバーを PK

   にする」 　→PostgreSQL のシーケンス（を元にした serial）、MySQL の 　　AUTO_INCREMENT を主キーに使うべきではない、という話 7

8. なぜダメなのか？ • シーケンス生成がボトルネックになる 　→生成よりも要求のペースが速いと「待ち」が発生する • 並列処理の場合に同期処理が必要になる 　→複数の書き込みノードで共通の連番を振るのは大変 • Right Growing

   Index 　→B 木の右側に更新が集中するので処理効率が悪い 8

9. 回避策（記事で示されたもの） • UUID(v4) を使う 　→値がランダムに振られるので偏らない・競合しない • ナチュラルキーを使う 　→ただし複数列＝複合主キーは結合時にキーとして使いづらい 　　長い文字列も処理効率が悪くなりそう 9

10. 【参考】分散 DB の場合①Google Spanner https://cloud.google.com/spanner/docs/whitepapers/optimizing-schema-design#anti-pattern_sequences →単調増加する値を主キーにするとホットスポットができやすい 10

11. 【参考】分散 DB の場合②Table Store (Alibaba Cloud) https://speakerdeck.com/hmatsu47/alibaba-cloudfalsetable-storedeotoinkurimentoji-neng-woshi-tutemita →自動インクリメント値を主キーの 2 列目以降に設定可（非連番）

    11

12. ところで、 • MySQL（InnoDB）ではクラスタインデックスを採用 　→主キーを昇順に振らないと挿入が遅くなるという話がある 12

13. というわけで、PostgreSQL も巻き込んで実験！ • 中小規模のシステムを想定 　→DB が 1 台（または書き込みノード 1 台のクラスタ）

    • 行挿入が瞬間的に遅くなったり波が生じたりしないか？ 　→シーケンス生成のボトルネック確認 • 行数が増えるにつれて行挿入が遅くならないか？ 　→Right Growing Index 問題の確認 13

14. テストの内容（1/2） • 120 万行のデータを 24 並列で挿入（合計 2,880 万行） • 主キーとして以下の

    3 種類を使い、挿入 100 万行あたり の所要時間の推移を比較 ◦ ①serial / AUTO_INCREMENT ◦ ②UUIDv4（文字列） ◦ ③UUIDv4（uuid 型）※PostgreSQL のみ 14

15. テストの内容（2/2） • テスト環境は以下のとおり ◦ Amazon(AWS) RDS（MySQL 5.7.31 / PostgreSQL 12.4

    R1） ▪ Dedicated Log Writer の性能問題のため MySQL 8.0.21 は使わず ◦ db.m5.xlarge（4vCPU・16GiB Mem） ◦ Single AZ ◦ デフォルトパラメータグループ ▪ ただし MySQL はバイナリログが書き出されないように調整 15

16. ・serial（PostgreSQL） CREATE TABLE btree_test2 (id serial PRIMARY KEY NOT NULL,

    v1 integer, t1 timestamp(6) NOT NULL DEFAULT current_timestamp); ・UUIDv4（文字列／PostgreSQL） CREATE TABLE btree_test3 (id char(36) PRIMARY KEY NOT NULL, v1 integer, t1 timestamp(6) NOT NULL DEFAULT current_timestamp); ・UUIDv4（uuid 型／PostgreSQL） CREATE TABLE btree_test4 (id uuid PRIMARY KEY NOT NULL, v1 integer, t1 timestamp(6) NOT NULL DEFAULT current_timestamp); ・AUTO_INCREMENT（MySQL） CREATE TABLE btree_test2 (id integer PRIMARY KEY NOT NULL AUTO_INCREMENT, v1 integer, t1 timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)); ・UUIDv4（文字列／MySQL） CREATE TABLE btree_test3 (id char(36) PRIMARY KEY NOT NULL, v1 integer, t1 timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)); テスト用のテーブル 16

17. ・serial / AUTO_INCREMENT INSERT INTO btree_test2 (v1) VALUES (10), (11),

    (12), (13), (14), (15), (16), (17), (18), (19); ※これを 12 万行 1 ファイル × 24 種類用意 ・UUIDv4（文字列） INSERT INTO btree_test3 (id, v1) VALUES ('23ce3e9f-16de-4e5c-a293-6104ca45b47a', 10), ('ccf75ce2-49e9-4585-8561-9c55acf9e954', 11), ('493ec339-a21c-4ae9-be52-8c313758dd77', 12), ('aee11198-7767-42a6-9363-a16556d936b7', 13), ('7133eb96-fe19-4d45-b2f7-62ddc719234d', 14), ('cc1ae2ee-d99e-4102-9288-877eaae3bddc', 15), ('67fc7a86-b3c8-46fa-a237-d6479ee693b5', 16), ('c2e663e1-0168-4084-97fb-97b7d4575cf6', 17), ('602f2007-76f4-45d7-8a0f-e51430943afe', 18), ('f2b1ce06-db4a-48c4-b089-94198d3ddbe9', 19); ※これを（異なる UUID で）12 万行 1 ファイル × 24 種類用意 ・UUIDv4（uuid 型／PostgreSQL） INSERT INTO btree_test4 (id, v1) VALUES ('23ce3e9f-16de-4e5c-a293-6104ca45b47a'::uuid, 10), ('ccf75ce2-49e9-4585-8561-9c55acf9e954'::uuid, 11), ('493ec339-a21c-4ae9-be52-8c313758dd77'::uuid, 12), ('aee11198-7767-42a6-9363-a16556d936b7'::uuid, 13), ('7133eb96-fe19-4d45-b2f7-62ddc719234d'::uuid, 14), ('cc1ae2ee-d99e-4102-9288-877eaae3bddc'::uuid, 15), ('67fc7a86-b3c8-46fa-a237-d6479ee693b5'::uuid, 16), ('c2e663e1-0168-4084-97fb-97b7d4575cf6'::uuid, 17), ('602f2007-76f4-45d7-8a0f-e51430943afe'::uuid, 18), ('f2b1ce06-db4a-48c4-b089-94198d3ddbe9'::uuid, 19); ※同上 テスト用の SQL 文（一部分のみ抜粋） 17

18. 注意 • 記事の内容への賛否を示すものではない 　→記事に書かれた内容は、セオリーとしては正しい • ある特定の条件下でどうなるかを確かめるもの 　→使う RDB(MS)・台数・データ量など（今回は中小規模） • 結果を鵜呑みにしない

    　→条件が変われば結果も変わる 18

19. 実行結果① serial / AUTO_INCREMENT →挿入 100 万行あたりの所要時間はいずれも大きな変化・波なし 19

20. 実行結果②・③ UUIDv4（文字列・uuid 型） →文字列の場合は速度低下や波が大きい 20

21. ここまでの結果まとめ • serial / AUTO_INCREMENT のほうが高速 ◦ 速度低下や波も小さい ◦ PostgreSQL

    の uuid 型は、少し遅くて行数が増えると波が… ◦ この規模なら serial / AUTO_INCREMENT のほうが扱いやすい • MySQL の UUIDv4（文字列）が意外と健闘 ◦ 前評判ほど遅くない？ ◦ 行数が増えると PostgreSQL（文字列）より速い？ 21

22. 準備中に気付いたこと • UUIDv4 を含むテストデータの生成が微妙に遅い ◦ Amazon(AWS) EC2 c5.large (Amazon Linux

    2) を使用 ◦ CPU も使い切らないし I/O も頭打ちにならないのに… ◦ シーケンスの生成より遅い！ • 乱数生成時のエントロピー不足の問題？ ◦ 仮想マシンだし、他の用途に使っていないので、エントロピーが なかなか溜まらないのかも…？ 22

23. 予想と少し違ったので、追試 • 1 行あたりの容量を増やしてみる ◦ varchar(100) の列を追加 ◦ デフォルトで 100

    文字入れておく • データを挿入する SQL 文は同じ ◦ ①②③ともそのまま 23

24. ・serial（PostgreSQL） CREATE TABLE btree_test2 (id serial PRIMARY KEY NOT NULL,

    v1 integer, t1 timestamp(6) NOT NULL DEFAULT current_timestamp, s1 varchar(100) DEFAULT '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); ・UUIDv4（文字列／PostgreSQL） CREATE TABLE btree_test3 (id char(36) PRIMARY KEY NOT NULL, v1 integer, t1 timestamp(6) NOT NULL DEFAULT current_timestamp, s1 varchar(100) DEFAULT '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); ・UUIDv4（uuid 型／PostgreSQL） CREATE TABLE btree_test4 (id uuid PRIMARY KEY NOT NULL, v1 integer, t1 timestamp(6) NOT NULL DEFAULT current_timestamp, s1 varchar(100) DEFAULT '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); ・AUTO_INCREMENT（MySQL） CREATE TABLE btree_test2 (id integer PRIMARY KEY NOT NULL AUTO_INCREMENT, v1 integer, t1 timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), s1 varchar(100) DEFAULT '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); ・UUIDv4（文字列／MySQL） CREATE TABLE btree_test3 (id char(36) PRIMARY KEY NOT NULL, v1 integer, t1 timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), s1 varchar(100) DEFAULT '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); テスト用のテーブル 24

25. 実行結果①-2 serial / AUTO_INCREMENT →少し遅くなった程度で傾向は①と同じ 25

26. 実行結果②-2・③-2 UUIDv4（文字列・uuid 型） →MySQL が極端に遅くなった 26

27. 追試の結果まとめ • やっぱり serial / AUTO_INCREMENT のほうが高速 ◦ この規模なら serial

    / AUTO_INCREMENT のほうが… （2 回目） • MySQL の UUIDv4（文字列）は当初想像どおりの遅さ ◦ 挿入時のページ分割が辛い https://qiita.com/SH2/items/654d89759e7e39d999b5 27

28. 全体のまとめ（1/2） • 計測は大事 ◦ セオリーや製品固有の特性を知っているのはもちろん大事 ◦ その上で、実際の利用シーンに合わせて試すことが重要 • 他人の計測結果を鵜呑みにしない ◦

    条件が変われば結果も変わる ◦ 自らの利用シーンに合わせて計測する 28

29. 全体のまとめ（2/2） • 性能に影響を与える要素は意外とたくさんある ◦ たとえば UUIDv4 の生成速度 ▪ 環境によってはシーケンスより遅くなることも ▪

    1 台の DB ではなく複数の Web サーバで生成したほうが良いケース もありそう ◦ 一点ばかり注目しない →二値（二項対立）で評価・判断しないほうが良い 29