MySQLTunerでMySQLのチューニングを診断する方法

2012年12月20日 ｜ 八子 武司

https://github.com/rackerhacker/MySQLTuner-perl

MySQLTunerはMySQLのチューニングを診断してくれるアプリケーションです。
基本的なパフォーマンスチューニングのヒントをわかりやすく表示してくれます。
MySQLのチューニング設定に不安な方や、はじめてMySQLをさわる方は試してみると良いでしょう。
ライセンスはGNU GPLで無料で使えます。

検証環境

CentOS 6.3(64bit)
MySQL 5.5.28

MySQL 5.5をインストール

MySQL 5.5をインストールします。

# wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm 
# wget http://ftp.jaist.ac.jp/pub/Linux/Fedora/epel/6/x86_64/epel-release-6-7.noarch.rpm
# rpm -Uvh epel-release-6-7.noarch.rpm
# rpm -Uvh remi-release-6.rpm 
# yum --enablerepo=remi install mysql.x86_64 mysql-server.x86_64 mysql-devel.x86_64
# /usr/bin/mysql_install_db
# /etc/init.d/mysqld start
# /usr/bin/mysqladmin -u root password 'mypassword'

※rootパスワードは便宜設定しましょう
MySQLTunerがどれだけMySQLの設定を診断してくれるか知りたいので、あえて、MySQLの設定ファイル/etc/my.cnfをデフォルトで立ち上げています。

MySQLTunerのインストール

MySQLTunerをgithubからダウンロードして展開します。

# cd /usr/share/
# wget -O MySQLTuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip 
# unzip MySQLTuner.zip
# cd MySQLTuner-perl-master

MySQLTunerを実行する準備が出来ました。

MySQLTunerの実行

早速、MySQLTunerで診断をしてみましょう。

# ./mysqltuner.pl --user root --pass mypassword

このような結果が表示されました。

>> MySQLTuner 1.2.0 - Major Hayden 
>> 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

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.28
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '@localhost.localdomain' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost.localdomain' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 55s (14 q [0.255 qps], 7 conn, TX: 10K, RX: 855)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (31% of installed RAM)
[OK] Slow queries: 0% (0/14)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/97.0K
[!!] Key buffer hit rate: 0.0% (1 cached / 1 reads)
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 3 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 1% (18/1K)
[OK] Table locks acquired immediately: 100% (37 immediate / 37 locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)

[!!]と出ている行が警告で、チューニングの改善が必要な箇所が示されます。
Recommendations以下にそれぞれのオススメの設定を教えてくれます。
ひとつひとつ見ていきましょう。

警告１

[!!] InnoDB is enabled but isn't being used
→InnoDBストレージエンジンが有効だけど、使っていないよ
Add skip-innodb to MySQL configuration to disable InnoDB
→InnoDBストレージエンジンを使わないなら、skip-innodbオプションをつけて、InnoDBを無効化するといいよ

MySQL5.5のデフォルトのストレージエンジンはInnoDBです。
これから、InnoDBを使う場合には無視してよいでしょう。
特に理由がない場合には、このままInnoDBを使う事をオススメします。

InnoDBのかわりに、MyISAMなど別のストレージエンジンを使う場合には、/etc/my.cnfにInnoDBを無効化する記述を追記します。
これにより、メモリの節約が出来ます。

# vi /etc/my.cnf
[mysqld]
skip-innodb
default-storage-engine=MyISAM

警告２

[!!] User '@localhost' has no password set.
[!!] User '@localhost.localdomain' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost.localdomain' has no password set.
→パスワードが設定されていないユーザがあるよ

MySQLのユーザを整理します。

$ mysql -uroot -pmypassword
DELETE FROM mysql.user WHERE user='' or password='';
FLUSH PRIVILEGES;

警告３

[!!] Key buffer hit rate: 0.0% (1 cached / 1 reads)
→Key bufferのヒット率が悪いよ

まだ、運用をしていないので0.0%になっています。
Key bufferについては、InnoDBを使う場合には無視してよいでしょう。
MyISAMを運用するときにはkey_buffer_sizeをチューニングしましょう。

警告４

[!!] Query cache is disabled
→Query cacheが無効になっているよ
query_cache_size (>= 8M)
→query_cache_sizeを8M以上に設定するといいよ

Query cacheについては慎重に設定を考える必要があります。
もし、高負荷のMySQLを運用するならば設定をOFFにしましょう。
以下の記事が参考になります。

クエリキャッシュは切ったほうがいいんじゃなイカ？
http://dsas.blog.klab.org/archives/52021866.html

そもそもキャッシュヒット率が低い。20%以下なんてこともザラにある
しかもクエリキャッシュの更新はグローバルなロックを取得する

第2回 ioDrive+MySQL勉強会 @外道父 ioDriveの世界へようこそ
http://www.slideshare.net/GedowFather/welcome-to-iodrive-world

場合によりけりといってもOFFにした方が安定稼働が望めるよ

Query cacheをOFFにするには以下の記述を、/etc/my.cnfに設定します。

# vi /etc/my.cnf
[mysqld]
query_cache_type=0
query_cache_size=0

警告５

[!!] Thread cache is disabled
thread_cache 
→thread_cacheが無効になっているよ
thread_cache_size (start at 4)
→thread_cacheは最低4から設定するといいよ

thread_cacheはスレッドキャッシュの数です。
MySQLはクライアントからの接続ごとに新しいスレッドを生成し、そのあと破棄します。
このスレッドをすぐに破棄せず、キャッシュしておくことにより再接続時の負荷を軽減することができます。
大量の接続があるときには、この値を調整してパフォーマンスアップすることが望めます。

# vi /etc/my.cnf
[mysqld]
thread_cache=256

運用時にはキャッシュヒット率を確認しつつ、thread_cacheの値を調整すると良いでしょう。
キャッシュヒット率は以下のように確認ができます。

mysql> SHOW STATUS LIKE '%Threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.1/ja/server-status-variables.html

Threads_created
接続を処理で作成されたスレッドの数。Threads_created の値が大きい場合、thread_cache_size の値を大きくして キャッシュ サイズを増やす。キャッシュヒット率の計算は Threads_created/Connections とする。

公式サイトによると、キャッシュヒット率 = Threads_created/Connections となるそうです。

警告６

MySQL started within last 24 hours - recommendations may be inaccurate
→MySQLを24以内にスタートしたばかりなので、正確な診断結果ではない可能性があるよ

1日以上運用すると表示が出なくなります。
本格運用が始まったら、また、MySQLTunerを実行して診断をすると良いでしょう。

まとめ

最低限のチューニング設定をMySQLTunerで確認する事ができました。
MySQLのチューニングに不安がある方は試してみる価値があるでしょう。

今回は、インストールした直後のMySQLの診断を行いましたが、
新しくInnoDBのテーブルを作成するとinnodb_buffer_pool_sizeを設定するように促されます。
MySQLを実サービスに投入する前や、運用中にも、MySQLTunerの診断を実行していくと良いでしょう。

少しでも、MySQLのチューニングで苦労する人が減ると良いですね。

PICK UP

お問い合わせ

サービスに関するご相談は
こちらよりお気軽にお問い合わせください。

ISMS認証ロゴ（ANAB+BSI+ISMS-AC）

認証番号：IS836468
※民泊事業を含みません。

Copyright © e-Agency Group, Inc. All rights reserved.

e-Agency Group の様々な情報をSNSでお届けします!