MySQLTunerを使ってMySQLを診断しよう！

DBのチューニングはインフラ環境、アプリのつくり等、その時の状況で調整する必要があります。今回はMySQLTunerというMySQLを診断してチューニングをアドバイスしてくれるツールを使って、実際にMySQLのチューニングを行いました。 MySQLTunerは警告項目に関してのみの情報は多いのですが、警告が出ていない項目に関する情報が少ないので、今回の診断結果をもとに詳しく解説していきます。

MySQLTunerのインストール

まずはMySQLTunerをインストールしましょう。 MySQLTunerはPerlで作成されており、無料で利用できます。インストールといっても、「サイトからzipファイルを取得して解凍するだけ！」といった至ってシンプルな作業です。以下、インストールのサンプルコマンドです。

# cd /usr/local/src/
# wget -O MySQLTuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip 
# unzip MySQLTuner.zip
# cd MySQLTuner-perl-master
# chmod +x mysqltuner.pl

MySQLTunerの診断結果

MySQLTunerを実行して、診断を行います。実行方法は「mysqltuner.pl」をそのままキックするだけです。今回診断した環境は以下のとおりです。

CPU: Intel(R) Xeon(R) CPU X5680 @ 3.33GHz
Memory: 2GByte　HDD:30GByte
OS: Red Hat 4.1.2　DB：MySQL5.5.27

# ./mysqltuner.pl
>> 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
[!!] Successfully authenticated with no password - SECURITY RISK!

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 324K (Tables: 5)
[--] Data in InnoDB tables: 3G (Tables: 68)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 16

-------- Security Recommendations  -------------------------------------------
[!!] 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@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 3h 1m 55s (474K q [2.584 qps], 1K conn, TX: 1B, RX: 1B)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 2.4G global + 20.4M per thread (400 max threads)
[!!] Maximum possible memory usage: 10.3G (525% of installed RAM)
[OK] Slow queries: 0% (45/474K)
[OK] Highest usage of available connections: 1% (7/400)
[OK] Key buffer size / total MyISAM indexes: 16.0M/256.0K
[OK] Key buffer hit rate: 99.9% (9K cached / 7 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 491 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 21K sorts)
[OK] Temporary tables created on disk: 1% (1 on disk / 76 total)
[OK] Thread cache hit rate: 99% (7 created / 1K connections)
[OK] Table cache hit rate: 82% (32 open / 39 opened) 
[OK] Open file limit used: 0% (11/2K)
[OK] Table locks acquired immediately: 100% (102K immediate / 102K locks)
[!!] InnoDB data size / buffer pool: 3.5G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 2M, or use smaller result sets)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 3G)

[!!] となっている項目がチューニングの必要な項目として表示されます。それでは各項目１つ１つについてどのような内容か、説明していきます。

General Statistics(一般統計)

Skipped version check for MySQLTuner script

MySQLTunerのバージョンチェックをスキップ。実行直後に「>> MySQLTuner 1.2.0 – Major Hayden 」とバージョンが表示されています。

Currently running supported MySQL version 5.5.27-log

現在のMySQLのバージョン情報

Operating on 64-bit architecture

OSが32bitか64bitなのかを表示

Storage Engine Statistics(ストレージ統計)

Status: -Archive -BDB -Federated +InnoDB -ISAM –NDBCluster

MySQLのストレージ構成がどのようなステータスかを表示しています。

Data in MyISAM tables: 324K (Tables: 5)

MyISAMの情報を表示しています。今回検証したMySQLではストレージ容量を「324KByte」利用し、5テーブルあることがわかります。

Data in InnoDB tables: 3G (Tables: 68)

InnoDBの情報を表示しています。今回検証したMySQLではストレージ容量を「3GByte」利用し、64テーブルあることがわかります。

Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

PERFORMANCE_SCHEMAの情報を表示しています。今回検証したMySQLではストレージ容量を「0Byte」利用し、17テーブルあることがわかります。（つまり全然使用していないということですね。）

Total fragmented tables: 16

断片化されたテーブル数が警告表示されます。今回検証した環境では16テーブルが断片化されています。 OPTIMIZE TABLE構文を使って、データファイルを最適化しましょう。

