Save 37% off PRO during our Black Friday Sale! »

LTのようでLTではない話(誰か教えて)

B1dca90d4b3ffd2ccd918774e1ba170d?s=47 hmatsu47
PRO
December 11, 2020

 LTのようでLTではない話(誰か教えて)

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

B1dca90d4b3ffd2ccd918774e1ba170d?s=128

hmatsu47
PRO

December 11, 2020
Tweet

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. 元ネタはこれ↓ • 「データベースを遅くするための8つの方法」 ◦ 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