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
-
タグ: コミュニケーション, コラム, ブログ, プロジェクト, 文化, 環境
2025年の熊手を買いに行きました。「応援し合い助け合い」を表す熊手はあったのか？｜公式note -
タグ: BigQuery, CDP, GA360, LookerStudio, googleアナリティクス360, コラム, データ可視化, データ統合活用, ホテル椿山荘東京, マーケティング施策, ユーザー会
GA360 ユーザー様のための懇親会「GA360 ユーザー会 2023」を4年ぶりに開催しました！ -
タグ: エンジニア採用, キャリア採用, コミュニケーション, コラム, ブログ, リモートワーク, ワーケーション, 文化, 環境
2024年12月の募集職種一覧！ エンジニア職からビジネス職まで採用強化中！｜株式会社イー・エージェンシー公式note -
タグ: エンジニア募集, オウンドメディア, コラム, ビジネス職, リモートワーク, レコメンドエンジン, 中途採用, 働きやすい環境, 採用強化, 社内文化, 翻訳ツール
【オープン社内報 2024年12月号】イー・エージェンシー プレミアムニュース -
タグ: AI活用, X（旧Twitter）, YouTube, claude, アルゴリズム, コラム, デジタルペルソナ, データ分析, メタ認知, 情報整理, 自己理解
猫よりAI 第3回：メタ認知のすゝめ 〜アルゴリズムで見える自分のペルソナ〜｜公式note -
タグ: ITツール活用, コミュニケーションデザイン, コラム, セキュリティ強化, チームサポート, パスキー, 働き方改革, 新技術導入, 業務効率化, 社内啓蒙, 社員教育
新技術導入で失敗しないコツ：効果的な社内啓蒙のすすめ｜公式note