※OPTIMIZE TABLEの実行中はテーブルロックがかかるので稼働中のシステムに実行する場合は注意が必要です！

Security Recommendations(セキュリティ警告)

User {ユーザー名} has no password set.

ユーザーにパスワードが設定されていないことをセキュリティ警告として表示しています。常日頃、必ずパスワードを設定するよう心掛けましょう！

Performance Metrics(現在のパフォーマンスを表す指標)

Up for: 2d 3h 1m 55s (474K q [2.584 qps], 1K conn, TX: 1B, RX: 1B)

MySQLが起動してからの時間を表示しています。

Reads / Writes: 99% / 1%

MySQLTunerで測定した際の読込み/書込みの比率を表示します。合計で100%となります。

Total buffers: 2.4G global + 20.4M per thread (400 max threads)

全体の使用メモリと1スレッドあたりのメモリ消費量を表示しています。また最大スレッド数も表示されます。最大スレッド数はmy.cnfで設定した「max_connections」となります。

Maximum possible memory usage: 10.3G (525% of installed RAM)

稼働しているMySQLの最大使用メモリを表示しています。括弧に表示されたパーセントはサーバーのメモリとの対比です。今回の警告は現在のMySQL設定で使用する最大メモリは10.3G必要であり、サーバーのメモリが2Gしかないことで表示されています。サーバー自体のメモリを増設する必要があります。もしくは最大接続数を減らす、個々のメモリの割り当て量を減らすなどの対応も考えられます。

Slow queries: 0% (45/474K)

スロークエリが発生しているかを表示しています。ここに警告が出ていた場合、MySQLの「slowquery.log」を参照し、対象のSQLのチューニングを行いましょう！

Highest usage of available connections: 1% (7/400)

稼働しているMySQLのこれまでの最大接続数を表示しています。この値が高い場合はmy.cnfで「max_connections」を増やすなどのチューニングを行いましょう！

Key buffer size / total MyISAM indexes: 16.0M/256.0K

MyISAMのIndexに関するメモリ使用量を表示しています。この項目で警告が出る場合はmy.cnfで「key_buffer_size」を増やすチューニングを行いましょう！また、InnoDBのみ利用する場合はこの設定は不要です。

Key buffer hit rate: 99.9% (9K cached / 7 reads)

インデックスのヒット率を表示しています。ヒット率が90％を下回らないようにチューニングする必要があります。上記項目と同様でMyISAMを利用する場合に警告が表示される場合は、「key_buffer_size」のチューニングを行いましょう。

Query cache efficiency: 0.0% (0 cached / 473K selects)

クエリキャッシュの効率性を表示しています。今回検証したMySQLでは「query_cache_type = OFF」と設定しているため、「0 cached」で警告が表示されています。なぜ「query_cache_type = OFF」にしているかはここでは割愛します。

Query cache prunes per day: 0

1日あたりのクエリキャッシュが削除された回数を表示します。クエリキャッシュサイズが小さい場合、より多くのキャッシュを必要とした際に、キャッシュが削除されます。この項目で警告が表示される場合は、「query_cache」関連の設定を見直しましょう！

Sorts requiring temporary tables: 0% (0 temp sorts / 21K sorts)

ソートに必要なTempテーブルの利用状況を表示します。この項目で警告が表示される場合は「sort_buffer_size」の設定を見直しましょう！

Temporary tables created on disk: 1% (1 on disk / 76 total)

Tempテーブルの利用状況を表示します。この項目で警告が出る場合はOPTIMIZE TABLE構文を使って、データベースの最適化を行いましょう。それでも解消されない場合は「tmp_table_size」の設定を見直しましょう！

Thread cache hit rate: 99% (7 created / 1K connections)

スレッドキャッシュのヒット率を表示します。ヒット率が90％を下回らないようにチューニングする必要があります。警告が表示される場合は、「thread_cache _size」のチューニングを行いましょう。

Table cache hit rate: 11% (64 open / 536 opened)

テーブルキャッシュのヒット率を表示します。ヒット率が90％を下回らないようにチューニングする必要があります。警告が表示される場合は、「table_open_cache」のチューニングを行いましょう。

