MySQLのインストールとベンチマーク(sysbench)

MySQL

MySQLも気がつけば5.6ですね。(私がバリバリ使っていた時は4.1とか5.0の時代でした...).
現在ダウロードできる5.6, 5.5, 5.1 を切り替えてインストールできる環境の構築とそれぞれでベンチマークを取ってみることにします。

MySQLのダウンロードは MySQL :: Download MySQL Community Server ここですが、色々種類があります。
RPM版とかもありますが、私が毎回使っているのは

Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive (もしくは32bit版)

です。RPMだと各種ファイルが散らばってしまってあんまり好きじゃないですので毎度 TAR Archive でインストールしています。3バージョン以下のようにダウンロードしました。(ファイル名の命名とかバラバラやな...)

$ ls -ltr
合計 608308
-rw-rw-r-- 1 keisyu keisyu 304788904  2月 13 01:45 2014 mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz
-rw-rw-r-- 1 keisyu keisyu 184478489  2月 13 01:47 2014 mysql-5.5.36-linux2.6-x86_64.tar.gz
-rw-rw-r-- 1 keisyu keisyu 133630298  2月 13 01:48 2014 mysql-5.1.73-linux-x86_64-glibc23.tar.gz

install

mysqlユーザを追加します。

$ sudo groupadd mysql
$ sudo useradd -r -g mysql mysql

tar.gzを /usr/local 以下に展開します

$ sudo tar -zxvf mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

３バージョンとも展開しました。アクティブにたいバージョンをmysqlにシンボリックリンクします。

$ cd /usr/local
$ sudo ln -s mysql-5.6.16-linux-glibc2.5-x86_64 mysql
$ ls -l
...
lrwxrwxrwx   1 root root   34  2月 13 01:54 2014 mysql -> mysql-5.6.16-linux-glibc2.5-x86_64
drwxr-xr-x  13 root root 4096 11月  5 20:28 2013 mysql-5.1.73-linux-x86_64-glibc23
drwxr-xr-x  13 root root 4096  2月 13 01:52 2014 mysql-5.5.36-linux2.6-x86_64
drwxr-xr-x  13 root root 4096  2月 13 01:50 2014 mysql-5.6.16-linux-glibc2.5-x86_64

PATHに設定しときます (.zshrc とかにも書いとく)

$ export PATH=/usr/local/mysql/bin:$PATH

初期DBの作成をします

$ sudo ./scripts/mysql_install_db --user=mysql --datadir=/var/mysql/data56

たまにココで

Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

なんて怒られることありますが、その場合は libaio というライブラリをインストールしてください。

$ sudo yum install libaio

my.cnfと起動

my.cnf は書けば長くなることが多いですがひとまずは最低限の記述を定義しておき /etc/my.cnf に保存します

[mysqld]
character-set-server=utf8  #5.1では default-character-set=utf8
socket=/tmp/mysql.sock

起動します.

$ sudo /usr/local/mysql/bin/mysqld_safe --user=mysql --datadir=/var/mysql/data56 &

停止は mysqladmin で行います

$ sudo /usr/local/mysql/bin/mysqladmin shutdown

起動は service に登録すると便利です。

$ sudo cp support-files/mysql.server /etc/init.d/mysql  # ファイル内のdatadirの定義だけ追記しときます
$ sudo /sbin/service mysql start
$ sudo /sbin/service mysql stop
さらに必要に応じて
$ sudo chkconfig --add mysql
$ sudo chkconfig mysql on

接続確認

# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.16                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.5               |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

sysbench

sysbench ( System performance benchmark ) はベンチマークツールです。CPU,メモリ,I/Oのパフォマンスも計測できますがDataBaseのベンチマークも行うことができます。インストールは epel のレポジトリにありますので yum でインストール可能です。
ベンチマークをする際には以下のようなパラメータを指定します。

検査テーブルのレコード数
Readonlyのみ/ReadWrite
同時アクセス数(スレッド数)
計測時間
MySQL接続情報

等を指定します。例えば100万行の検査テーブルに対して60秒間,４スレッドをReadのみのパフォーマンスを計測する場合は以下のコマンドでまず準備をします。

sysbench \
 --test=oltp\
 --oltp-read-only=on\
 --db-driver=mysql\
 --mysql-user=root\
 --mysql-socket=/tmp/mysql.sock\
 --oltp-table-size=1000000\
 --num-threads=4\
 --max-requests=0\
 --max-time=60\
 prepare

最後の prepare の部分を以下に変更します。

prepare	初期化(検査前に１回だけ行う)
run	計測
cleanup	後始末(最後に行う)

runに変更して実行すると

sysbench 0.4.12:  multi-threaded system evaluation benchmark

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

Doing OLTP test.
Running mixed OLTP test
Doing read-only 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
Threads started!
Time limit exceeded, exiting...
(last message repeated 3 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            260260
        write:                           0
        other:                           37180
        total:                           297440
    transactions:                        18590  (309.81 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 260260 (4337.32 per sec.)
    other operations:                    37180  (619.62 per sec.)

Test execution summary:
    total time:                          60.0048s
    total number of events:              18590
    total time taken by event execution: 239.8965
    per-request statistics:
         min:                                  1.62ms
         avg:                                 12.90ms
         max:                                 76.98ms
         approx.  95 percentile:              19.17ms

Threads fairness:
    events (avg/stddev):           4647.5000/8.26
    execution time (avg/stddev):   59.9741/0.00

という風な結果が60秒後に表示されます。　309 TPS(transactions/sec) って部分の指標を結果としてよく使うと思います。

ベンチマーク (のまね事）

5.6, 5.5, 5.1 のMySQLでそれぞれ計測してみます。以下の点などを考慮していないのでまったくもって計測のまね事だということにご注意ください。

my.cnfの設定を全くしてなく全てデフォルト(Innodbのメモリ関連の値も全くチューニングなし)
sysbenchを動かすサーバがMySQLの稼働しているサーバと同じ
そもそもMacBookProのViertualBox上のCentOS

Threads=>	1	4	8	16
MySQL5.6	314	312	312	316
MySQL5.5	375	371	370	395
MySQL5.1	375	361	360	355

前述のとおりお遊びなのでこのデータを考察することの意味はありませんが
1スレッドですでにCPUがぶっ飛んでるからスレッド数を上げたところで意味なし... 5.5が一番TPS高いのはちょっと面白かった。
...
...
そうはいってもイマイチ面白みのなさすぎる結果だったのでVirtualBoxのCPU割り当てを8個にしてみた。