mysqlをtmpfsに乗せて速くする.md

mysqlをtmpfsに乗せて速くする

はじめに

こんにちは高知工科大Advent Calendar 2015の6日目担当です.
今日は大学生なら一度は経験する問題について書いていきます.

みなさんも一度はmysqlを使った時に遅くてイライラしたことがあると思います.

大量のデータにselect文投げたら15時間経過した後に結果が帰ってくることとかあるあるですよね.
今日はそんな問題を多少改善する方法についてです.

どうやったら速く出来るのか

速度を速くする方法として以下の様な方法が有ります.

• クエリをチューニングする
• スケールアップ, スケールアウトをする.
• cacheをチューニングする
• IOを速くする

どれも興味深い内容ですが今回はIOを速くすることに注目していきます.

mysqlのデータはcacheの上に乗っかってる

mysqlのデータベースのファイルはHDDの上に乗っかってます.
みなさんもご存知の通りHDDはCPUに比べるとかなり遅いですね.
本当はレジスタの上にすべてのデータを配置できれば速くなるんですがそんな贅沢なことは現実的に無理です.
HDDより速くてそれなりに容量があるものといえば・・・そう, メモリですね.
メモリの上にmysqlのデータベースファイルを全部のせてしまいましょう.

どうやって載せるか

Unixにはtmpfsというものが有ります.

$ df -lh
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda3        97G  5.9G   86G   7% /
tmpfs           499M     0  499M   0% /dev/shm
/dev/vda1       239M   53M  174M  24% /boot

tmpfsはメモリの上に乗っかった揮発性のFilesystemです.
windowsとかだとramdiskがそれに相当します.
揮発性のメモリにのってるため電源が落ちたりすると消えちゃいますが結構速いです.

tmpfsを設定する

tmpfsのマウントポイントを設定します.
今回は/mysql-datasに作ります.

$ sudo mkdir /mysql-datas

tmpfsの設定をしていきます.
viで以下のファイルを編集します.

$ sudo vi /etc/fstab

開くといろいろ項目がある中にtmpfsの項目を追加します.

tmpfs   /mysql-datas    tmpfs   defaults    0 0

/mysql-datasに搭載されている容量の半分の容量がtmpfsとして設定されます.
ここで一度サーバーを再起動します.
再起動するとこんな感じで追加されているはずですです.

$ df -lh
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda3        97G  5.9G   86G   7% /
tmpfs           499M     0  499M   0% /dev/shm
/dev/vda1       239M   53M  174M  24% /boot
tmpfs           499M     0  499M   0% /mysql-datas

mysqlの設定を変更していきましょう.
/etc/my.cnfを変更します.
datadir=/var/lib/mysqlがdefaultのデータディレクトリの設定です.
datadir=/mysql-datasに変更します.
変更したらmysqlを再起動しましょう.

$ sudo service mysqld restart

これでmysqlのtmpfs化は完了しました.
IOをベンチマークします.

まずはHDDの方です.

Test execution summary:
    total time:                          300.0003s
    total number of events:              581000
    total time taken by event execution: 38.8323
    per-request statistics:
         min:                                  0.00ms
         avg:                                  0.07ms
         max:                                 35.84ms
         approx.  95 percentile:               0.03ms

Threads fairness:
    events (avg/stddev):           581000.0000/0.00
    execution time (avg/stddev):   38.8323/0.00

tmpfsの方です.

Test execution summary:
    total time:                          300.0003s
    total number of events:              625800
    total time taken by event execution: 9.9635
    per-request statistics:
         min:                                  0.00ms
         avg:                                  0.02ms
         max:                                  2.30ms
         approx.  95 percentile:               0.03ms

Threads fairness:
    events (avg/stddev):           625800.0000/0.00
    execution time (avg/stddev):   9.9635/0.00

それなりに早くなりましたねー

終わりに

今回メモリに乗っけて早くしましたがメモリに直接のせるのも限界があるので 本当はクエリチューニングとかキャッシュチューニングして速度を上げたほうがいいです.
実際問題チューニングするときはちゃんと遅いところを計測してやりましょうね.
限界までチューニングしてどうしてもダメな時の最後の手段くらいに思っておいて下さい.