Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQLとPostgreSQLと主キー

hmatsu47
December 07, 2020

 MySQLとPostgreSQLと主キー

第 19 回 PostgreSQL アンカンファレンス@オンライン 2020/12/07

hmatsu47

December 07, 2020
Tweet

More Decks by hmatsu47

Other Decks in Technology

Transcript

  1. 自己紹介(前回とまあまあ同じ) 松久裕保(@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
  2. 「データベースを遅くするための8つの方法」 • koduki さんが Zenn に掲載された記事 https://zenn.dev/koduki/articles/d3e8984f420b370681f9 • とりあげるのは「シーケンスナンバーを PK

    にする」  →PostgreSQL のシーケンス(を元にした serial)、MySQL の   AUTO_INCREMENT を主キーに使うべきではない、という話 7
  3. というわけで、PostgreSQL も巻き込んで実験! • 中小規模のシステムを想定  →DB が 1 台(または書き込みノード 1 台のクラスタ)

    • 行挿入が瞬間的に遅くなったり波が生じたりしないか?  →シーケンス生成のボトルネック確認 • 行数が増えるにつれて行挿入が遅くならないか?  →Right Growing Index 問題の確認 13
  4. テストの内容(1/2) • 120 万行のデータを 24 並列で挿入(合計 2,880 万行) • 主キーとして以下の

    3 種類を使い、挿入 100 万行あたり の所要時間の推移を比較 ◦ ①serial / AUTO_INCREMENT ◦ ②UUIDv4(文字列) ◦ ③UUIDv4(uuid 型)※PostgreSQL のみ 14
  5. テストの内容(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
  6. ・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
  7. ・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
  8. ここまでの結果まとめ • serial / AUTO_INCREMENT のほうが高速 ◦ 速度低下や波も小さい ◦ PostgreSQL

    の uuid 型は、少し遅くて行数が増えると波が… ◦ この規模なら serial / AUTO_INCREMENT のほうが扱いやすい • MySQL の UUIDv4(文字列)が意外と健闘 ◦ 前評判ほど遅くない? ◦ 行数が増えると PostgreSQL(文字列)より速い? 21
  9. 準備中に気付いたこと • UUIDv4 を含むテストデータの生成が微妙に遅い ◦ Amazon(AWS) EC2 c5.large (Amazon Linux

    2) を使用 ◦ CPU も使い切らないし I/O も頭打ちにならないのに… ◦ シーケンスの生成より遅い! • 乱数生成時のエントロピー不足の問題? ◦ 仮想マシンだし、他の用途に使っていないので、エントロピーが なかなか溜まらないのかも…? 22
  10. 予想と少し違ったので、追試 • 1 行あたりの容量を増やしてみる ◦ varchar(100) の列を追加 ◦ デフォルトで 100

    文字入れておく • データを挿入する SQL 文は同じ ◦ ①②③ともそのまま 23
  11. ・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
  12. 追試の結果まとめ • やっぱり serial / AUTO_INCREMENT のほうが高速 ◦ この規模なら serial

    / AUTO_INCREMENT のほうが… (2 回目) • MySQL の UUIDv4(文字列)は当初想像どおりの遅さ ◦ 挿入時のページ分割が辛い https://qiita.com/SH2/items/654d89759e7e39d999b5 27
  13. 全体のまとめ(2/2) • 性能に影響を与える要素は意外とたくさんある ◦ たとえば UUIDv4 の生成速度 ▪ 環境によってはシーケンスより遅くなることも ▪

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