MySQL道普請便り

第137回 MySQLTunerを使ってチューニングの足がかりを見つけてみる

この記事を読むのに必要な時間:およそ 4.5 分

皆さんはMySQLのパラメータや変数をどのように設定しているでしょうか? MySQLやデータベースを運用する専用のチームの人たちがいて,秘伝のタレやノウハウが蓄積されて,その設定を使っているのかもしれません。しかし,そういったチームがなくMySQLのチューニングを行いたい場合に,何か手がかりが欲しいと思うこともあるでしょう。そんな場合に,簡単に使用できるMySQLのおすすめの設定を教えてくれる「MySQLTuner」を,今回は説明していきます。

検証環境

今回は,第125回 phpMyAdminでDockerで建てたMySQLにアクセスするで記載したdocker-composeを利用して作成します。手元で簡単に試せるように,GitHubの筆者のレポジトリにサンプルコードとして置いてあるので,気軽に試したい方はgit cloneして試してみてください。試すにはdockerとdocker-composeが必要です。前回までと比較をしてwgetを追加しているため,以前cloneした方はpullをして試してもらうか,追加でapt install wgetを行うかして進めてください。

MySQLTunerとは?

MySQLTunerはperlで作られた,MySQLのパフォーマンスと安定性を向上するための調整を提案してくれるツールです。現状の変数やステータスを読み取って,基本的なパフォーマンスの設定を簡単に提案してくれます。公式のGitHubはこちらになります。

MySQLTunerは,MySQLだけでなく,MySQLからForkして作られたMariaDBやPercona Server等の製品等もサポートされています。また,最新のMySQL 8では,パスワードのチェックが動かない模様です。また,対応バージョンとしては,8.0系と5.7系, 5.6系, 5.5系と現在主だって使われているものに関してはサポートがされています。それ以外のバージョンに関しては一部サポートがされていたりしなかったりするのですが,その辺りに関しては,GitHubのReadmeのCompatibilityに詳しく書かれているので,一度目を通して実行してみてください。

インストール方法

MySQLTunerは,前述の通りperlで作られたスクリプトのため,Linuxの場合は簡単にインストールできます。

$ docker-compose up -d
$ docker-compose exec db bash
# cd /tmp
# wget http://mysqltuner.pl/ -O mysqltuner.pl
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt  # passwordチェックをする場合に必要なファイルよくあるパスワード一覧が載ってます。
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv # MySQLの脆弱性情報がまとまっています。

これで準備完了です。非常に簡単でいいですね。

動かしてみる

それではMySQLTunerを動かしてみましょう。perl mysqltuner.plとコマンドを実行すると,以下のように,admin権限を持っているアカウントに関してコンソール上でダイアログに表示されるので,ダイアログにしたがって入力していきましょう。docker-compose.ymlを利用している場合は,rootとpasswordで実行できます。

今回はローカルホストに対して実行していますが,別のホストを指定したい場合は--host変数で追加することができます。

# perl mysqltuner.pl
 >>  MySQLTuner 1.7.20 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: 

以下のようにチェックが進んでいることがわかります。

[OK] Currently running supported MySQL version 8.0.22
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
 log_error is set to stderr MT can't read stderr
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 48.0K (Tables: 3)
[OK] Total fragmented tables: 0

~省略~

完了すると,最後の段落に以下のように推奨設定が表示されます。

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate unclosed connections and network issues
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

General recommendations: には一般的な推奨事項が書かれています。簡単に意訳していくと,以下のようになります。

  1. MySQLが24時間以内に立ち上がったもので,正確性に欠ける可能性があります。
  2. システムの安定性のためにMySQLの全体的なメモリー使用量を減らすべきです。
  3. パフォーマンスを最大化したい場合はサーバーをデータベースに専念させたほうが良いです。
  4. MySQLへの接続の失敗や,ネットワーク上の問題を減らしましょう
  5. MySQL 8.0のinnnodb-redo-logのドキュメントを読んでからinnodb_log_file_sizeとinnodb_log_files_in_groupを変更しましょう

となっています。納得が行くものがあるのではないかなと思います。