Open file limit used: 0% (11/2K)

MySQLで使用可能なファイル数の使用率を表示します。この項目で警告が表示される場合は「open_files_limit」の設定を見直しましょう！

Table locks acquired immediately: 100% (102K immediate / 102K locks)

テーブルロックがすぐに取得できたかどうかを表示しています。この項目に警告が表示される場合は、クエリを最適化し、次にテーブルを分割するかレプリケーションの活用を考える必要があります。また、MyISAMであれば、InnoDBテーブルへの移行を検討する必要があります。

InnoDB data size / buffer pool: 3.5G/2.0G

InnoDBのデータサイズと「innodb_buffer_pool_size」の値を表示しています。今回の検証したMySQLではInnoDBのデータサイズが3.5GByteあり、バッファサイズが2GByteのため、警告表示されています。検証環境のメモリが2GByteのため、サーバーのメモリを増設した上で、「innodb_buffer_pool_size」を3.5GByte以上に設定する必要があります。

Recommendations [勧告]

ここに表示されている内容を改善しましょう！といったMySQLTunerからの勧告となります。原則ここに表示された内容を改善できればチューニングOK！ということになります。

『General recommendations:』[全般的な改善勧告]

Run OPTIMIZE TABLE to defragment tables for better performance

「より良いパフォーマンスを得るためにOPTIMIZE TABLEを実行して、テーブルをデフラグしましょう！」と勧告されています。OPTIMIZE TABLEを実行しましょう。

Reduce your overall MySQL memory footprint for system stability

「システムの安定化のためには、MySQLが必要とする全メモリを少なくした方がいい」と表示されています。今回検証したサーバーではメモリ不足のはずなのですが。。。これはMySQLでメモリを大量に使用しているため、OSやその他アプリケーションに割り当てるメモリが少なくてシステムが安定しないことを示しています。データ量が多いのであれば、サーバーのスペック自体の見直しをしましょう！ということですね。

『Variables to adjust:』[MySQL設定関連の改善勧告]

ここに表示される内容がMySQLの設定アドバイスになります。この値で設定を見直しましょう！

*** MySQL’s maximum memory usage is dangerously high ***
                *** Add RAM before increasing MySQL buffer variables ***

「MySQLで使用しているメモリ量が高く、危険です。MySQLをチューニングする前にサーバーのメモリを増設してください。」と言われています。今回の検証結果では当然の判断ですね。。。 MySQLの設定について、実際の推奨値を表示してくれます。

  query_cache_limit (> 2M, or use smaller result sets)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 3G)

この内容に即して、設定を変更しましょう！ただ「query_cache_limit」を2MByte以上に設定しても、再度MySQLTunerで検証すると「query_cache_limit (> 8M)」といった結果になることもありますので、勧告が表示されなくなるまで設定の見直しが必要です。

また、「query_cache_limit」のみを変更するだけでなく、関連した設定も変更が必要となります。「table_cache」はMySQL5.1からは「table_open_cache」と設定値が変更されています。「innodb_buffer_pool_size」はサーバーのメモリを増設しなければ、設定しても意味をなさないので、今回は設定見直し対象外としました。

今回の診断結果から設定を見直した項目は以下のとおりです。

query_cache_limit = 128M　　　※変更前  2M
query_cache_size =  256M　　  ※変更前 64M
table_open_cache =  128　　　 ※変更前 64

また、OPTIMIZE TABLEで全テーブルへのデフラグを実施しました。その結果は以下のとおりです。（Recommendationsのみ表示。他割愛）

-------- Recommendations -----------------------------------------------------
General recommendations:
    Reduce your overall MySQL memory footprint for system stability
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_buffer_pool_size (>= 3G)

メモリ以外の勧告がすべてクリアされました。

MySQLTunerのまとめ

MySQLTunerはMySQLのチューニング診断に、とても有用なツールです。しかも無料！ただ、MySQLTunerからアドバイスされた項目のみを変更して、MySQLが動かなくなったということにならないようにチューニングには深い知識が必要です。なので、このツールに頼るのではなく、このツールをきっかけにより深い知識を身につけていく入り口として活用してはいかがでしょうか。