Posted inMySQL

MySQLTunerでMySQL Serverの設定を診断・高速最適化する実践ガイド

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

昨日、チームの同僚からメッセージが来た。「MySQLサーバーが遅くてCPUが80%張り付いてるんだけど、どこから最適化すればいいか知ってる？」 — 何度も聞かれてきた、お馴染みの質問だ。いくつかのツールを試した後でも、結局MySQLTunerに戻ってくる。速くて、問題の核心を突いてくれて、深いDBAの経験がなくても使えるからだ。

実際の問題：デフォルトのMySQL設定はproductionに向いていない

初めてこの状況に直面したときのことを今でもよく覚えている。usersテーブルが1000万行を超えたとき、スロークエリが頻発し始め、ハードウェアにはまだ余裕があるのにサーバー負荷が上がり続けた。インデックスを最適化しなければならなかったが、それと同時に、MySQLのデフォルト設定が大量データには全く向いていないことに気づいた。

デフォルトの設定ファイルmy.cnfは、512MBのRAMしかない環境でも動くよう、あらゆるマシンで動作することを前提に設計されている。16GBのRAMを積んでいるのに、MySQLがInnoDBバッファプールに使えるのはたった128MB？これは極めて大きなリソースの無駄遣いだ。

ドキュメントを読んで各パラメータを自分で計算する代わりに、MySQLTunerがその面倒な作業を代わりにやってくれる。

MySQLTunerとは何か、どのように動作するか

MySQLTunerの本質はオープンソースのPerlスクリプトだ。実行中のMySQLに直接接続し、実際のランタイム統計を読み取り、問題のある設定項目のリストを、設定すべき具体的な数値とともに返してくれる。

よく使う理由はいくつかある：

  • 追加パッケージのインストールが不要 — PerlはほとんどのLinuxに標準搭載されている
  • 本番稼働中のサーバーで直接実行でき、実際の稼働統計を分析できる
  • 出力が見やすい：[OK]は緑で問題なし、[!!]は赤で要確認
  • 「増やすべき」という曖昧な表現ではなく、設定すべき具体的な値を提示してくれる

プロのDBAに取って代わるものではない。しかし、10〜15分MySQLTunerを実行するだけで、当日中に修正できる主要なボトルネックを2〜3個見つけられることが多い。

MySQLTunerのインストールと実行

方法1：GitHubから直接スクリプトをダウンロード

# MySQLTunerをダウンロード
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

# 実行権限を付与
chmod +x mysqltuner.pl

# MySQLの認証情報を指定して実行
perl mysqltuner.pl --user root --pass yourpassword

方法2：パッケージマネージャーでインストール（Ubuntu/Debian）

sudo apt install mysqltuner -y

# 直接実行
mysqltuner

より詳細な結果が欲しい場合は、--verboseフラグを追加する：

perl mysqltuner.pl --user root --pass yourpassword --verbose

重要な注意点：MySQLTunerは実際の稼働統計データを分析する。MySQLを再起動したばかりの場合、データが不十分なため結果が正確でない。実際の負荷下で少なくとも24〜48時間稼働させた後に実行するのが理想的だ。

MySQLTunerの結果の読み方

General Statisticsセクション

-------- General Statistics ------------------------------------------
[OK] Currently running supported MySQL version 8.0.32
[OK] Operating on 64-bit architecture
[!!] Uptime = 2d 3h 14m (should be > 7 days for accurate data)

[OK]の緑色の行は問題なし。[!!]の赤色の行は要注意。稼働時間が7日未満の場合、結果はあくまで参考程度に留めること。

Performance Metricsセクション

-------- Performance Metrics -----------------------------------------
[--] Reads / Writes: 84% / 16%
[!!] Thread cache is disabled
[!!] Table cache hit rate: 11% (400 open / 3K opened)
[OK] Temporary tables created on disk: 12% (2K on disk / 20K total)
[OK] Open file limit used: 0% (36/65K)

