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