「MySQL JSONカラム」と「MongoDB」の簡易ベンチマーク #MySQL

本稿は、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/