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 <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 -------- 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
-
タグ: コミュニケーション, コラム, ブログ, リモートワーク, ワーケーション, 文化, 環境
有楽町 森の本社オフィスのVR紹介記事がメディアに掲載されました！｜公式note -
タグ: CART RECOVERY, カゴ落ち対策, カートリカバリー, クラウドサービス, コラム, ニュース
ECサイトとは？ 役割と必要な機能・サイトの種類についてわかりやすく解説 -
タグ: ECサイト, キャンペーン, コラム, ニュース, レコメンドエンジン
さぶみっと！レコメンド、ECサイト構築サービス「makeshop byGMO」で公式アプリ提供開始 -
タグ: CART RECOVERY, カゴ落ち対策, カートリカバリー, クラウドサービス, コラム, ニュース
カゴ落ち率と機会損失を分析！ ECサイトのカート離脱を改善する方法 -
タグ: CART RECOVERY, カゴ落ち対策, カートリカバリー, クラウドサービス, コラム, ニュース
「カゴ落ち」とは？ ECサイトで商品が売れない原因と対策について紹介 -
タグ: Webサイト多言語変換サービス, shutto翻訳, インバウンド, コラム
shutto翻訳、4/24-26開催Japan IT Week 春「デジタルマーケティング EXPO」出展！