続いて,Variables to adjust:の部分ですが,調整したほうが良いパラメータなどに関して書かれています。

  1. MySQLの最大メモリー使用量が危険なほど高い
  2. MySQLのバッファー変数を増やす前にまずはメモリーを増やしてください
  3. InnnoDBの全体のログファイルの大きさをbuffer poolサイズの25%にしたほうが良いので,もしできるならinnodb_log_file_sizeのサイズを16Mにしてください。

ここでは簡単に変更できるinnodb_log_file_sizeを変更してみます。

docker-compose.yml内のcommand行を以下のように変更して再起動を行ってみます。

変更前は以下のようになっていました。

command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --secure-file-priv="/docker-entrypoint-initdb.d"

これを以下のように変更して再起動します。

command: mysqld --innodb-log-file-size=16M --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --secure-file-priv="/docker-entrypoint-initdb.d"

もう一度wgetでMySQLTunerを取得して,実行してみます。


-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate unclosed connections and network issues

このように,General recommendationsからinnodb_log_file_sizeの説明が消えて,さらにVariables to adjust:の内容が消えたことがわかります。メモリに関する警告が消えてしまったのは,docker-composeを再起動したためだと思われます。

今回説明した項目以外にも,MySQLTunerが指摘を行う場合があると思いますが,MySQLTunerのコードを読みながら,どうしてそういう説明が出てきたのか,追いかけてみるというのも勉強になるのでおすすめします。

脆弱性情報を確認する

インストール時に一緒にダウンロードしたファイルを使用して,現在のMySQLにある脆弱性を確認することができます。docker-composeではlatestを指定しているため,CVEが発見されませんでした。

# perl mysqltuner.pl --cvefile=vulnerabilities.csv --user=root --password=password

~省略~
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

~省略~

5.7.13で試しに実行してみましょう。165件脆弱性があることがわかりました。

~省略~
-------- CVE Security Recommendations --------------------------------------------------------------
 CVE-2016-3492(<= 5.7.14) : "Unspecified vulnerability in Oracle MySQL 5.5.51 and earlier
~省略~
 CVE-2018-3077(<= 5.7.22) : "Vulnerability in the MySQL Server component of Oracle MySQL (subcomponent: Server: DDL). Supported versions that are affected are 5.7.22 and prior and 8.0.11 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.0 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.0/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)."
 165 CVE(s) found for your MySQL release.
~省略~

続いてせっかくなので,5.7.13のRecommendationsも見てみましょう。

General recommendations:
    Restrict Host for 'root'@% to root@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='root' AND host ='%'; FLUSH PRIVILEGES;
    Set up a Secure Password for root@% user: SET PASSWORD FOR 'root'@'%' = PASSWORD('secure_password');
    1 user(s) used basic or weak password from basic dictionary.
    165 CVE(s) found for your MySQL release. Consider upgrading your version !
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)

MySQL 8.0では上手く動いてなかったユーザ回りの設定と,Variablesには8.0から消えたquery_cacheの設定が出力されています。General recommendationsで増えた部分を意訳すると,以下のようになります。

  1. rootユーザで接続できるホスト名を制限しなさい。
  2. UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='root' AND host ='%'; FLUSH PRIVILEGES;(制限する方法のSQL)
  3. root@% ユーザに,SET PASSWORD FOR 'root'@'%' = PASSWORD('secure_password')でセキュアなパスワードを設定しなさい
  4. 一人のユーザが,基本的な辞書に載っているような脆弱なパスワードを使用しています。
  5. 165個のCVEが,あなたの使用しているMySQLのバージョンで見つかりました。アップグレードの検討をしてください!

以上のようになります。ということで,5.7の方が少し豪華ですね。

まとめ

今回は,MySQLのパフォーマンスの足がかりとしてMySQLTunerに関して紹介をさせていただきました。このようなツールを足がかりに,MySQLにどういったパラメータがあるのか詳しく知っていくというのもありだと思います。

ただし注意をすることとしては,どんなパフォーマンスチューニングにも言えることですが,出てきた対策を盲目的に設定するのではなく,必ずそれがどういったパラメータでどういう影響があるのか確認をして設定をしましょう。できれば,検証環境などで挙動が本当に想定通りの動作になっているのか,想定している挙動でパフォーマンスが上がったのか下がったのか,必ず確認してから本番環境に適用してみると良いと思います。

著者プロフィール

木村浩一郎(きむらこういちろう)

株式会社オプティム 技術統括本部のエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter:@kk2170