皆さんは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の場合は簡単にインストールできます。
これで準備完了です。非常に簡単でいいですね。
動かしてみる
それではMySQLTunerを動かしてみましょう。perl mysqltuner.pl
とコマンドを実行すると、以下のように、admin権限を持っているアカウントに関してコンソール上でダイアログに表示されるので、ダイアログにしたがって入力していきましょう。docker-compose.ymlを利用している場合は、rootとpasswordで実行できます。
今回はローカルホストに対して実行していますが、別のホストを指定したい場合は--host変数で追加することができます。
以下のようにチェックが進んでいることがわかります。
完了すると、最後の段落に以下のように推奨設定が表示されます。
General recommendations: には一般的な推奨事項が書かれています。簡単に意訳していくと、以下のようになります。
- MySQLが24時間以内に立ち上がったもので、正確性に欠ける可能性があります。
- システムの安定性のためにMySQLの全体的なメモリー使用量を減らすべきです。
- パフォーマンスを最大化したい場合はサーバーをデータベースに専念させたほうが良いです。
- MySQLへの接続の失敗や、ネットワーク上の問題を減らしましょう
- MySQL 8.0のinnnodb-redo-logのドキュメントを読んでからinnodb_log_file_sizeとinnodb_log_files_in_groupを変更しましょう
となっています。納得が行くものがあるのではないかなと思います。
続いて、Variables to adjust:の部分ですが、調整したほうが良いパラメータなどに関して書かれています。
- MySQLの最大メモリー使用量が危険なほど高い
- MySQLのバッファー変数を増やす前にまずはメモリーを増やしてください
- InnnoDBの全体のログファイルの大きさをbuffer poolサイズの25%にしたほうが良いので、もしできるならinnodb_log_file_sizeのサイズを16Mにしてください。
ここでは簡単に変更できるinnodb_log_file_sizeを変更してみます。
docker-compose.yml内のcommand行を以下のように変更して再起動を行ってみます。
変更前は以下のようになっていました。
これを以下のように変更して再起動します。
もう一度wgetでMySQLTunerを取得して、実行してみます。
このように、General recommendationsからinnodb_log_file_sizeの説明が消えて、さらにVariables to adjust:の内容が消えたことがわかります。メモリに関する警告が消えてしまったのは、docker-composeを再起動したためだと思われます。
今回説明した項目以外にも、MySQLTunerが指摘を行う場合があると思いますが、MySQLTunerのコードを読みながら、どうしてそういう説明が出てきたのか、追いかけてみるというのも勉強になるのでおすすめします。
脆弱性情報を確認する
インストール時に一緒にダウンロードしたファイルを使用して、現在のMySQLにある脆弱性を確認することができます。docker-composeではlatestを指定しているため、CVEが発見されませんでした。
5.7.13で試しに実行してみましょう。165件脆弱性があることがわかりました。
続いてせっかくなので、5.7.13のRecommendationsも見てみましょう。
MySQL 8.0では上手く動いてなかったユーザ回りの設定と、Variablesには8.0から消えたquery_cacheの設定が出力されています。General recommendationsで増えた部分を意訳すると、以下のようになります。
- rootユーザで接続できるホスト名を制限しなさい。
- UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='root' AND host ='%'; FLUSH PRIVILEGES;(制限する方法のSQL)
- root@% ユーザに、SET PASSWORD FOR 'root'@'%' = PASSWORD('secure_password')でセキュアなパスワードを設定しなさい
- 一人のユーザが、基本的な辞書に載っているような脆弱なパスワードを使用しています。
- 165個のCVEが、あなたの使用しているMySQLのバージョンで見つかりました。アップグレードの検討をしてください!
以上のようになります。ということで、5.7の方が少し豪華ですね。
まとめ
今回は、MySQLのパフォーマンスの足がかりとしてMySQLTunerに関して紹介をさせていただきました。このようなツールを足がかりに、MySQLにどういったパラメータがあるのか詳しく知っていくというのもありだと思います。
ただし注意をすることとしては、どんなパフォーマンスチューニングにも言えることですが、出てきた対策を盲目的に設定するのではなく、必ずそれがどういったパラメータでどういう影響があるのか確認をして設定をしましょう。できれば、検証環境などで挙動が本当に想定通りの動作になっているのか、想定している挙動でパフォーマンスが上がったのか下がったのか、必ず確認してから本番環境に適用してみると良いと思います。