MySQLTunerをインストールしてMySQLチューニング
2017 年 6 月 17 日
前回までにhttpd.confやphp.iniである程度チューニングを行いましたが
mysqlもチューニングしなければと思い診断できるツールがあると知りインストールをしてみました。
ツールといってもplの拡張子ということでperlで作られたシェルスクリプトのようです。
まずplファイルをダウンロードします。
サイトについては、こちらになります。
https://github.com/major/MySQLTuner-perl
まずは、wgetでダウンロード
[root@www16071ue src]# pwd /usr/local/src [root@www16071ue src]# wget https://github.com/major/MySQLTuner-perl/archive/master.zip --2017-06-15 08:57:14-- https://github.com/major/MySQLTuner-perl/archive/master.zip github.com をDNSに問いあわせています... 192.30.255.113, 192.30.255.112 github.com|192.30.255.113|:443 に接続しています... 接続しました。 HTTP による接続要求を送信しました、応答を待っています... 302 Found 場所: https://codeload.github.com/major/MySQLTuner-perl/zip/master [続く] --2017-06-15 08:57:14-- https://codeload.github.com/major/MySQLTuner-perl/zip/master codeload.github.com をDNSに問いあわせています... 192.30.255.121, 192.30.255.120 codeload.github.com|192.30.255.121|:443 に接続しています... 接続しました。 HTTP による接続要求を送信しました、応答を待っています... 200 OK 長さ: 212283 (207K) [application/zip] `master.zip' に保存中 100%[======================================>] 212,283 419K/s 時間 0.5s 2017-06-10 08:57:16 (419 KB/s) - `master.zip' へ保存完了 [212283/212283] [root@www16071ue src]# ls -la 合計 216 drwxr-xr-x. 2 root root 4096 6月 10 08:57 2017 . drwxr-xr-x. 13 root root 4096 10月 11 03:30 2014 .. -rw-r--r-- 1 root root 212283 6月 10 08:57 2017 master.zip
zipファイルを解凍してから実行権を付与します。
[root@www16071ue src]# unzip master.zip Archive: master.zip 0de0df36fa8f18b02759cbd5acdfe46f2e97685c creating: MySQLTuner-perl-master/ inflating: MySQLTuner-perl-master/.gitignore inflating: MySQLTuner-perl-master/.perltidy inflating: MySQLTuner-perl-master/.travis.yml inflating: MySQLTuner-perl-master/CODE_OF_CONDUCT.md inflating: MySQLTuner-perl-master/CONTRIBUTING.md inflating: MySQLTuner-perl-master/INTERNALS.md inflating: MySQLTuner-perl-master/LICENSE inflating: MySQLTuner-perl-master/README.fr.md inflating: MySQLTuner-perl-master/README.it.md inflating: MySQLTuner-perl-master/README.md inflating: MySQLTuner-perl-master/README.ru.md inflating: MySQLTuner-perl-master/USAGE.md creating: MySQLTuner-perl-master/Vagrant/ inflating: MySQLTuner-perl-master/Vagrant/Vagrantfile_for_MariaDB10.0 inflating: MySQLTuner-perl-master/Vagrant/Vagrantfile_for_MariaDB10.1 inflating: MySQLTuner-perl-master/Vagrant/Vagrantfile_for_MySQL5-6 inflating: MySQLTuner-perl-master/Vagrant/Vagrantfile_for_MySQL5-7 inflating: MySQLTuner-perl-master/Vagrant/prepare.sh inflating: MySQLTuner-perl-master/basic_passwords.txt creating: MySQLTuner-perl-master/build/ inflating: MySQLTuner-perl-master/build/build_rpm.sh inflating: MySQLTuner-perl-master/build/clean.sh inflating: MySQLTuner-perl-master/build/mysqltuner.spec.tpl inflating: MySQLTuner-perl-master/build/updateCVElist.pl inflating: MySQLTuner-perl-master/build/updateStaff.sh inflating: MySQLTuner-perl-master/mysqltuner.pl inflating: MySQLTuner-perl-master/mysqltuner.png inflating: MySQLTuner-perl-master/template_example.tpl inflating: MySQLTuner-perl-master/vulnerabilities.csv [root@www16071ue src]# ls -la 合計 220 drwxr-xr-x. 3 root root 4096 6月 10 08:59 2017 . drwxr-xr-x. 13 root root 4096 10月 11 03:30 2014 .. drwxr-xr-x 4 root root 4096 5月 31 21:57 2017 MySQLTuner-perl-master -rw-r--r-- 1 root root 212283 6月 10 08:57 2017 master.zip [root@www16071ue src]# cd MySQLTuner-perl-master [root@www16071ue MySQLTuner-perl-master]# ls CODE_OF_CONDUCT.md README.fr.md USAGE.md mysqltuner.pl CONTRIBUTING.md README.it.md Vagrant mysqltuner.png INTERNALS.md README.md basic_passwords.txt template_example.tpl LICENSE README.ru.md build vulnerabilities.csv [root@www16071ue MySQLTuner-perl-master]# ls -la 合計 1220 drwxr-xr-x 4 root root 4096 5月 31 21:57 2017 . drwxr-xr-x. 3 root root 4096 6月 15 08:59 2017 .. -rw-r--r-- 1 root root 97 5月 31 21:57 2017 .gitignore -rw-r--r-- 1 root root 482 5月 31 21:57 2017 .perltidy -rw-r--r-- 1 root root 828 5月 31 21:57 2017 .travis.yml -rw-r--r-- 1 root root 1422 5月 31 21:57 2017 CODE_OF_CONDUCT.md -rw-r--r-- 1 root root 7951 5月 31 21:57 2017 CONTRIBUTING.md -rw-r--r-- 1 root root 13691 5月 31 21:57 2017 INTERNALS.md -rw-r--r-- 1 root root 35147 5月 31 21:57 2017 LICENSE -rw-r--r-- 1 root root 11948 5月 31 21:57 2017 README.fr.md -rw-r--r-- 1 root root 12231 5月 31 21:57 2017 README.it.md -rw-r--r-- 1 root root 11949 5月 31 21:57 2017 README.md -rw-r--r-- 1 root root 16464 5月 31 21:57 2017 README.ru.md -rw-r--r-- 1 root root 5241 5月 31 21:57 2017 USAGE.md drwxr-xr-x 2 root root 4096 5月 31 21:57 2017 Vagrant -rw-r--r-- 1 root root 3909 5月 31 21:57 2017 basic_passwords.txt drwxr-xr-x 2 root root 4096 5月 31 21:57 2017 build -rwxr-xr-x 1 root root 216532 5月 31 21:57 2017 mysqltuner.pl -rw-r--r-- 1 root root 78877 5月 31 21:57 2017 mysqltuner.png -rw-r--r-- 1 root root 1877 5月 31 21:57 2017 template_example.tpl -rwxr-xr-x 1 root root 781526 5月 31 21:57 2017 vulnerabilities.csv
あとは、mysqltuner.plが実行体なので直接このファイルを実行するだけのようです。
[root@www16071ue MySQLTuner-perl-master]# ./mysqltuner.pl -user root -pass データベースのパスワード
>> MySQLTuner 1.7.2 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.5.44
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(0B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[!!] Log file /var/log/mysql/error.log is empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[OK] /var/log/mysql/error.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHE MA
[--] Data in MyISAM tables: 21M (Tables: 26)
[--] Data in InnoDB tables: 113M (Tables: 41)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[!!] CVE-2015-4792(<= 5.5.45) : "Unspecified vulnerability in Oracle MySQL Server 5.5.45 and earlier a nd 5.6.26 and earlier allows remote authenticated users to affect availability via unknown vectors rel ated to Server : Partition
[!!] CVE-2015-4802(<= 5.5.45) : "Unspecified vulnerability in Oracle MySQL Server 5.5.45 and earlier a nd 5.6.26 and earlier allows remote authenticated users to affect availability via unknown vectors rel ated to Server : Partition
[!!] CVE-2015-4815(<= 5.5.45) : "Unspecified vulnerability in Oracle MySQL Server 5.5.45 and earlier a nd 5.6.26 and earlier allows remote authenticated users to affect availability via vectors related to Server : DDL."
[!!] CVE-2015-4816(<= 5.5.44) : "Unspecified vulnerability in Oracle MySQL Server 5.5.44 and earlier a llows remote authenticated users to affect availability via unknown vectors related to Server : InnoDB ."
[!!] CVE-2015-4819(<= 5.5.44) : "Unspecified vulnerability in Oracle MySQL Server 5.5.44 and earlier
[!!] CVE-2017-3600(<= 5.5.54) : "Vulnerability in the MySQL Server component of Oracle MySQL (subcompo nent: Client mysqldump). Supported versions that are affected are 5.5.54 and earlier
[--] False positive CVE(s) for MySQL and MariaDB 5.5.x can be found.
[--] Check careful each CVE for those particular versions
[!!] 60 CVE(s) found for your MySQL release.
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 7d 16h 7m 9s (3M q [5.473 qps], 20K conn, TX: 12G, RX: 432M)
[--] Reads / Writes: 88% / 12%
[--] Binary logging is disabled
[--] Physical Memory : 996.0M
[--] Max MySQL memory : 583.2M
[--] Other process memory: 459.0M
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 217.5M (21.84% of installed RAM)
[OK] Maximum possible memory usage: 583.2M (58.56% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (9/3M)
[OK] Highest usage of available connections: 11% (18/151)
[OK] Aborted connections: 0.03% (7/20728)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can red uce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 3M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (202 temp sorts / 879K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 39% (102K on disk / 262K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 77% (318 open / 412 opened)
[OK] Open file limit used: 24% (254/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 43.3% (3M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/6.0M
[OK] Read Key buffer hit rate: 100.0% (93M cached / 2K reads)
[!!] Write Key buffer hit rate: 92.8% (516K cached / 478K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[OK] InnoDB buffer pool / data size: 128.0M/113.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.8125 %): 5.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (21891719508 hits/ 21891720675 total)
[!!] InnoDB Write Log efficiency: 72.65% (29176 hits/ 40157 total)
[OK] InnoDB log waits: 0.00% (0 waits / 10981 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
60 CVE(s) found for your MySQL release. Consider upgrading your version !
Dedicate this server to your database for highest performance.
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Set thread_cache_size to 4 as a starting value
Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
performance_schema = OFF disable PFS
innodb_file_per_table=ON
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M)
if possible.
ずらずらとmysqlの設定についてメモリの使用量、ログの出力などさまざまな角度から調べています。
最後にRecommendations(勧告)ということで英語で色々と書かれています。
そのなかでまずはじめにGeneral recommendations:(一般的な推奨事項)という事項から
１ 60 CVE(s) found for your MySQL release. Consider upgrading your version !
60個の脆弱性が見つかったのでバージョンをアップグレードするように！！
２ Dedicate this server to your database for highest performance.
最高のパフォーマンスを得るには、このサーバーをデータベースに専用します。
３ Enable the slow query log to troubleshoot bad queries
不正なqueryのトラブルシューティングのためにslow querylogを有効にする。
４ When making adjustments, make tmp_table_size/max_heap_table_size equal
調整（チューニング）を行うときは、tmp_table_sizeとmax_heap_table_sizeは、同等に調整する。
５ Reduce your SELECT DISTINCT queries which have no LIMIT clause
重複している制限のない（limit句）クエリを減らすこと。
６ Set thread_cache_size to 4 as a starting value
スタート時のthread_cache_sizeを４に設定する。
７ Performance shouldn’t be activated for MySQL and MariaDB 5.5 and lower version
パフォーマンスは、mysqlとMariaDB5.5もしくは、それ以下のバージョンは、活性化させるべきじゃない。
（バージョンは、5.6以上にしないとだめ？）
以上で直訳ですがまずバージョンが古いようです。
また細部設定については、その下に書いてるのでこれを見直していかなければならないようです。
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
performance_schema = OFF disable PFS
innodb_file_per_table=ON
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M)
とりあえず、だめな所をひとつずつつぶしていきます。
次回に続く
*****************************************************************************
