LTのようでLTではない話（誰か教えて）

MyNA(日本 MySQL ユーザ会) 望年 LT 大会 2020＠オンライン
2020/12/11

Transcript

1. LT のようで LT ではない話 （誰か教えて） MyNA(日本 MySQL ユーザ会) 望年 LT 大会 2020 ＠オンライン　2020/12/11 まつひさ（hmatsu47）
2. 最近、 • PostgreSQL アンカンファレンスの登壇枠が空いている のを見つけて、登壇したりしています ◦ 第 15 回、第 17・18・19 回の計 4 回 • 前回のネタはこれ↓ ◦ MySQL と PostgreSQL と主キー https://speakerdeck.com/hmatsu47/mysqltopostgresqltozhu-ki 2
3. 元ネタはこれ↓ • 「データベースを遅くするための８つの方法」 ◦ koduki さんが Zenn に掲載された記事 https://zenn.dev/koduki/articles/d3e8984f420b370681f9 • とりあげたのは「シーケンスナンバーを PK にする」 　→PostgreSQL のシーケンス、MySQL の AUTO_INCREMENT を主キーに使う 　　べきではない、という話 　　（PostgreSQL を名指しで言ってるわけではないので語弊があるかも） 3
4. 記事で指摘された問題点 • シーケンス生成がボトルネックになる 　→生成よりも要求のペースが速いと「待ち」が発生する • 並列処理の場合に同期処理が必要になる 　→複数の書き込みノードで共通の連番を振るのは大変 • Right Growing Index 　→B 木の右側に更新が集中するので処理効率が悪い 4
5. 実験で確かめてみました • 中小規模のシステムを想定 　→DB が 1 台（または書き込みノード 1 台のクラスタ） • 行挿入が瞬間的に遅くなったり波が生じたりしないか？ 　→シーケンス生成のボトルネック確認 • 行数が増えるにつれて行挿入が遅くならないか？ 　→Right Growing Index 問題の確認 5
6. テストの内容（1/2） • 120 万行のデータを 24 並列で挿入（合計 2,880 万行） • 主キーとして以下の 3 種類を使い、挿入 100 万行あたり の所要時間の推移を比較 ◦ ①serial / AUTO_INCREMENT ◦ ②UUIDv4（文字列） ◦ ③UUIDv4（uuid 型）※PostgreSQL のみ 6
7. テストの内容（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・700GiB SSD） ◦ Single AZ ◦ デフォルトパラメータグループ ▪ ただし MySQL はバイナリログが書き出されないように調整 7
8. ・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'); テスト用のテーブル ※追試で使ったもの 8
9. ・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 文（一部分のみ抜粋） 9
10. ちなみにこれ↓を PostgreSQL ユーザ会界隈に輸出 • 主キーは好きですか？ ◦ ©となりのふかまちさん https://www.slideshare.net/hidemifukamachi/sql-require-primarykey 10
11. 実行結果① serial / AUTO_INCREMENT ※追試分 11
12. 実行結果②・③ UUIDv4（文字列・uuid 型）※追試分 12
13. 途中で、こば（@tzkb）さんからコメントが 13
14. 確かに。（Database Internals より） 14
15. ところで、 • MySQL についての言及がない ◦ AUTO_INCREMENT なら MySQL、のはずなのに →ソースに何か書いてないかな？ 15
16. …よくわからなかった。 16
17. というわけで、 • どなたかご存知でしたら教えてください ◦ MySQL でどうやって Right-Only Appends の対処をして いるのかを… 17
18. おまけ • PostgreSQL ユーザ会界隈との合同イベント・勉強会 しましょう！ 18