" _ " or " % " - Which is faster? (Like predicate in MySQL's sql)
MySQL Advent Calendar 2019 の22日目です。
MySQLのあいまい検索時のLIKEで使える「_」と「%」について調べてみました。
あいまい検索時に使う「_」と「%」
会社でlike 検索した際に「_」と「%」で速度差ってあるのかな？という話になり、
こういうの調べたことある人って世の中に結構いるんじゃない？と調べてみたんですが、なかなか出てこないので自分で調べてみました
検証について
- カラムはとりあえず文字列としました
- セカンダリインデックスの有無とNULLの有無で比較
- NULLABLEなカラムのほうは全体の1割程度NULLにしてみた(根拠は無い)
- 作業環境はdockerに立てたMySQL8.0.18です(HDD上に結構小さいスペックで立ててます)
テーブル用意
CREATE TABLE `like_test` ( `like_test_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'LIKE文の検証ID', `uuid_key_ari_nn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `uuid_key_nashi_nn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `uuid_key_ari_dn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin default NULL, `uuid_key_nashi_dn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin default NULL, PRIMARY KEY (`like_test_id`), KEY `idx_uuid_key_ari_nn` (`uuid_key_ari_nn`), KEY `idx_uuid_key_ari_dn` (`uuid_key_ari_dn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='LIKE文の検証' ;
| カラム名 | カラムの役割 | インデックス有無 | NOT NULL |
|---|---|---|---|
| like_test_id | PK | 〇 | 〇 |
| uuid_key_ari_nn | UUID値をNOT NULLの形で持つ | 〇 | 〇 |
| uuid_key_nashi_nn | UUID値をNOT NULLの形で持つ | × | 〇 |
| uuid_key_ari_dn | UUID値をNULLABLEの形で持つ | 〇 | × |
| uuid_key_nashi_dn | UUID値をNULLABLEの形で持つ | × | × |
データ用意
INSERT INTO `test`.`like_test` (`like_test_id`, `uuid_key_ari_nn`, `uuid_key_nashi_nn`, `uuid_key_ari_dn`, `uuid_key_nashi_dn`) VALUES (null, uuid(), uuid(), uuid(), uuid());
pythonで書いてuuid v4で入れるか？とも考えたけど、とりあえずいーやってことでMySQLのUUID関数使う。
上のINSERTを適当にぶん回したりアレコレして480000件作った
> select count(1) from like_test; +------------+ | count(1) | |------------| | 480000 | +------------+ 1 row in set Time: 0.059s
ちょっとデータをいじって、４つのカラムがすべて同じ値かつテーブルで一意な値を用意した。
> select * from like_test where like_test_id = 14100; +----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+ | like_test_id | uuid_key_ari_nn | uuid_key_nashi_nn | uuid_key_ari_dn | uuid_key_nashi_dn | |----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------| | 14100 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | +----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+ 1 row in set Time: 0.010s
このレコードを使って検証してみる。
検証詳細
- mysqlslapを使う
- 並列数は1、1000回クエリを実行して平均、最小、最大時間を見る
- where句に
uuid_key_ari_nn,uuid_key_nashi_nn,uuid_key_ari_dn,uuid_key_nashi_dnの4カラムでLIKE文を作る - where 句の条件は以下のような感じ
| パターンNo | 条件パターン | WHERE句 |
|---|---|---|
| 1 | _も%も使わないかつlikeじゃなくて= |
= 'z9y8x7v6-99xx-99yy-99zz-zzzzzz123456' |
| 2 | _も%も使わない |
like 'z9y8x7v6-99xx-99yy-99zz-zzzzzz123456' |
| 3 | _で前方一致検索 |
like 'z9y8x7v6-99xx-99yy-99zz-____________' |
| 4 | %で前方一致検索 |
like 'z9y8x7v6-99xx-99yy-99zz-%' |
| 5 | _で中間があいまいな検索(一応前方一致) |
like 'z9y8x7v6-______________-zzzzzzz123456' |
| 6 | %で中間があいまいな検索(一応前方一致) |
like 'z9y8x7v6-%-zzzzzz123456' |
| 7 | _で後方一致検索 |
like '________-99xx-99yy-99zz-zzzzzz123456' |
| 8 | %で後方一致検索 |
like '%-99xx-99yy-99zz-zzzzzz123456' |
| 9 | _で中間一致検索 |
like '-99xx-99yy-99zz-____' |
| 10 | %で中間一致検索 |
like '%-99xx-99yy-99zz-%' |
ちなみに、後方一致と中間一致(7～10のパターンですね)はインデックス張っててもフルテーブルスキャンになります。
結果
分かりやすい結果が出ました。
- 検索条件値が曖昧じゃない場合は＝のほうがLIKEよりほんのり速い気がする(けど気にしなくてもいいかなってレベル)
- インデックス使えてたら気にしなくていいレベル
- フルテーブルスキャンの場合は前方一致の場合と中間一致・後方一致のばあいだと前方一致検索のほうが速い
- フルテーブルスキャン・前方一致の場合だと「_」と「%」で差が無い
- 一方、フルテーブルスキャン・中間一致/後方一致だと「_」のほうが効率がいいのが明らか
まあ、文字列探索とかしたことある人はイメージしやすいですよね。(インデックスの素晴らしさも改めて実感しましたｗ)
それぞれの速度結果
uuid_key_ari_nn(インデックスあり・NOT NULL)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.005 | 0.003 | 0.012 |
| 2 | 0.005 | 0.003 | 0.017 |
| 3 | 0.005 | 0.003 | 0.011 |
| 4 | 0.005 | 0.003 | 0.013 |
| 5 | 0.005 | 0.003 | 0.011 |
| 6 | 0.005 | 0.003 | 0.013 |
| 7 | 0.160 | 0.154 | 0.176 |
| 8 | 0.248 | 0.240 | 0.263 |
| 9 | 0.160 | 0.154 | 0.182 |
| 10 | 0.248 | 0.241 | 0.269 |
uuid_key_nashi_nn(インデックス無し・NOT NULL)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.143 | 0.137 | 0.156 |
| 2 | 0.147 | 0.141 | 0.162 |
| 3 | 0.147 | 0.140 | 0.175 |
| 4 | 0.147 | 0.141 | 0.160 |
| 5 | 0.147 | 0.140 | 0.159 |
| 6 | 0.147 | 0.141 | 0.162 |
| 7 | 0.160 | 0.153 | 0.177 |
| 8 | 0.248 | 0.241 | 0.286 |
| 9 | 0.160 | 0.154 | 0.182 |
| 10 | 0.248 | 0.240 | 0.269 |
uuid_key_ari_dn(インデックスあり・NULLABLE)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.005 | 0.003 | 0.013 |
| 2 | 0.005 | 0.003 | 0.017 |
| 3 | 0.005 | 0.003 | 0.011 |
| 4 | 0.005 | 0.003 | 0.013 |
| 5 | 0.005 | 0.003 | 0.011 |
| 6 | 0.005 | 0.003 | 0.015 |
| 7 | 0.158 | 0.151 | 0.171 |
| 8 | 0.234 | 0.227 | 0.255 |
| 9 | 0.157 | 0.151 | 0.172 |
| 10 | 0.234 | 0.227 | 0.253 |
uuid_key_ari_dn(インデックスあり・NULLABLE)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.142 | 0.136 | 0.152 |
| 2 | 0.146 | 0.139 | 0.166 |
| 3 | 0.145 | 0.140 | 0.158 |
| 4 | 0.145 | 0.140 | 0.160 |
| 5 | 0.146 | 0.140 | 0.158 |
| 6 | 0.146 | 0.140 | 0.160 |
| 7 | 0.157 | 0.151 | 0.171 |
| 8 | 0.234 | 0.227 | 0.254 |
| 9 | 0.157 | 0.151 | 0.172 |
| 10 | 0.233 | 0.227 | 0.250 |
結論
- 中間一致検索、後方一致検索で「%」使うとパフォーマンス下がる可能性があるので、そういう場合は「_」に置き換えましょう
- そもそもそんな検索しないのがいいんですが・・・
明日は@asmrt_dsさんの「はじめてMySQLを使ってみた話」です。
COUNT に「OR NULL」入れるの、正しい結果返すのわかったけど、心がザワザワしてたけど、今はスッキリした。(スゲーッ爽やかな気分だぜ)
タイトルにある話はつまりこう
ここに都市名を入れたテーブルがあったとします。
mysql root@127.0.0.1:test> select * from city limit 10; +-----------------+-------------------+-----------+--------------+------------------------+ | prefecture_id | prefecture_name | city_id | city_name | city_kana | |-----------------+-------------------+-----------+--------------+------------------------| | 1 | 北海道 | 0 | <null> | <null> | | 1 | 北海道 | 100 | 札幌市 | サッポロシ | | 1 | 北海道 | 101 | 札幌市中央区 | サッポロシチュウオウク | | 1 | 北海道 | 102 | 札幌市北区 | サッポロシキタク | | 1 | 北海道 | 103 | 札幌市東区 | サッポロシヒガシク | | 1 | 北海道 | 104 | 札幌市白石区 | サッポロシシロイシク | | 1 | 北海道 | 105 | 札幌市豊平区 | サッポロシトヨヒラク | | 1 | 北海道 | 106 | 札幌市南区 | サッポロシミナミク | | 1 | 北海道 | 107 | 札幌市西区 | サッポロシニシク | | 1 | 北海道 | 108 | 札幌市厚別区 | サッポロシアツベツク | +-----------------+-------------------+-----------+--------------+------------------------+ 10 rows in set
実際には47都道府県分入っています。 city_idが0のものが都道府県を表しているレコードです。 (あ、このテーブル正規化されてなくてイケてないというツッコミはご勘弁をｗ あえてイメージしやすいようにしてるので)
定義はこちらです。
mysql root@127.0.0.1:test> desc city; +-----------------+--------------+--------+-------+-----------+---------+ | Field | Type | Null | Key | Default | Extra | |-----------------+--------------+--------+-------+-----------+---------| | prefecture_id | tinyint(2) | NO | PRI | <null> | | | prefecture_name | varchar(10) | YES | | <null> | | | city_id | smallint(3) | NO | PRI | <null> | | | city_name | varchar(600) | YES | | <null> | | | city_kana | varchar(216) | YES | | <null> | | +-----------------+--------------+--------+-------+-----------+---------+ 5 rows in set
件数は市区町村名まで入ってるレコードと47都道府県の分で1987件あるとします。
mysql root@127.0.0.1:test> select count(1) from city; +------------+ | count(1) | |------------| | 1987 | +------------+ 1 row in set mysql root@127.0.0.1:test> select count(1) from city where city_name is null; +------------+ | count(1) | |------------| | 47 | +------------+ 1 row in set mysql root@127.0.0.1:test> select * from city where city_id = 0; +-----------------+-------------------+-----------+-------------+-------------+ | prefecture_id | prefecture_name | city_id | city_name | city_kana | |-----------------+-------------------+-----------+-------------+-------------| | 1 | 北海道 | 0 | <null> | <null> | | 2 | 青森県 | 0 | <null> | <null> | | 3 | 岩手県 | 0 | <null> | <null> | | 4 | 宮城県 | 0 | <null> | <null> | | 5 | 秋田県 | 0 | <null> | <null> | | 6 | 山形県 | 0 | <null> | <null> | | 7 | 福島県 | 0 | <null> | <null> | | 8 | 茨城県 | 0 | <null> | <null> | | 9 | 栃木県 | 0 | <null> | <null> | | 10 | 群馬県 | 0 | <null> | <null> | | 11 | 埼玉県 | 0 | <null> | <null> | | 12 | 千葉県 | 0 | <null> | <null> | | 13 | 東京都 | 0 | <null> | <null> | | 14 | 神奈川県 | 0 | <null> | <null> | | 15 | 新潟県 | 0 | <null> | <null> | | 16 | 富山県 | 0 | <null> | <null> | | 17 | 石川県 | 0 | <null> | <null> | | 18 | 福井県 | 0 | <null> | <null> | | 19 | 山梨県 | 0 | <null> | <null> | | 20 | 長野県 | 0 | <null> | <null> | | 21 | 岐阜県 | 0 | <null> | <null> | | 22 | 静岡県 | 0 | <null> | <null> | | 23 | 愛知県 | 0 | <null> | <null> | | 24 | 三重県 | 0 | <null> | <null> | | 25 | 滋賀県 | 0 | <null> | <null> | | 26 | 京都府 | 0 | <null> | <null> | | 27 | 大阪府 | 0 | <null> | <null> | | 28 | 兵庫県 | 0 | <null> | <null> | | 29 | 奈良県 | 0 | <null> | <null> | | 30 | 和歌山県 | 0 | <null> | <null> | | 31 | 鳥取県 | 0 | <null> | <null> | | 32 | 島根県 | 0 | <null> | <null> | | 33 | 岡山県 | 0 | <null> | <null> | | 34 | 広島県 | 0 | <null> | <null> | | 35 | 山口県 | 0 | <null> | <null> | | 36 | 徳島県 | 0 | <null> | <null> | | 37 | 香川県 | 0 | <null> | <null> | | 38 | 愛媛県 | 0 | <null> | <null> | | 39 | 高知県 | 0 | <null> | <null> | | 40 | 福岡県 | 0 | <null> | <null> | | 41 | 佐賀県 | 0 | <null> | <null> | | 42 | 長崎県 | 0 | <null> | <null> | | 43 | 熊本県 | 0 | <null> | <null> | | 44 | 大分県 | 0 | <null> | <null> | | 45 | 宮崎県 | 0 | <null> | <null> | | 46 | 鹿児島県 | 0 | <null> | <null> | | 47 | 沖縄県 | 0 | <null> | <null> | +-----------------+-------------------+-----------+-------------+-------------+ 47 rows in set
さて問題
select count(1) from city where city_name is null;を実行した場合にかえる数値は？
当然、47です。
mysql root@127.0.0.1:test> select count(1) from city where city_name is null; +------------+ | count(1) | |------------| | 47 | +------------+ 1 row in set
では、select count(city_name is null) from city; を実行した場合に返る数値は？
この場合、全件がかえります。
mysql root@127.0.0.1:test> select count(city_name is null) from city; +----------------------------+ | count(city_name is null) | |----------------------------| | 1987 | +----------------------------+ 1 row in set
ここでの city_name is null はtrueの意味の1とfalseの意味の0を返してます。
mysql root@127.0.0.1:test> select *,city_name is null from city limit 10; +-----------------+-------------------+-----------+--------------+------------------------+---------------------+ | prefecture_id | prefecture_name | city_id | city_name | city_kana | city_name is null | |-----------------+-------------------+-----------+--------------+------------------------+---------------------| | 1 | 北海道 | 0 | <null> | <null> | 1 | | 1 | 北海道 | 100 | 札幌市 | サッポロシ | 0 | | 1 | 北海道 | 101 | 札幌市中央区 | サッポロシチュウオウク | 0 | | 1 | 北海道 | 102 | 札幌市北区 | サッポロシキタク | 0 | | 1 | 北海道 | 103 | 札幌市東区 | サッポロシヒガシク | 0 | | 1 | 北海道 | 104 | 札幌市白石区 | サッポロシシロイシク | 0 | | 1 | 北海道 | 105 | 札幌市豊平区 | サッポロシトヨヒラク | 0 | | 1 | 北海道 | 106 | 札幌市南区 | サッポロシミナミク | 0 | | 1 | 北海道 | 107 | 札幌市西区 | サッポロシニシク | 0 | | 1 | 北海道 | 108 | 札幌市厚別区 | サッポロシアツベツク | 0 | +-----------------+-------------------+-----------+--------------+------------------------+---------------------+ 10 rows in set
where 条件と同じ結果をSELECT 句のCOUNT内で表現するにはどうすれば良いでしょう？
OR NULLを足します
mysql root@127.0.0.1:test> select count(city_name is null or null) from city; +------------------------------------+ | count(city_name is null or null) | |------------------------------------| | 47 | +------------------------------------+ 1 row in set
ちなみに、MySQLでCOUNT内のカラムにNULLが含まれている場合は、NULLのカラムはカウント対象から外れます。 これはマニュアルにも載ってますし、Oracle等でも同じなので結構おなじみな話だと思います。
mysql root@127.0.0.1:test> select count(city_name) from city; +--------------------+ | count(city_name) | |--------------------| | 1940 | +--------------------+ 1 row in set
OR NULLを入れるとなぜ正しい結果がかえるのか？
- count(city_name is null) -> TRUEもFALSEもかえす
- count(city_name is null or null) -> 「OR NULL」を付けることで前の構文に当てはまればTRUE、それ以外はNULL -> NULLはカウント非対象 -> 正しい結果がかえる
何に心がザワつくのか？
- 「OR NULL」付けたら条件として見るというのがイマイチ納得いかない。（じゃあ、IS NULLは何なの？）
- 「OR NULL」付けたら0をNULLに置き換えてるの？
基本的にはWHERE句でやるべきことをCOUNTとかでやろうとするとこのように書くということで、本当は使わないほうがいいんだけど HAVING句で使いたい場合にはこれが出てくるだろうし、なんか納得する説明が欲しいんだけど誰か教えてチョモランマ。
と言ったら、神様仏様、いや、『兄貴ィ』降臨です！
- const OR NULLは
- constが真の時に短絡評価されて真
- constが偽の時に "偽とNULLの和集合" と評価されて不定(= NULL)
真 IS NULLは偽偽 IS NULLは偽NULL IS NULLは真
おふざけが入りましたが、本当にスッキリしました。 ありがとうございました！
インデックスマージ、とても良い
MySQLのインデックス
世の中がMySQL8.0.18のハッシュJOINに盛り上がってる中、オジサンは古いけど自分にとっては新しいマージインデックスの話をします。
複数のインデックスがテーブルに作られていても、使えるのは一つ
というのがMySQLでは原則ですが、これの 例外 があります。
それがタイトルのインデックスマージというものです。
インデックスマージとは？
名前の通りインデックス（の結果）をマージするというものです。
MySQLのドキュメント曰く
複数の range スキャンによって、行を取得しそれらの結果を 1 つにマージするために使用されます。
The Index Merge access method retrieves rows with multiple range scans and merges their results into one.
MySQL5.1では使える機能なんで、それ以降の5.5、5.6、5.7、8.0でも使えます。
1 つのテーブルからのインデックススキャンをマージします。複数のテーブルにわたるスキャンはマージしません。
This access method merges index scans from a single table only, not scans across multiple tables.
というわけで、JOINしたテーブル間からはマージしないとのこと。
単体のテーブルの条件内でのみ発動すると。
詳しくはMySQLのドキュメント読んでもらえばわかりますが、要するに1つのテーブル内で2つのインデックスを使ってORで条件指定している場合にこれが発動する可能性があるということです。
社内のDBのスロークエリを眺めていたところ…
WHERE句にこんな感じの条件が書かれているクエリがありました。
SELECT count(*) FROM A_table WHERE (A_table.last_update_time >= '2019-10-16' OR A_table.saigono_koushin_jikan >= '2019-10-16') AND A_table.last_update_time <= '2019-10-17 17:00:00' AND A_table.saigono_koushin_jikan <= '2019-10-17 17:00:00' AND … ;
（※本当はもうちょっと複雑なんですが、大人の事情でカラム名とか色々変えて、雰囲気だけ伝わるようにしていますｗ）
last_update_time というカラムと saigono_koushin_jikan というカラムでいずれかが前日以降の日付が入っていて、いずれのカラムも今日の取得時点の日付までというレンジスキャンをしています。
いずれのカラムにもインデックスは張られておらず、別のインデックスを使っていました。
（ぶっちゃけほぼインデックスフルスキャンでした）
EXPLAINを見るとこんな感じ
（見せられない色々なものを隠していますが、ご了承ください）
この2つのインデックス、張るとマージインデックス効くんじゃね？と見つけて思った自分はさっそくステージング環境にインデックスを張ってみました。
alter table A_table add key i_saigono_koushin_jikan(saigono_koushin_jikan); alter table A_table add key i_last_update_time(last_update_time);
そして実行計画確認したところ、コスト爆下がりでした！
（今回のクエリでは17万が7になりましたｗ）
本番で4.4秒かかってたクエリなんですが、ステージングではこのくらいでした。
| 追加前（秒） | 追加後（秒） |
|---|---|
| 3.234 | 0.0072 |
これは良い！
まとめ
もちろん、インデックスマージで結果が大きかったらインデックスを張った意味はあまりないかもしれないですし、逆にインデックスを張ったことで更新コストが上がる可能性もあります。
（テーブルにあれこれインデックスが増えないほうが良いと思っている人でもあるので）
（インデックスが増えすぎているということはテーブルが正規化されるべきなのかもしれないというのは別の話）
とはいえ、OR句を使ってて、絞り込まれる条件がそこにあるのなら検討する余地はあります。
インデックスのテクニックとして
- PKやUKで検索するのが最速
- セカンダリインデックスはインデックス内にPK情報を持つ
- カバリングインデックスで複合インデックスは効率よく全て使えるようにしましょう
- 実行計画（EXPLAIN）を見てExtraにUsing whereがあったらインデックスをうまく使えてないかも？
- インデックスは張りすぎると更新処理が遅くなる
といったことに加え、今回のインデックスマージを使えるようになるとまたMySQLが好きになるかもしれません。
原則（使えるインデックスは１つ）は頭に入れつつも、ORを見かけたら「もしかして？」と立ち止まって見てみましょう。
MySQL Technology Cafe #4参加してきた
昨日行ってきたやつのお話共有します。 松信さん（Facebook）の話がすごすぎて聞いてて頭プスプス言ってた。 それを思い返しながらまたプスプス言ってたんですが、なんとかかきおこしました。 正直、まだ理解が追いついてないです…。
MySQL Technology Cafe #4
https://oracle-code-tokyo-dev.connpass.com/event/135081/
タイムスケジュール ＊逐次通訳有
| 時間 | 内容 | 登壇者 |
|---|---|---|
| 18:00-18:25 | 受付 | - |
| 18:25-18:30 | はじめに | MySQL GBU 梶山 隆輔 氏 |
| 18:30-18:55 | State of the Dolphin - What's new in MySQL | Oracle Corporation Philip Antoniades (逐次通訳あり) |
| 18:55-19:55 | MySQL Replication and HA at Facebook | Facebook, Database Engineer, 松信嘉範 氏 |
| 19:55-20:30 | ネットワーキング |
State of the Dolphin - What's new in MySQL
セッション概要 MySQLの最新の開発動向についてご紹介するセッションです。MySQL 8.0のGA以降も機能強化が続けられ、MySQL Cluster 8.0やMySQL Cloud Serviceもリリースに向けて開発が進められています。
登壇者 Philip Antoniades Oracle Corporation, Senior Director MySQL Sales Consulting プロフィール MySQL社の古参メンバーの一人で、現在はMySQLの技術営業部門のを統括。2003年にMySQL ABに入社、現在はオラクルにてワールドワイドのMySQLソリューションエンジニアチームのディレクターを務める。ニューヨーク在住。
Top 8 Best Features in MySQL 8
新機能であるInnoDB ClusterやJSON、GIS関数の強化の話をされていました。
- SQL+NoSQL = MySQL ロゴにもなってるよね！
- MySQL InnoDB Cluster みなさんご存知。
- MySQLShell (SQL, Python, Java（Scriptですね）)
- ROLEとかパスワードとかセキュリティ強化
- GIS関数
- データディクショナリ
- ユニコード改善
- SQLのアナライズ
Oracle Open World 2019でこのあたりの話はもちろん、今回はメルカリ社がMySQL Analytics Serviceの話をするらしい。 MySQL Analytics Serviceって、Nipunさんの話（この動画参照くらいでしか知らないけど、当時聞いたときにまだLaboの研究段階だったと思うし、それをどう実用に活かすかみたいな話が聞けるのかと思うとちょっと今からwkwkする。
MySQL Replication and HA at Facebook
セッション概要 FacebookにおけるMySQLの高可用化技術について紹介します。具体的には、Semi-Synchronous Replicationを始めとするレプリケーション技術の話や、そこに追加した機能の話をします。また、高可用化を実現するための内製ソフトウェアのDBStatus、Binlog Server、Logtailerなどを紹介し、どのような背景で、どのような課題を解決することを目指したのかを説明します。
登壇者 松信嘉範 氏 Facebook, Database Engineer プロフィール Yoshinori is a database engineer at Facebook. Before joining Facebook, Yoshinori was a database and infrastructure architect at DeNA, living in Tokyo. Yoshinori’s primary responsibility at DeNA is to make our database infrastructure more reliable, faster and more scalable. Before joining DeNA, Yoshinori worked at MySQL/Sun/Oracle as a lead consultant in APAC for four years. Yoshinori has written eight MySQL related technical books so far and has published technical articles about MySQL, Linux, and Java for a monthly database magazine since 2004.
MySQL replication and HA @Facebook
以下松信さんの話したことを箇条書きでメモしてる分だけですが書き起こします
今日の話(目次)
レプリケーションの話
- グローバルな構成（複数リージョンでMySQLのマスタースレーブを構成）
- Facebookでは１リージョンに１つのMySQLインスタンスしか持ちません
- マルチマスターはやりません
- ある程度シンプルにシステムを組まないとトラブルの時に大変なので
- チェインもしません
- 孫スレーブはない
- カスケードもしません
- R1＝リージョン１という意味
- ちなみに、普段、マスタースレーブって言っちゃうんですが、プライマリー・レプリカっていうべきじゃないという話ありますよね。だけどslaveってソースに書いてるじゃん！ってことで（松信さんが）スレーブって言ったらレプリカのことだと思ってください
- マスターと同じリージョンにスレーブが必要
- Facebookではレプリケーションの主要な点として、リードをスケールアウトできるようにしたい
- リードをスケールアウトするためには、Writeはクロスリージョンにするのはしょうがないとしている
- 単一（同一）リージョンの中では準同期レプリケーション
- 準同期レプリケーションの仕組みは、ローカルのバイナリログを書いて、
- LBU ロジカルバックアップユニット >binlogを書いてackだけ返す
- Master capable regions マスター候補となるスレーブ
- マスター候補にならないスレーブもいる
- binlogを読んで通知する機能：Wormhole
- バイナリログの更新情報を元にMySQL以外のデータを更新する
- キャッシュとかDWHにBinlogの更新情報を通知する Wormholeの参考
binlog BinlogServerの話
- Live Master Promotion
- マスターを切り替えたい時
- アップグレード(マイナーバージョン)をFacebookでは頻繁に行っている（ので必要）
- 重要なのはダウンタイム(500ms)
- マスターをリードオンリーにした後、同期されたところで切り替え先をRWに書き換え、切り替える
- リージョンが違うのでそのくらいかかる
- Dead Master Promotion
- どっかのRACが常に落ちるんで切り替えるのを自動化してる
- ダウンタイムは30sくらい
- ダウンしてもちゃんと復活するように
- 多少切り替え時間がかかっても
- 一個もセミシンククライアントがいないときは書き込みを止めるということができる＝書き込みをさせない状態にする
- リージョンがまるごと落ちるということもないことはない
- そういう時は人が対応する
- データがロストしたか(自動では)わからないので、人が介入する
Binlog Server Role and Features
- GoogleのRippleのようなもの
ちなみにBinlog Serverの参考資料 もあげときます RippleについてはSmartStyleの説明が分かりやすかった yoku0825さんも検証をやってた
- （マスター→binlog server→スレーブの流れ）
- この機構により別のオートメーションが（スレーブやマスターを）構築する
Dependency replication
- MySQLのレプリケーションを使っているほぼすべての人は遅延を経験したことがあると思うんですけど
- MySQLのレプリケーション自体はすごく進化していて、並列性が上がって早くなっていますが課題はある
- Facebookでは特定のアカウントの特定の投稿、例えばオバマが写真をアップロードした時に、その社員へのアクセスが集中して遅延が発生しました
- こういう場合、特定のDBにデータがあるので、DBの並列性を上げても１つなんで限界がある
- Facebookでは RBR を使ってbinlogから並列に更新可能なトランザクションを判定するグラフを作っている
- トランザクション単位ではなく、レコード単位で並列度を上げていく
RBRって何？ と思ってtom__boさんの記事に行く
- RBR (Row based replication)ではRW/WWコンフリクトを見つけるためのすべてのデータが入っている。
- conflictをDAGに書き換えて、コンフリクトがないものは部分的にであっても(例ではrowレベルで分解して競合しない部分を並列化していた)すぐに適用する
- 最終的には適用でCommit orderingが保持されるようにしている
- この方法でコンフリクトが少ない環境ではsysbenchのoltp_write_onlyのシナリオで最大4~5倍の性能改善
ああ、Row Based Replのことね。 とはいえ、言ってることはわかるけど、3行目の話とかどうやって実現しているのかわけわかめ・・・。
- 非同期なスレーブが準同期なスレーブよりも多くのバイナリログを持っている可能性はある
- Facebookでは、node fenceは準同期レプリケーションを組んでいるスレーブをすべて止める
- スレーブよりもbinlog serverが正としている
- binlog serverが再構築されないようにしている
- 特定のUIDを持ったサーバにしかアクセスできないようにする
- Rows Queryにコメントが書けるので、ほかのシステムから判別できるようにしている
- シャードのIDとかレプリカセットとか
- ネットワークの優先度を調整できるようにしてる
- 準同期しているbinlog serverが最も進んだ状態にすることでフェールオーバーなどをシンプルにできた
RBR COMPLETE image format
- binlog_row_imageについて
- FULLに比べて元の値しか書かない
- MINIMALだと微妙
- FULLだと多いのでその中間のCOMPLETEを追加
Key Learnings/observations
- binlog自体がハックされて壊されると深刻な問題
- checksum機能が役に立ってる
- SBRはやめてる
- RACが部分的に遅延していると、特にリージョン間のネットワークが遅延してると書き込みは完結してても、レプリケーションが遅延する
- SQL Thread をどんなに早くしても遅れてしまう
- できるだけそういう状況を早く検知できるようにしている
今後
あたりが今後のチャレンジ
- あと、パッチの管理、いわゆる技術的負債として残っているものをできるだけなくしていきたい
Q&A
Q: NICの速度の重要性が共感してもらいにくい。松信さんはどうお考えか？
- MySQL だと優先度を変えるというのが大事
- バックアップとかはそうかもしれないけど
- あまりバンドル間での帯域については気にしてない
Q:リストアはどうしている？
- こないだ梶山さんと話したのはバックアップかHAで今回の話はHAにしたんですけど、
- mysqldumpでバックアップとってる
- consistent restore
- 自動的にバックアップをリストアしたものが正しいかを検証する機構がある
Q:変えたくても変えられないものはあるとは思うんですが、もし変えてもいいものがあればどこを変えたいですか？
- MySQLじゃないものを使うこともできますが、レプリも使えてSQLも使えて気軽に使えるMySQLはイイ
- RocksDBエンジンもあるし
- パッチの管理が大変なので、できるだけその改造がしなくていいようにしたいんだけどそうはいかなくて、
- でもMongoDBでどうする？ってのはそれはそれであるし
- KVSでSQLが使えないとめんどい
- だからRDBMSがいいよね、というのでMySQL使ってます
- とにかくパッチを取り入れてほしい
- Group ReplicationとかMySQLClusterの場合、同一リージョンにないといけないのでリージョンをまたがりたいFacebookの方針と合わない
- Group Replicationがリージョンまたがるといいなと思ってる
授賞式
当日出席されていたMySQL ACEのとみたさん、yoku0825さん、三谷さんと、DB Techで登壇された北川さんにイルカの授与式
受賞後のyoku0825さんを撮っちゃいました
最後は残った人で写真撮影
資料について
後日上がるとのことなので、イメージわきづらい人はそれを待ちましょう
最後に
mysql_cafeのスレッドに書かれていることをだいぶ参考に書かせてもらいました。
みなさんありがとうございました（特にyoku0825さん） そして、美味しい食事と飲み物、きれいな会場と、Philipさんと松信さんの貴重なお話を提供してくれたOracle社の関係者の皆様に感謝です。
データを適当に作成するmysql_random_data_loadを試してみた
テストデータ作りたいな
- 自分で手で作るのもめんどいし、定義変わるとアレ（メンテが必要）やしな。
- せや、型から適当にデータ作るツールとかないんかな？
- あったわ→mysql_random_data_load
さすぺる！（さすがPercona！）
試してみよう
use test; -- 適当なテーブル作る CREATE TABLE `person` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', `guid` varchar(36) COLLATE utf8mb4_bin NOT NULL COMMENT 'GUID', `name` varchar(150) COLLATE utf8mb4_bin NOT NULL COMMENT '名前', `age` tinyint(4) NOT NULL DEFAULT '0' COMMENT '年齢', `money` int(11) DEFAULT NULL COMMENT '手持ちのお金', `marriage_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '結婚フラグ', `prefecture` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '住所（都道府県）', `tel_number` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '電話番号', `fax_number` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'FAX番号', `email` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Ｅメールアドレス', `address` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '住所', `company` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '会社名', PRIMARY KEY (`id`), UNIQUE KEY `u_1` (`guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='誰かさんの情報';
すぐ使いたいんで、precompiled binariesから取ってくる。
解凍して、mysql_random_data_loadを$PATHで見えるところに置いて、
$ which mysql_random_data_load /home/xxx/bin/mysql_random_data_load
ってな感じで見えるようになったんでOK。
使い方はhttps://github.com/Percona-Lab/mysql_random_data_loadに書いてある。
デバッグモードで100行くらい入れて試してみるか。
$ mysql_random_data_load test person 100 --debug --user=root --password=xxx --host=127.0.0.1 --port=3307
DEBU[2019-07-06T10:30:48+09:00] &tableparser.Table{
Schema: "test",
Name: "person",
Fields: {
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "id",
OrdinalPosition: 1,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "bigint",
CharacterMaximumLength: sql.NullInt64{},
CharacterOctetLength: sql.NullInt64{},
NumericPrecision: sql.NullInt64{Int64:19, Valid:true},
NumericScale: sql.NullInt64{Int64:0, Valid:true},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{},
CollationName: sql.NullString{},
ColumnType: "bigint(20)",
ColumnKey: "PRI",
Extra: "auto_increment",
Privileges: "select,insert,update,references",
ColumnComment: "ID",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "guid",
OrdinalPosition: 2,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:36, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:144, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(36)",
ColumnKey: "UNI",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "GUID",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "name",
OrdinalPosition: 3,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:150, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:600, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(150)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "名前",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "age",
OrdinalPosition: 4,
ColumnDefault: sql.NullString{String:"0", Valid:true},
IsNullable: false,
DataType: "tinyint",
CharacterMaximumLength: sql.NullInt64{},
CharacterOctetLength: sql.NullInt64{},
NumericPrecision: sql.NullInt64{Int64:3, Valid:true},
NumericScale: sql.NullInt64{Int64:0, Valid:true},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{},
CollationName: sql.NullString{},
ColumnType: "tinyint(4)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "年齢",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "money",
OrdinalPosition: 5,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "int",
CharacterMaximumLength: sql.NullInt64{},
CharacterOctetLength: sql.NullInt64{},
NumericPrecision: sql.NullInt64{Int64:10, Valid:true},
NumericScale: sql.NullInt64{Int64:0, Valid:true},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{},
CollationName: sql.NullString{},
ColumnType: "int(11)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "手持ちのお金",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "marriage_flag",
OrdinalPosition: 6,
ColumnDefault: sql.NullString{String:"0", Valid:true},
IsNullable: false,
DataType: "tinyint",
CharacterMaximumLength: sql.NullInt64{},
CharacterOctetLength: sql.NullInt64{},
NumericPrecision: sql.NullInt64{Int64:3, Valid:true},
NumericScale: sql.NullInt64{Int64:0, Valid:true},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{},
CollationName: sql.NullString{},
ColumnType: "tinyint(1)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "結婚フラグ",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "prefecture",
OrdinalPosition: 7,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:30, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:120, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(30)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "住所（都道府県）",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "tel_number",
OrdinalPosition: 8,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:20, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:80, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(20)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "電話番号",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "fax_number",
OrdinalPosition: 9,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:20, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:80, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(20)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "FAX番号",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "email",
OrdinalPosition: 10,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:255, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:1020, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(255)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "Ｅメールアドレス",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "address",
OrdinalPosition: 11,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:300, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:1200, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(300)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "住所",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
{
TableCatalog: "def",
TableSchema: "test",
TableName: "person",
ColumnName: "company",
OrdinalPosition: 12,
ColumnDefault: sql.NullString{},
IsNullable: false,
DataType: "varchar",
CharacterMaximumLength: sql.NullInt64{Int64:300, Valid:true},
CharacterOctetLength: sql.NullInt64{Int64:1200, Valid:true},
NumericPrecision: sql.NullInt64{},
NumericScale: sql.NullInt64{},
DatetimePrecision: sql.NullInt64{},
CharacterSetName: sql.NullString{String:"utf8mb4", Valid:true},
CollationName: sql.NullString{String:"utf8mb4_bin", Valid:true},
ColumnType: "varchar(300)",
ColumnKey: "",
Extra: "",
Privileges: "select,insert,update,references",
ColumnComment: "会社名",
GenerationExpression: "",
SetEnumVals: {},
Constraint: (*tableparser.Constraint)(nil),
SrsID: sql.NullString{},
},
},
Indexes: {
"u_1": {
Name: "u_1",
Fields: {"guid"},
Unique: true,
Visible: true,
},
"PRIMARY": {
Name: "PRIMARY",
Fields: {"id"},
Unique: true,
Visible: true,
},
},
Constraints: {
},
Triggers: {
},
conn: (*sql.DB)(nil),
}
INFO[2019-07-06T10:30:48+09:00] Starting
DEBU[2019-07-06T10:30:48+09:00] Must run 1 bulk inserts having 100 rows each
INFO[2019-07-06T10:30:49+09:00] 100 rows inserted
お、入ってる感じやな。どれどれ？
| id | guid | name | age | money | marriage_flag | prefecture | tel_number | fax_number | address | company | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | recusandae et consequatur eius aut a | consequatur nihil error eius ut. | 5 | 1342458479 | 6 | rerum unde qui perferendis nob | Terry Sanchez | Tammy Torres | eum culpa nam minima maxime. | et non nisi voluptas temporibus aliquam magnam. | eius deleniti nam et sint voluptates id. |
| 2 | aut odio soluta ut dolores. | omnis accusamus est voluptatem consequuntur quisquam quae quia. | 11 | 914091476 | 5 | fugiat magnam aut culpa ut rep | Janet Oliver | Juan King | nemo suscipit doloremque porro error et qui tenetur ut asperiores corporis! | nemo quia magnam hic dolor. | adipisci architecto animi vitae deleniti rerum. |
| 3 | aut dolor consequatur soluta exercit | perspiciatis sit odio fugit quasi non. | 3 | 1255569388 | 11 | ullam facere exercitationem il | Janet Murphy | Fred Grant | ut ea nisi recusandae quo inventore tenetur et. | et blanditiis maxime est quam quidem id. | illo ea voluptas vitae repudiandae architecto saepe. |
| 4 | quidem omnis iste ea a impedit odio | quod velit ut earum dolore molestiae! | 14 | 2003588754 | 12 | et qui et id voluptas voluptat | Tina Garrett I II II | Joe Edwards | quo explicabo eum qui alias sequi. | dolorem quaerat nihil aut perspiciatis qui asperiores nihil. | alias nisi ullam quo debitis iusto animi. |
| 5 | illo earum est pariatur aut in digni | consequatur molestias dicta omnis. | 4 | 1698116023 | 5 | doloribus natus distinctio nos | Brenda Hunt | Sean Carter | assumenda et deserunt quidem. | consequatur occaecati non libero accusantium reprehenderit quam suscipit. | ratione eum beatae laboriosam quo enim. |
| 6 | consequuntur quibusdam nihil soluta | accusamus similique cum eos quidem. | 8 | 1153628287 | 1 | voluptas est et temporibus aut | Paula Parker | Kathryn Watkins | quis voluptatem at perferendis labore. | reiciendis voluptates itaque voluptatem optio ea qui hic. | ab quibusdam illum. |
| 7 | voluptas animi vero tenetur adipisci | et ut et! | 5 | 1640670520 | 8 | quod corporis qui rerum culpa | Joshua Hart | Louise Fernandez | quia et molestiae distinctio quaerat quasi soluta aut voluptatem et recusandae! | doloribus dignissimos ut hic tempora. | temporibus eveniet vel quisquam repellendus aut animi fugit. |
| 8 | quae quod ratione pariatur repellend | suscipit omnis aut consequatur numquam laboriosam non repellendus. | 11 | 1218842385 | 7 | dolorem quia qui accusamus eni | Kenneth Burns | Terry Bryant | consequatur ex aliquam beatae qui et consequatur et. | vitae ratione quaerat rerum facilis. | animi sit ut nobis quae ea. |
| 9 | laudantium odit et facere minima. | ea quis voluptas ipsum dignissimos asperiores sequi aut. | 13 | 601894044 | 12 | id non consequuntur iste disti | Charles Anderson | Raymond Diaz | sequi impedit nihil ea vel et nihil rerum aut. | sit commodi et autem eius. | odit qui vitae facilis modi quia harum amet aspernatur. |
| 10 | magni ullam dignissimos praesentium | dicta qui sunt dolor ut repellat hic. | 10 | 1408984034 | 11 | quia tempora consequatur delec | Stephanie Garcia | Patricia Carroll | repudiandae fuga dignissimos odio voluptate quia. | quia necessitatibus molestiae dolorem natus accusantium. | sunt consectetur est amet doloremque qui autem quae. |
そりゃそうだよね・・・。
だって型で見てるだけなんだもん。
カラム名も見てくれるツール欲しい！
- 日本語で入ってほしいものもあるよね。
- コメントを参考にしたりしてほしいよね。
- MySQL8.0でも動いて欲しい（このツールはMySQL5.7までしか動かない）
自分で作るか、こいつをいじるかな〜
追記
を見るとわかりますが、型のフィールド値に制限が有ります。
decimal(25,0)
とかやると、
panic: invalid argument to Int63n
ってメッセージが出るのでご注意を！
♡♡♡主キー大主キー♡♡♡（MySQL Casual Talks vol.11）
名言生まれる！
タイトルにもある大主キーという単語がtwitterのTLに一瞬溢れました。
後日もことあるごとに大主キーという人が続出。
もうこれは今年の流行語対象 (in MySQL)といっても過言ではありません。
MySQL Casual Talksとは？
もっと深く浅く、広く狭くMySQLを使っていこうという趣旨のイベントです。 多方面から多様なMySQLの使い方、運用、TipsなどなどのTalkを集めたいと思っております。
https://mysql-casual.connpass.com/event/131551/
というイベントの趣旨の通りvol. 11 でも非常に個性的かつDeepな話が繰り広げられました。
togetter
tmtmsさんがまとめたこちらをどうぞ
| 発表者 | 内容 | 時間 | 資料URL |
|---|---|---|---|
| TBD | オープニング | 5分くらい | - |
| do_aki | TritonnからElasticSearchへの移行話 | 30分くらい | https://www.slideshare.net/do_aki/tritonn-elasticsearch |
| awache | DBRE(Database Reliability Engineering) 始めました ~ Make it visible / No Ops, More Code ~ |
30分くらい | https://speakerdeck.com/_awache/no-ops-more-code |
| tom__bo | テーブル定義から見積もるストレージサイズと8.0以降のベンチマーク自動化について | 20分くらい | https://speakerdeck.com/tombo/about-table-size-estimator |
| t_kyt | オンプレMySQLをRDSにして解決したこと/しなかったこと(仮) | 15分か20分くらい | |
| lhfukamachi | TBD | 10分くらい | https://www.slideshare.net/hidemifukamachi/sql-require-primarykey |
| keny_lala | TBD | 5～10分くらい | https://www.slideshare.net/kenken0807/mysql-149112359 |
| tmtms | MySQLと令和 | 10分くらい | https://speakerdeck.com/tmtms/mysql-on-reiwa |
TritonnからElasticSearchへの移行話
do_akiさんによる全文検索のお話。
す、すみません。にわかなMySQLerなんでTritonn もSennaも初耳です…。
このTritonn、2009年で開発が止まっており、脆弱性等を考えるとMySQLも5.0から移行しなくてはならず脱Tritonnをする必要が出たことがきっかけらしい。
ここで移行案を色々検討されたところはTritonnに限らず全文検索を使う人には本当に参考になる話。
https://www.slideshare.net/do_aki/tritonn-elasticsearch#10
Elasticsearchを採用されるが、そこでも試行錯誤しているところもやはり参考になる。
DBRE(Database Reliability Engineering) 始めました ~ Make it visible / No Ops, More Code ~
会場を貸してくれたBizreachの_awacheさんのトーク。
「DBREとは何ぞや？」を数か月前に教えてくれたその人の話ということで参加前から非常に楽しみにしてました。
DBREとは、については
を読んだほうが確実なのですが、二つを読んだうえでの自分のいい加減な理解では 「DBを使って開発の手助けをするスペシャリスト」 です。
DBAとの違いがややこしいというか、今までのDBAはDBRE的なこともやってる場合が多いんだと思っていて、
- DBAはデータベースを守るために尽力する(セキュリティ、バックアップ・リストア(本番)、レプリケーション、HA設定等)のに対し、
- DBREはバックアップを開発機に利用するためのパイプを作ったり、そのままでは使えない場合はマスキングが必要なのでその機構を作ったりするイメージ。
まあ、DB大好きな人はどっちの素養も大抵持ってるとは思うんですが、DBAが僧侶、DBREが戦士・魔法使い的な感じでしょうかね？(どっちもやってる人は勇者ですねｗ)
資料を見るとわかりますが、DBREの存在意義として ３つの非機能要件の共通化 を行い、それを各事業へ提供することで会社のMissionとVisionを具現化しようとしています。
３つの非機能要件の共通化とは
- Provisioning(DBのパラメータセッティング、ユーザ設計等)
- Monitoring(定量モニタリング⇒人材の横断的な移動を可能に)
- Backup(開発環境の構築、定期的バックアップ、マスキングによる安全な開発)
なるほど、DBREとは安心してDBを使えるように積極的に活動することだな！(雑な理解)
そして面白いのはBizreach社のDBREは守備(結局DBA的なことも全部やってる)時もやはりオフェンシブで、例えば
- リストア時に監査ログを全出力
- リストアをする時点でランダムパスワード
- 必要な処理が終わったらインスタンスもスナップショット(バックアップファイル)も全て削除
と言った守備的なところもイケイケ。
- ダンプもパラレル処理(テーブル単位でダンプする)
とか、
- 事業側にマスキングルールを決めさせて、そのルールとなる設定ファイルをcommitしてもらって、
- 欲しい形でデータをTSV形式で作る仕組みを作った。
- これにより責任範囲を明確にして自分たちを守る
とか、
かっこよすぎます！
少しマネさせてもらいます。
テーブル定義から見積もるストレージサイズと8.0以降のベンチマーク自動化について
今回のトークの中で一番みんなが楽しんだのがtom__boさんの話だったと思うｗ
2年目の子がMySQLのパーサー作るという偉業にチャレンジするもんだから、おじさんたちの目はキラキラしちゃって、見えないはずのマサカリが具現化してた気すらしましたｗ
テーブルサイズを見積もるためにここまでやろうということすら思わない凡人の自分にはtom__boさんは眩しすぎて、
「ぜひ、うちの会社に来ないか？」(代わりに俺がリストラされるだろうけど)
オンプレMySQLをRDSにして解決したこと/しなかったこと(仮)
t_kytさんのこの話はスライドが上がってないんですが、内容は結構濃くて、メモを後述しておきますが要するにMySQLのことというかDBすらあんまり知らなかったけど、1年間ひたすらバージョンアップ等を繰り返して詳しくなっちゃいました！というもの。
MySQL4.xをRDSで上げるのにまず5.6まで持っていかなければいけないので、その色々な苦労をDBあまり知らない人が悪戦苦闘しながらすることは話としては参考になります。
ここからはLT
Sql require primary_keyを使って主キーを必須にさせる
lhfukamachiさんのLT。
このLTで冒頭の名言が生まれました。
https://www.slideshare.net/hidemifukamachi/sql-require-primarykey#4
スライドがちょっとyokuさんの影響受けてるのはさすがｗ
MySQLのリアルタイムモニタリングツールを作った話
keny_lalaさんのモニタリングツールの話。
Go製のMySQLのリアルタイムモニタリングツールですが、最後に
「これって需要あります？」
とあったんですが、
めっちゃほしいです！
MySQLと令和
最後の締めはACEのtmtmsさんの文字コードのお話。
これについては既に先日のOracleで聴いてたので知ってはいましたがやはり濃いなぁ…。
まとめ
MySQL Casual Talks、２回目の参加(前回はVol.10)でしたが、今回も非常に面白かったです。
特に@_awacheさんの話は今後の自分の仕事の方向にも影響を与えてくれたいいお話でした。
あとは若い人たちが本当に活気というか才能があって、羨ましいというか頑張らねばという気持ちになりました。
yoku0825さんの幻のLT聞けなかったのはちょっぴり残念でした。
最後に
開場・飲食物を提供してくれたBizreach社と運営を手伝ってくださってた社員の方々と@_awacheさん、 トークしていただいた皆様、 開催してくれた管理人のお二人、 ありがとうございました。
Vol.12も楽しみにしています。
AWS Loft TokyoのMiddlewares Deep Talks行ってきました
これもあんまりまとめる感じでは無いですが、とりあえず後から追記できる程度には雑記します。
iddlewares Deep Talks（AWS Loft Tokyo）
Amazonさんがありがたいことにミドルウェア関連の話≒DB系の話やってくれたんで、目黒のビルに行ってきた。
ぼくらが8.0に至ったみちのり（踏破）
https://speakerdeck.com/yoku0825/bokuraga8-dot-0nizhi-tutamitifalseri-ta-po
遅刻したので半分聞けず…(´;ω;｀)
道に迷ったり、3階のエレベーター乗り場で別の会社の人に違う場所に案内されたり、入り口で登録したはずのメールアドレスが無いよーと言われたり…。
(着いたときは汗だく…)
でも、こないだのOracle Code2019とその辺は話が重複してたので結果オーライ。
後半の話は実際にMySQL8使っての今のところの感想ということで、とても興味深いものだった。
(スライド見るともしかすると微妙そうに見えちゃうかもしれないけど、新規に立てるならMySQL8.0は超おススメだと俺も思う)
PostgreSQL 12の話
https://www.slideshare.net/masahikosawada98/postgresql-12
一番気になったのはパーティションの話。
プルーニングでパフォーマンス超上がったとのこと。
休憩時間に
気になったんで間の休憩時間に澤田さん捕まえて聞いてみたら、パーティションした場合別のところ(複数あるらしい)でメモリ食うので数千レベルのテーブルで使うのはまだ危険とのこと。
(そこにYahoo! の三谷さんも来たので巻き込んで3人でMySQLとPostgreSQLの話で「へー」「ほー」と言い合う) (三谷さん、ACEポイントは6月かららしい！5月はタダ働き…。mjsk)
そのあと、
- MySQLの良さはクエリからコストが予想できること。
という温かいMySQLとPostgreSQLの交流会がTwitter上で行われた。(sh2さんのスライド、メッチャためになった！)
PostgreSQLのパーティションについては13で更に改良する(澤田さんも参加)ので、お楽しみに！とのこと。(12がまだRC出てないのにｗ)
What's new in Slastic Stack 7.0?
https://noti.st/johtani/psVhfT/whats-new-in-elastic-stack-7-1
- Elasticsearchのインデックスはお前(RDB使い)のインデックスじゃない
- 今のElastic Searchは本当に何もしなくてもいい感じの設定になってる
- 可用性めっちゃ高い
- むしろ設定触るな
- seed node触るな
- なんかあったらログを見ろ！
- PainlessでPainfull
というメッセージが印象に残った。
(ちなみに、p37のnumber_indicesってのはnumber_of_shardsの間違い)
Cassandra vs ScyllaDB 性能比較
資料がまだアップされてないのでメモを乗せとく。
資料がアップされました→こちら
要するにCassandra使うんなら、ScyllaDBスゲー早いよおススメ、Yahoo!では切り替え始めてるって話。
Yahoo! JapanでのCassandra
Cassandraの限界
特定サイズを超えたデータにアクセスが集中すると、クラスタが不安定になる
Cassandra VS ScyllaDB
- 99.9％読み書きレイテンシ
- Cassandraは早い段階で跳ね上がり
- ScyllaDBは2倍程度のPF
- IO waitがScyllaDBではほとんどでない
- ScyllaDBはSATA3の性能限界に到達したが、Cassandraは使いきれず
https://www.scylladb.com/2019/05/22/yahoo-japan-using-scylla-and-cassandra-at-scale/
S3 整合性モデルと Hadoop/Spark の話
https://www.slideshare.net/ssuserca76a5/s3-hadoopspark
分かったこと。
『俺はまだS3を知らない』
でも、S3の使い方ちょっと知れて雰囲気だけ感じました。 S3にログとか置いて遊ぼうかな。
Introducing Algolia with Demo
https://speakerdeck.com/shinodogg/introducing-algolia-with-demo
すみません、俺本当に何もわからないおバカさんです。 俺には難しかったです…。
Middlewares Deep Talks 楽しかった！
軽食(サンドイッチ)もドリンク(俺はコーラばっかりだけど、みんなはビール)も旨かった。
スピーカーの方々の軽妙なトークに笑い声も何度も上がり、壇上のスピーカーと聞いてる人たちとのやり取りなんかもあって和気あいあいとしてました。
最後の懇親会までいたかったんですが、妻のご機嫌も気にしてたので昨日は早々に帰宅。
開催者の皆様、場所と食事を提供してくださったAmazon様、そしてスピーカーの皆様
ありがとうございました