ここが最も重要なセクションだ。上の例を見ると、2つの問題がすぐに目に入る：

  • スレッドキャッシュが無効 — 新しい接続ごとにスレッドが生成されるため、短時間の接続が多いとリソースを大量消費する
  • テーブルキャッシュのヒット率がわずか11% — table_open_cacheが低すぎるため、MySQLがファイルディスクリプタを頻繁に開閉している

Recommendationsセクション — 最も重要な読み所

-------- Recommendations ---------------------------------------------
General recommendations:
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting point

Variables to adjust:
    thread_cache_size  (> 0)
    table_open_cache   (> 400)
    innodb_buffer_pool_size (>= 5G) if possible.
    innodb_log_file_size should be (= 512M)

具体的な数値が常に示されている — これをそのままmy.cnfにコピーする部分だ。

最適化の推奨設定を実際に適用する

何かを変更する前に、必ずconfigをバックアップする：

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf \
  /etc/mysql/mysql.conf.d/mysqld.cnf.bak.$(date +%Y%m%d)

echo "バックアップ完了、編集を開始します..."

InnoDBバッファプールの最適化 — 最重要項目

[mysqld]
# MySQLがサーバーのメインアプリの場合、RAMの70〜80%を設定する
# 16GB RAMのサーバー → 12Gに設定
innodb_buffer_pool_size = 12G

# バッファプールが1Gを超える場合、contentionを減らすために複数インスタンスを使用する
innodb_buffer_pool_instances = 4

# I/Oチェックポイントを減らすためにログファイルサイズを増やす
innodb_log_file_size = 512M

接続とキャッシュの最適化

[mysqld]
# スレッドキャッシュ — 接続ごとに新規作成せず、スレッドを再利用する
thread_cache_size = 16

# ファイルの頻繁な開閉を減らすためにテーブルキャッシュを増やす
table_open_cache = 2000
table_definition_cache = 1400

# RAMのテンポラリテーブルメモリ — ソート/グループ時のディスクI/Oを削減
tmp_table_size = 128M
max_heap_table_size = 128M

ファイルを保存したら、MySQLを再起動してサービスが正常に起動するか確認する：

sudo systemctl restart mysql
sudo systemctl status mysql

# 変数が適用されているか確認
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

十分な統計データを収集するために少なくとも48時間待ってから、再度MySQLTunerを実行して[!!]の数がどれだけ減ったか確認する。

注意事項

  • 闇雲に適用しない：MySQLTunerは現在の統計に基づいて提案する。トラフィックが異常だったり、サーバーを再起動したばかりの場合、結果が実態とずれる可能性がある。
  • 少しずつ変更する：10個のパラメータを一度に変更しない。まずInnoDBバッファプールを優先し、数日テストしてから他の設定に進む。
  • 変更後に監視する：SHOW STATUS LIKE 'Innodb_buffer_pool%'を使って実際の効果を確認する — 特にInnodb_buffer_pool_reads（低いほど良い）に注目。
  • innodb_buffer_pool_sizeが最重要：1つだけ変更するとしたらこれを変える。MySQLのパフォーマンスの大部分は、データをディスクから読み込む代わりにRAMに保持できるかどうかにかかっている。

まとめ

私がよく使うワークフロー：MySQLTunerを実行 → すべての[!!]行を記録 → それぞれの意味を調べる → 優先順位に従って適用（まずInnoDBバッファプール、次に接続設定）→ 48時間待つ → 再実行して進捗を確認。

MySQLTunerはすべての問題を解決してくれるわけではない — 設定を適切にチューニングしてもスロークエリが残る場合は、EXPLAINとクエリプロファイリングで個々のSQLを深掘りするのが次のステップだ。しかし、正しい設定は先に整えるべき土台であり、それなしではクエリの最適化も半分しか効果を発揮できない。

Share:
Tags: