本稿は、MySQL Casual Advent Calendar 2017の19日目の記事です。
昨日はbringer1092さんの「NDB Clusterをmemcachedで簡単にアクセス」でした。
昨年の記事とはうって変わって、今年はMySQLのJSONカラムとMongoDBの簡易ベンチマークについて取り上げてみたいと思います。
きっかけは自分のGitHubに何かしら残してみたかったからです（）
検証手順
1. MySQL と MongoDB を同じサーバ上にセットアップ
2. "mysql_mongodb_simple_bench.sh"を任意のディレクトリに配置
3. "json-templete.hbs" を /tmp 直下に配置
4. "mysql_mongodb_simple_bench.sh" を実行 ※対話式になっています
5. 「1」を入力して、dummyjsonコマンドを使ってテストデータを生成
6. 「1」を入力して、MySQLでベンチマーク実行
7. "mysql_mongodb_simple_bench.sh" を実行 → 「2」を入力してテストデータ生成をスキップ
8. 「2」を入力して、MongoDBでベンチマーク実行
9. "mysql_bench_result.log" / "mongodb_bench_result.log" の内容を確認
使用したスクリプトについては、以下のURLで公開しています（プルリク歓迎）。
https://github.com/kakuka4430/mysql_mongodb_simple_bench
検証環境
今回のベンチマークは以下の環境で実施しています。
| OS | CentOS 7.4 |
| CPU | Xeon(R) E5-2403 8core |
| MEM | 62 GB |
| Disk | 一般的なHDD |
生成・使用するテストデータは100万件（約146M）のJSONファイルです。
MySQL、MongoDBともに設定ファイルはいじらず、デフォルトのままにしています。
ただし、MySQLのみテストデータの読み込みを許可するため、 secure_file_priv='/tmp' の設定を追加しています。
検証結果
以下の数値は、time コマンドで出力される「real」時間をまとめたものです。
■ MySQL
| Operation | Time 1st | Time 2nd |
|---|---|---|
| INSERT bench | 22.407s | 15.135s |
| SELECT bench | 3.102s | 3.154s |
| UPDATE bench | 3.695s | 3.684s |
| DELETE bench | 4.244s | 4.632s |
■ MongoDB
| Operation | Time 1st | Time 2nd |
|---|---|---|
| INSERT bench | 15.210s | 15.256s |
| SELECT bench | 1.172s | 1.102s |
| UPDATE bench | 1.300s | 1.349s |
| DELETE bench | 2.089s | 2.118s |
MongoDBの方が速いことが分かりました。ただ、このままではMySQLに良いところがなくなってしまうので、チューニングしてみましょう。
チューニング1 : サーバパラメータの変更
とりあえず my.cnf に以下の設定を追加してみました。
query_cache_type=OFF
innodb_buffer_pool_size=4G
innodb_log_file_size=512M
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_checksum_algorithm=crc3
skip_innodb_doublewrite
結果は以下の通りです。少し時間は短くなりましたが、ほとんど変わっていませんね。
| Operation | Time 1st | Time 2nd |
|---|---|---|
| INSERT bench | 23.444s | 22.497s |
| SELECT bench | 2.730s | 2.860s |
| UPDATE bench | 3.353s | 3.499s |
| DELETE bench | 3.757s | 4.035s |
チューニング2 : 仮想列を使ってインデックス追加
参考サイトでも述べられている通り、JSONカラムにはインデックスを貼ることができません。
しかし、仮想列を使うことで疑似的にインデックスを利用することが可能です。
・SELECT count(*) FROM bench WHERE JSON_EXTRACT(col1,'$.nation') = 'Japan'
・UPDATE bench SET col1 = JSON_SET(col1, '$.phone', '000-000-0000') WHERE JSON_UNQUOTE( JSON_EXTRACT(col1,'$.phone') ) LIKE '080%'
・DELETE FROM bench WHERE JSON_EXTRACT(col1,'$.birth') >= '1995-01-01'
今回のベンチマークで使用している上記の3クエリに対して有効なインデックスを作成してみましょう。
mysql> ALTER TABLE bench ADD bench_nation VARCHAR(100) AS (JSON_UNQUOTE(col1->"$.nation"));
mysql> ALTER TABLE bench ADD INDEX (bench_nation);
mysql> ALTER TABLE bench ADD bench_phone VARCHAR(15) AS (REPLACE(JSON_UNQUOTE(col1->"$.phone"), '-', ''));
mysql> ALTER TABLE bench ADD INDEX (bench_phone);
mysql> ALTER TABLE bench ADD bench_birth DATE AS (JSON_UNQUOTE(col1->"$.birth"));
mysql> ALTER TABLE bench ADD INDEX (bench_birth);
仮想カラムが上手く作れていれば、データの再ロード後には以下のような出力になるかと思います。
mysql> DELETE FROM bench;
mysql> LOAD DATA INFILE '/tmp/sample_data.json' INTO TABLE bench(col1);
mysql> SELECT * FROM bench LIMIT 1\G
*************************** 1. row ***************************
id: 1000002
col1: {"id": 0, "name": "Drew Remus", "birth": "1997/02/08", "email": "drew.remus@protheon.xyz", "phone": "000-031-0950", "nation": "Tanzania, United Republic of"}
bench_nation: Tanzania, United Republic of
bench_phone: 0000310950
bench_birth: 1997-02-08
仮想カラムを使用するようクエリを変更します。この状態であれば、インデックスを利用することができます
mysql> EXPLAIN SELECT count(*) FROM bench WHERE bench_nation = 'Japan';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | bench | NULL | ref | bench_nation | bench_nation | 103 | const | 3970 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
mysql> EXPLAIN UPDATE bench SET col1 = JSON_SET(col1, '$.phone', '000-000-0000') WHERE bench_phone LIKE '080%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+------+----------+------------------------------+
| 1 | UPDATE | bench | NULL | range | bench_phone | bench_phone | 18 | const | 1014 | 100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+------+----------+------------------------------+
mysql> EXPLAIN DELETE FROM bench WHERE bench_birth >= '1995-01-01';
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+--------+----------+-------------+
| 1 | DELETE | bench | NULL | range | bench_birth | bench_birth | 4 | const | 110872 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+--------+----------+-------------+
このテーブル定義、クエリを用いて、再度ベンチマークを実行してみます。
CREATE TABLE IF EXISTS 文を使っているので、テーブルの再作成は行われません。
クエリについては、実行スクリプトのコメントアウトを外してください。
| Operation | Time 1st | Time 2nd |
|---|---|---|
| INSERT bench | 1m11.631s | 1m10.947s |
| SELECT bench | 0.012s | 0.008s |
| UPDATE bench | 0.134s | 0.108s |
| DELETE bench | 3.162s | 3.219s |
インデックスが使えるようになったおかげで、SELECTとUPDATEはかなり速くなりました。
一方で、INSERT（LOAD DATA）が著しく遅くなってしまっています。また、DELETEもそれほど時間に変化はありませんでした。
INSERTは仮想カラムの存在がオーバヘッドになっていると考えられます。
DELETEについては以下のように削除対象の行数が多いことが要因である可能性があります。
mysql> SELECT count(*) FROM bench WHERE bench_birth >= '1995-01-01';
+----------+
| count(*) |
+----------+
| 49979 |
+----------+
ひとまず、MySQL側についてはここまでとします。
おまけ１ : MongoDBのチューニング
MySQLだけ色々手を加えるのはアンフェアなので、MongoDBも簡単にチューニングしてみたいと思います。MySQL同様、インデックスを作成してみましょう。
# echo 'db.bench.createIndex({nation : 1})' | mongo d1
# echo 'db.bench.createIndex({phone : 1})' | mongo d1
# echo 'db.bench.createIndex({birth : 1})' | mongo d1
※ MongoDBではベンチマークごとにテーブルを再作成しているので、スクリプトを修正します
この状態でのベンチマーク結果は以下の通りです。やはりインデックスの効果は偉大ですね…。
| Operation | Time 1st | Time 2nd |
|---|---|---|
| INSERT bench | 38.341s | 36.985s |
| SELECT bench | 0.139s | 0.141s |
| UPDATE bench | 0.219s | 0.219s |
| DELETE bench | 3.726s | 3.742s |
おまけ２ : MySQL8.0を使ってみる
アドベントカレンダーの流れに便乗して MySQL5.7 を MySQL8.0 に入れ替えてみました。
# systemctl stop mysqld
# yum remove mysql-community-server
# vi /etc/yum.repos.d/mysql-community.repo
→ 5.7のリポジトリを「disabled」にし、8.0のリポジトリを「enabled」に
# yum install mysql-community-server
# mv /etc/my.cnf.rpmsave /etc/my.cnf
# vi /etc/my.cnf
→ "query_cache_type=OFF"をコメントアウト
# systemctl start mysqld
# mysql_upgrade -u root -p
結果は以下の通りです。MySQL8.0でも余り状況は変わりませんでした…。
| Operation | Time 1st | Time 2nd |
|---|---|---|
| INSERT bench | 1m31.722s | 1m31.670s |
| SELECT bench | 0.012s | 0.022s |
| UPDATE bench | 0.119s | 0.212s |
| DELETE bench | 4.105s | 3.944s |
まとめ
今回実行した主なベンチマーク結果（2回実行した平均時間）をまとめると以下のようになりました。MySQLでも、MongoDBでも、インデックスが大切なのがよく分かりました！
| MySQL (素) | MySQL (index有) | MongoDB (素) | MongoDB (index有) | |
|---|---|---|---|---|
| INSERT bench | 18.77s | 1m11.29s | 15.23s | 37.66s |
| SELECT bench | 3.10s | 0.01s | 1.14s | 0.14s |
| UPDATE bench | 3.69s | 0.12s | 1.32s | 0.22s |
| DELETE bench | 4.44s | 3.19s | 2.10s | 3.734 |
次の機会があれば、各処理が遅い原因や対応策についてもう少し深堀してみたいです。
参考サイト：
https://yakst.com/ja/posts/3841
https://www.s-style.co.jp/blog/2017/06/420/
https://docs.mongodb.com/manual/indexes/