MySQLTunerを使って簡単にデータベースチューニングを行なう
今回は、MySQLTunerを使って、簡単にデータベースチューニングをやってみます。
MySQLTunerはこちらにあります。
https://github.com/major/MySQLTuner-perl
githubを開くと分かりますが、スター数は、6,000を超えています。
readmeにも記載されていますが、リリースから今に至るまで、利用者が着実に増えてきているようです。
ダウンロードする
MySQLTunerをダウンロードします。
$ wget http://mysqltuner.pl/ -O mysqltuner.pl
................................................................................
................................................................................
................................................................................
2021-02-04 23:46:37 (17.7 MB/s) - `mysqltuner.pl' へ保存完了 [227610/227610]
$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
................................................................................
................................................................................
................................................................................
2021-02-04 23:46:43 (85.7 MB/s) - `basic_passwords.txt' へ保存完了 [3988/3988]
$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
--2021-02-04 23:46:47-- https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv
................................................................................
................................................................................
................................................................................
2021-02-04 23:46:48 (35.8 MB/s) - `vulnerabilities.csv' へ保存完了 [1185249/1185249]
ここまで終われば、ダウンロードは完了です。
MySQLTunerを実行する
$ perl mysqltuner.pl --host 127.0.0.1
>> MySQLTuner 1.7.21 - 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
[--] Performing tests on 127.0.0.1:3306
Please enter your MySQL administrative login: xxxxxxxxx
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 8.0.23
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /path/to/mysqld.log exists
[--] Log file: /path/to/mysqld.log(30K)
[OK] Log file /path/to/mysqld.log is not empty
[OK] Log file /path/to/mysqld.log is smaller than 32 Mb
[OK] Log file /path/to/mysqld.log is readable.
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8
-------- CVE Security Recommendations --------------------------------------------------------------
..................................................................................
..................................................................................
-------- Performance Metrics -----------------------------------------------------------------------
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
-------- Performance schema ------------------------------------------------------------------------
..................................................................................
..................................................................................
-------- ThreadPool Metrics ------------------------------------------------------------------------
..................................................................................
..................................................................................
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled on last MySQL versions.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
..................................................................................
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /path/to/mysqld.log file
Control error line(s) into /path/to/mysqld.log file
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `xxxxxxxxxx`.`xxxxxxxxxx`; -- can free xxx MB
Total freed space after theses OPTIMIZE TABLE : xxx Mb
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
innodb_buffer_pool_size (>= xxG) if possible.
innodb_buffer_pool_instances(=xx)
ログが流れて、上記のような結果になりました。
ポイントとしては2つあり、1つ目は、最適化できるテーブルがあるというメッセージがありました。
OPTIMIZE TABLE `xxxxxxxxxx`.`xxxxxxxxxx`; -- can free xxx MB
大きな支障がなければ、OPTIMIZEしてしまって大丈夫かと思いますので、最適化してしまいましょう。
2つ目は、innodbのパラメータチューニングに関わる記述がありました。
innodb_buffer_pool_size (>= xxG) if possible.
innodb_buffer_pool_instances(=xx)
記載のチューニングができると、パフォーマンスがかなり上がるそうです。ただ、innodb_buffer_pool_size (>= xxG) if possible. ここで推奨される値ですが、データベースのインデックスサイズ等を踏まえて、計算されており、実際にサーバに搭載しているメモリよりも大きい値が表示されることがたまにあります。その場合は、サーバスペックを上げるか、もしくは、テーブルインデックスの見直しなどを行なう必要がありそうです。