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


## MySQL

現在ダウンロードできる5.6, 5.5, 5.1 を切り替えてインストールできる環境の構築とそれぞれでベンチマークを取ってみることにします。

MySQLのダウンロードは http://dev.mysql.com/downloads/mysql/ ここですが、色々種類があります。
RPM版とかもありますが、私が毎回使っているのは

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

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

```sh
$ 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ユーザを追加します。

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

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

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

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

```sh
$ 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 とかにも書いとく)

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

初期DBの作成をします

```sh
$ 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 というライブラリをインストールしてください。

```sh
$ sudo yum install libaio
```

## my.cnfと起動

my.cnf は書けば長くなることが多いですがひとまずは最低限の記述を定義しておき <span style="color: #cc0000">/etc/my.cnf</span> に保存します

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

起動します. 

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

停止は mysqladmin で行います

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

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

```sh
$ 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 
```

接続確認

```sh
# 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](http://sysbench.sourceforge.net/) はベンチマークツールです。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でそれぞれ計測してみます。<span style="color: #cc0000">以下の点などを考慮していないのでまったくもって計測のまね事だということにご注意ください。</span>

- 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個にしてみた。

|Threads=>|1|4|8|16|
|:--:|--:|--:|--:|--:|
|MySQL5.6+1CPU|314|312|312|316|
|MySQL5.6+8CPU|128|244|185|196|
う～ん?