MySQLTuner の導入
ダウンロードはこちらから。wget コマンドで zip ファイルを入手しました。
最終更新が 2 年前と随分古いのですね。2 年の間で更新されてきた MySQL に対応できているのか少し不安ですけれども、そんなこと、感じ入っておりましても仕方がありません。チューニングでのポイントや MySQL の根本が変更されていない、成熟していると考えましてすすめることにいたします。
[root@oki2a24 ~]# # 作業場所に移動
[root@oki2a24 ~]# cd /tmp/
[root@oki2a24 tmp]# # MySQLTuner をダウンロード
[root@oki2a24 tmp]# wget https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip
--2013-05-09 22:21:22-- https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip
github.com をDNSに問いあわせています... 204.232.175.90
github.com|204.232.175.90|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: https://codeload.github.com/rackerhacker/MySQLTuner-perl/zip/master [続く]
--2013-05-09 22:21:23-- https://codeload.github.com/rackerhacker/MySQLTuner-perl/zip/master
codeload.github.com をDNSに問いあわせています... 204.232.175.86
codeload.github.com|204.232.175.86|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 特定できません [application/zip]
`master' に保存中
[ <=> ] 25,785 70.2K/s 時間 0.4s
2013-05-09 22:21:26 (70.2 KB/s) - `master' へ保存終了 [25785]
[root@oki2a24 MySQLTuner-perl-master]#
zip ファイルは master という名前でこれを解凍します。
[root@oki2a24 tmp]# ＃ MySQLTuner を解凍 [root@oki2a24 tmp]# unzip master Archive: master 71f14ea05358fe5094b99fbbd4cf62d4fa109731 creating: MySQLTuner-perl-master/ inflating: MySQLTuner-perl-master/LICENSE inflating: MySQLTuner-perl-master/README inflating: MySQLTuner-perl-master/mysqltuner.pl [root@oki2a24 tmp]#
「.pl」ファイルということは、Perl ですね！わたくし、全然馴染みがありません！実際に実行してみます。わくわくしますね！
そのまえに、移動します。
[root@oki2a24 tmp]# cd MySQLTuner-perl-master/
では、mysqltuner.pl を実行してみましょう。「perl」コマンドで実行ですね。データーベースのパフォーマンスチューニング診断をやってみます♪
実行するだけで結果が出るわけではなく、MySQL データベースに接続するための管理者権限のあるユーザーとパスワードの入力が求められました。
[root@oki2a24 MySQLTuner-perl-master]# perl mysqltuner.pl
>> 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 9M (Tables: 12)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 6
-------- Security Recommendations -------------------------------------------
[!!] User '@dti-vps-srv57' has no password set.
[!!] User '@localhost' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@dti-vps-srv57' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 17d 0h 30m 0s (5M q [3.468 qps], 77K conn, TX: 23B, RX: 717M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 24.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 429.8M (41% of installed RAM)
[OK] Slow queries: 0% (0/5M)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/5.0M
[OK] Key buffer hit rate: 100.0% (47M cached / 12K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 551K sorts)
[!!] Temporary tables created on disk: 30% (197K on disk / 659K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 53% (57 open / 106 opened)
[OK] Open file limit used: 6% (62/1K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
[root@oki2a24 MySQLTuner-perl-master]#
[!!] マークの付いている項目が、パフォーマンス向上のために見直すと良い箇所です。ちょっと抜き出してみます。
- [!!] Total fragmented tables: 6
→ 6 つのテーブルが断片化しているよ！ - [!!] User ‘@dti-vps-srv57’ has no password set.
[!!] User ‘@localhost’ has no password set.
[!!] User ‘root@127.0.0.1’ has no password set.
[!!] User ‘root@::1’ has no password set.
[!!] User ‘root@dti-vps-srv57’ has no password set.
→ パスワード設定されていないユーザーがあるよ！ - [!!] Query cache is disabled
→ Query cache が無効になっているよ！ - [!!] Thread cache is disabled
→ Thread cache が無効になっているよ！
なるほど。パフォーマンスだけではなく、セキュリティについても軽く診断していただけるのですね。そういえばと思い返してみますと、わたくしたちのこのブログのサーバー構築時は右も左も分からない状態でした。
そんな具合でしたから、MySQL セキュアインストールスクリプト（/usr/bin/mysql_secure_installation）を実行することで簡単に初期化できることを知リませんでした。それで、パスワードが設定されていないホストのユーザーがいるのですね。危ないです＞＜。
それはともかく、MySQL データベースのチューニング最初の一歩は、クエリーキャッシュ、そしてスレッドキャッシュから始めるとよさそうなのですね♪勉強になります♪
また、上記の見直すべき観点を実際に変更してみるために、どのパラメーターを修正するべきなのかも教えていただけます。
- query_cache_size (>= 8M)
→ query_cache_size を 8 M 以上に設定するといいよ！ - tmp_table_size (> 16M)
→ tmp_table_size を 16 M 超に設定するといいよ！ - max_heap_table_size (> 16M)
→ max_heap_table_size を 16 M 超に設定するといいよ！ - thread_cache_size (start at 4)
→ thread_cache_size をまずは 4 に設定するといいよ！
クエリーキャッシュに関係するのが query_cache_size、tmp_table_size、max_heap_table_size、スレッドキャッシュに関係するのが thread_cache_size でしょうか。そのように予想を立て、切り分けて個々に見ていけばよさそうです。
MySQLTuner は大変親切ですね♪別の投稿で実際にやってみようと思います。
MySQLTuner を使うにあたって参考にさせていただくサイト
先人の実施、チューニング取り組みのページです。参考になりますし、やる気になります。ありがとうございます。
- MySQL 5.5.10 ～パフォーマンスチューニング | グーフー WordPressのためのLinuxノート
- MySQLTunerでMySQLのチューニングを診断する方法 | ブログ | 株式会社イー・エージェンシー
おわりに
チューニングをやるべき！なのはわかっているのですけれども、ぱっと考えただけでも、測定用のアプリを入れて、現状のデータを収集し、分析して考察し、MySQL の設定を変更してみて、結果どうだったのかまた測定する、などなどきっちりやろうと思うとやるべきことが底無しでございます。
そのような意味では、こちらの連載ページが網羅的です。素晴らしい内容です。
ですがそんな弱気なことを考えてしまいますと、そもそもチューニングをやろうという気持ちが萎えてしまいます。
これでは、いけませんね♪
と、たまたま導入も簡単で MySQL のチューニングを大雑把にアドバイスしてくれる MySQLTuner の存在を知りました。
これを利用しない手はないと感じました。つまり、この MySQLTuner でアドバイスしていただいた項目の、1つでも2つでも勉強しよう、きっと、身につけますから、こう思ったのです。
これから、少しずつ取り組んでいきたいです。
以上です。
「MySQL チューニングしたいです！★MySQLTuner をきっかけに♪」への3件の返信
[…] ← 前へ […]
[…] MySQL チューニングしたいです！★MySQLTuner をきっかけに♪ | oki2a24 […]
[…] MySQL チューニングしたいです！★MySQLTuner をきっかけに♪ | oki2a24 […]