MySQL 5.7 お試し編

MySQL 5.7が正式にGAとしてリリースされたようです。
というわけでどこが変わったのか、触って見つつ調査してみようと思います。
ちなみにインストール先は AWS の r3.xlarge (Amazon Linux) です。

インストール/セットアップ

Redhat6向けのyumリポジトリをインストールし、mysql-community-serverをインストール。

sudo rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
sudo yum install -y mysql-community-server
sudo service mysqld start

# 原因が把握できていないのですが、mysqlのディレクトリに初期DBが構築されていない事があるようです。
# その場合は、以前のバージョンであれば mysql_install_db を実行して初期DBを構築しますが、
# mysql5.7からは mysqld --initialize に変更されたようです。
mysqld --initialize --datadir=/var/lib/mysql

rootでログインする際の初期パスワードは、下記の様にmysqlのエラーログに出力されるようです。
ノーヒントだったので困った。5.6は .mysql_secret というファイルに保存されていたのに・・

2015-10-22T10:32:43.950929Z 1 [Note] A temporary password is generated for root@localhost: xxxxxxxxxxx

ログインできることを確認したらインストール完了です。
適当にSQLを実行した際、このようなエラーが表示された場合は、 validate_password プラグインが有効になっています。
プラグインを無効化するか、適切なパスワードをrootユーザに設定してください。
(root以外のユーザも、強度の高いパスワードの設定を求められて面倒な為、今回はプラグインを無効化しています。本番環境では有効にしておきましょう)

> alter user root@localhost identified by "xxxxxxxxx";

or

> uninstall plugin validate_password;

とりあえずベンチマーク

http://mysqlserverteam.com/whats-new-in-mysql-5-7-generally-available/

5.6から3倍速くなったぜ!と豪語しているので、
別環境に mysql 5.6 をインストールし、sysbench でベンチマークを取ってみました。

sudo yum install -y sysbench --enablerepo=epel
echo "CREATE DATABASE sbtest; GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@localhost IDENTIFIED BY 'sbtest';" | mysql -u root -p
sysbench --test=oltp --db-driver=mysql --mysql-password=sbtest prepare
sysbench --test=oltp --db-driver=mysql --mysql-password=sbtest run

ほどほどにメモリは割り当てています。

innodb_buffer_pool_size = 20G
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT
max_connections = 100
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

mysql 5.6の結果

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (234.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (4446.01 per sec.)
    other operations:                    20000  (468.00 per sec.)

Test execution summary:
    total time:                          42.7349s
    total number of events:              10000
    total time taken by event execution: 42.6917
    per-request statistics:
         min:                                  2.67ms
         avg:                                  4.27ms
         max:                                 17.84ms
         approx.  95 percentile:               5.02ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   42.6917/0.00

mysql 5.7

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (203.06 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (3858.12 per sec.)
    other operations:                    20000  (406.12 per sec.)

Test execution summary:
    total time:                          49.2467s
    total number of events:              10000
    total time taken by event execution: 49.2031
    per-request statistics:
         min:                                  2.97ms
         avg:                                  4.92ms
         max:                                 24.68ms
         approx.  95 percentile:               5.40ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   49.2031/0.00

んー、どちらかというと5.7の方がスループットが出ていないような・・
この程度の負荷だと誤差の範囲なのかもしれません。
あとはパラメータチューニングをもっと細かくやるべきなのかも。

JSONテーブルってどんな感じ?

個人的には結構大きいJSONの対応。
PostgreSQLでは既に似たような機能が実装されていたのですが、
RDBMSが複数あるのは微妙ですよね。というわけで採用したことはありませんでした。
そんな中、ついにMySQLに実装されたので、色々と試してみたいと思います。

mysql> create table json_test (id varchar(64) primary key, val json default null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into json_test values
    ->   ('key1', '{"id":1, "value":"hogehoge"}'),
    ->   ('key2', '{"id":2, "value":"fugafuga", "option":"optionvalue"}'),
    ->   ('key3', '{"id":3, "value":"foobar", "option":null}');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
[/bash]

これでテーブルとテストデータができあがりましたね。
色々とクエリを発行してみます。
一致検索、LIKE検索と、簡単な構文には対応している様子。


mysql> select * from json_test;
+------+---------------------------------------------------------+
| id   | val                                                     |
+------+---------------------------------------------------------+
| key1 | {"id": 1, "value": "hogehoge"}                          |
| key2 | {"id": 2, "value": "fugafuga", "option": "optionvalue"} |
| key3 | {"id": 3, "value": "foobar"}                            |
+------+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from json_test where json_extract(val, '$.id') = 1;
+------+--------------------------------+
| id   | val                            |
+------+--------------------------------+
| key1 | {"id": 1, "value": "hogehoge"} |
+------+--------------------------------+
1 row in set (0.00 sec)

mysql> select * from json_test where json_extract(val, '$.value') like '%hoge%';
+------+--------------------------------+
| id   | val                            |
+------+--------------------------------+
| key1 | {"id": 1, "value": "hogehoge"} |
+------+--------------------------------+
1 row in set (0.00 sec)

mysql> select * from json_test where json_extract(val, '$.option') is null;
+------+--------------------------------+
| id   | val                            |
+------+--------------------------------+
| key1 | {"id": 1, "value": "hogehoge"} |
+------+--------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract(val, '$.option') from json_test;
+-------------------------------+
| json_extract(val, '$.option') |
+-------------------------------+
| NULL                          |
| "optionvalue"                 |
| null                          |
+-------------------------------+
1 row in set (0.00 sec)

という感じで、なかなかそれっぽく動いているようです。
ただ、json内のフィールドとしてはnullとして保存されていても、SQLのnullとは評価されないようですね。。
ドキュメントを読んでもずばりな回答が得られなかったのですが、とりあえずjsonのnullにキャストするとnull値の検索が可能でした。

mysql> select * from json_test where val->"$.option" = cast('null' as json);
+------+----------------------------------------------+
| id   | val                                          |
+------+----------------------------------------------+
| key3 | {"id": 3, "value": "foobar", "option": null} |
+------+----------------------------------------------+
1 row in set (0.00 sec)

そしてjson用の独自構文。

mysql> select * from json_test where val->"$.id" = 2;
+------+---------------------------------------------------------+
| id   | val                                                     |
+------+---------------------------------------------------------+
| key2 | {"id": 2, "value": "fugafuga", "option": "optionvalue"} |
+------+---------------------------------------------------------+
1 row in set (0.00 sec)

sqlでアロー演算子は斬新すぎでは?
なんとか楽にフィールドにアクセスできないか、という事で生まれたのでしょうか・・・
まあ助かりますけど・・・

InnoDB周りの改善だったりとか、他にも色々とあるのですが、今日のエントリではこんなところで。