利用mysqltuner工具对mysql数据库进行优化
mysqltuner工具使用，本工具建议定期运行,发现目前MYSQL数据库存在的问题及修改相关的参数
将下载的mysqltuner.pl文件上传到需要测试的mysql服务器中
根据提示输入管理员账号密码，得出建议：
报错
# ./mysqltuner.pl
>> MySQLTuner 1.6.9 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
which: no mysqladmin in (/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin)
[!!] Couldn't find mysqladmin in your $PATH. Is MySQL installed?
添加环境变量
# vi /etc/profile.d/mysql.sh
加入
export PATH=/opt/lampp/bin:$PATH
>> MySQLTuner 1.6.9 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root200
Please enter your MySQL administrative password:
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 5.0.67 is EOL software! Upgrade soon!
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 8G (Tables: 448)
[--] Data in InnoDB tables: 320K (Tables: 2)
[--] Data in MEMORY tables: 1M (Tables: 4)
[!!] Total fragmented tables: 42
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'em@%' hasn't specific host restriction.
[!!] User 'huhonggen@%' hasn't specific host restriction.
[!!] User 'rone@%' hasn't specific host restriction.
[!!] There is no basic password file list!
-------- CVE Security Recommendations ---------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -------------------------------------------------
[--] Up for: 13h 30m 47s (81M q [1K qps], 39K conn, TX: 1G, RX: 1G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Total buffers: 4.3G global + 48.3M per thread (1500 max threads)
[!!] Maximum reached memory usage: 31.4G (100.18% of installed RAM)
[!!] Maximum possible memory usage: 75.1G (239.73% of installed RAM)
[OK] Slow queries: 0% (144/81M)
[OK] Highest usage of available connections: 38% (574/1500)
[!!] Aborted connections: 6.73% (2688/39922)
[OK] Query cache efficiency: 63.4% (49M cached / 78M selects)
[!!] Query cache prunes per day: 149286
[OK] Sorts requiring temporary tables: 0% (97 temp sorts / 862K sorts)
[OK] Temporary tables created on disk: 13% (46K on disk / 347K total)
[OK] Thread cache hit rate: 98% (643 created / 39K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 13% (1K/7K)
[OK] Table locks acquired immediately: 98% (35M immediate / 36M locks)
-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.
-------- Performance schema --------------------------------------------------
[--] Performance schema is disabled.
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 40.7% (1B used / 4B cache)
[OK] Key buffer size / total MyISAM indexes: 4.0G/3.9G
[OK] Read Key buffer hit rate: 100.0% (1B cached / 373K reads)
[!!] Write Key buffer hit rate: 83.9% (2M cached / 344K writes)
-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/320.0K
[!!] InnoDB Used buffer: 0.78% (64 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 93.93% (201 hits/ 214 total)
[!!] InnoDB Write Log efficiency: 0% (-1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate unclosed connections and network issues
Increasing the query_cache size over 128M may reduce performance
Upgrade to MySQL 5.5+ to use asynchrone write
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
工具的下载及部署
解决环境依赖，因为工具是perl脚本开发的，需要perl脚本环境
# yun install -y perl*
下载脚本
在http://mysqltuner.com/下载major-MySQLTuner-perl-1.6.0-142-g42593c6.zip将下载的mysqltuner.pl文件上传到需要测试的mysql服务器中
# chmod +x mysqltuner.pl
执行脚本
# ./mysqltuner.pl根据提示输入管理员账号密码，得出建议：
报错
# ./mysqltuner.pl
>> MySQLTuner 1.6.9 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
which: no mysqladmin in (/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin)
[!!] Couldn't find mysqladmin in your $PATH. Is MySQL installed?
添加环境变量
# vi /etc/profile.d/mysql.sh
加入
export PATH=/opt/lampp/bin:$PATH
>> MySQLTuner 1.6.9 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root200
Please enter your MySQL administrative password:
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 5.0.67 is EOL software! Upgrade soon!
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 8G (Tables: 448)
[--] Data in InnoDB tables: 320K (Tables: 2)
[--] Data in MEMORY tables: 1M (Tables: 4)
[!!] Total fragmented tables: 42
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'em@%' hasn't specific host restriction.
[!!] User 'huhonggen@%' hasn't specific host restriction.
[!!] User 'rone@%' hasn't specific host restriction.
[!!] There is no basic password file list!
-------- CVE Security Recommendations ---------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -------------------------------------------------
[--] Up for: 13h 30m 47s (81M q [1K qps], 39K conn, TX: 1G, RX: 1G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Total buffers: 4.3G global + 48.3M per thread (1500 max threads)
[!!] Maximum reached memory usage: 31.4G (100.18% of installed RAM)
[!!] Maximum possible memory usage: 75.1G (239.73% of installed RAM)
[OK] Slow queries: 0% (144/81M)
[OK] Highest usage of available connections: 38% (574/1500)
[!!] Aborted connections: 6.73% (2688/39922)
[OK] Query cache efficiency: 63.4% (49M cached / 78M selects)
[!!] Query cache prunes per day: 149286
[OK] Sorts requiring temporary tables: 0% (97 temp sorts / 862K sorts)
[OK] Temporary tables created on disk: 13% (46K on disk / 347K total)
[OK] Thread cache hit rate: 98% (643 created / 39K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 13% (1K/7K)
[OK] Table locks acquired immediately: 98% (35M immediate / 36M locks)
-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.
-------- Performance schema --------------------------------------------------
[--] Performance schema is disabled.
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 40.7% (1B used / 4B cache)
[OK] Key buffer size / total MyISAM indexes: 4.0G/3.9G
[OK] Read Key buffer hit rate: 100.0% (1B cached / 373K reads)
[!!] Write Key buffer hit rate: 83.9% (2M cached / 344K writes)
-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/320.0K
[!!] InnoDB Used buffer: 0.78% (64 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 93.93% (201 hits/ 214 total)
[!!] InnoDB Write Log efficiency: 0% (-1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
针对给出的意见进行修改
-------- Recommendations -----------------------------------------------------General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate unclosed connections and network issues
Increasing the query_cache size over 128M may reduce performance
Upgrade to MySQL 5.5+ to use asynchrone write
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 128M) [see warning above]
注意：
浏览输出的结果，特别是末尾的 Recommendations ，里面一般会提到您需要在 my.cnf 修改的内容。修改 my.cnf 后记得重启 MySQL 。重启后再运行 MySQLTuner 检查。另外需要注意的是 MySQL 需要启动 24 小时候再运行 MySQLTuner ，不然有些内容会不准。
