先日、「【MySQL】Disk IOが大きいSQLの内容を確認する」や「MySQLのスロークエリログをmysqldumpslowで分析する」という記事を書きましたが、そもそも設定とかサーバのスペックあってるの？
という疑問を抱いた際に使えるツール「MySQL Tuner」をご紹介します。
目次
MySQL Tunerとは
MySQL Tunerとは、簡単にいうと「MySQLのチューニング診断をしてくれるアプリケーション」です。
よくわかんないけど、ググってmy.cnf書いたけど自分の環境にあってるのかよくわかんない。
という方には是非一度お試しいただきたいアプリケーションですね。
基本的なパフォーマンスチューニング診断をわかりやすく表示してくれます。
MySQL Tuner
https://github.com/rackerhacker/MySQLTuner-perl
ライセンスはGNU GPLなので無料で利用可能です。
実際にMySQL Tunerを使ってみる
１．ダウンロードとインストール
調査したいDBのあるサーバ上でダウンロードします。
# cd /usr/local/src/ # wget -O mysqltuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip # unzip mysqltuner.zip # rm mysqltuner.zip # cd MySQLTuner-perl-master # chmod +x mysqltuner.pl
適当なディレクトリにダウンロードして、展開。（今回は/usr/local/src/）
その後、解凍して実行ファイルに実行権限つけてるだけ。
２．実行
そのまま実行ファイルをキックするだけ。
$ ../mysqltuner.pl --user root --pass <MySQL Pass>
>> MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.5.37-0ubuntu0.13.10.1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 761M (Tables: 114)
[--] Data in InnoDB tables: 13G (Tables: 778)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 17)
[!!] Total fragmented tables: 780
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 11h 24m 43s (531 q [0.004 qps], 182 conn, TX: 98K, RX: 36K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (7% of installed RAM)
[OK] Slow queries: 3% (19/531)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Key buffer size / total MyISAM indexes: 16.0M/115.3M
[!!] Key buffer hit rate: 35.3% (17 cached / 11 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 337 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4 sorts)
[OK] Temporary tables created on disk: 23% (55 on disk / 235 total)
[OK] Thread cache hit rate: 99% (1 created / 182 connections)
[!!] Table cache hit rate: 10% (400 open / 3K opened)
[OK] Open file limit used: 26% (273/1K)
[OK] Table locks acquired immediately: 100% (176 immediate / 176 locks)
[!!] InnoDB buffer pool / data size: 128.0M/14.0G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
key_buffer_size (> 115.3M)
query_cache_limit (> 1M, or use smaller result sets)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 13G)
MySQL Tunerの実行にはDBのID/PASSが必要です。（解析するので権限が必要になります。
その場合は、実行ファイルに引数として情報をつけてやればOK。
# ./mysqltuner.pl --user root --pass <MySQLのパスワード>
結果を見てチューニングしてみる
実行結果を確認してチューニングを行っていきましょう。
基本的には「[!!]」と表記のある部分を解決していけばいい感じですね。
上記の実行結果を見て改善していきましょう。
断片化
[!!] Total fragmented tables: 780
断片化してるテーブルが780もあるよ。。
これ開発環境だから放置してるけど、実際の環境ならパフォーマンス劣化の一因なのでしっかり対応していきましょう。
対応方法
OPTIMIZE TABLE構文を使って、データファイルを最適化しましょう。
MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 8.5.2.5 OPTIMIZE TABLE 構文
バッファサイズ・キャッシュヒット率
[!!] Key buffer size / total MyISAM indexes: 16.0M/115.3M [!!] Key buffer hit rate: 35.3% (17 cached / 11 reads) [!!] Table cache hit rate: 10% (400 open / 3K opened) [!!] InnoDB buffer pool / data size: 128.0M/14.0G
まず、key_buffer_sizeが小さいのでインデックスの効果を活かせてない感じ。
同様にバッファサイズが小さいので、クエリのキャッシュヒット率が低い。
次にテーブルキャッシュのヒット率も低い。
最後のもInnoDBのbuffer poolがデータに比べて小さくて乗らないよ。ってところですね。
対応方法
my.cnfでkey_buffer_sizeを上げましょう。（メモリと相談しながら）
[mysqld] key_buffer_size = 128M table_cache = 500 query_cache_limit = 2M innodb_buffer_pool_size = 16G
まとめ
設定する値とかよくわかんない。という方でも
-------- Recommendations -----------------------------------------------------
以降の値を確認しながら調節すればなんとなく良くなりそうですね。
が、サーバの物理スペックなどもあると思うので、そのあたりは慎重に変更をしましょう。