简介
MySQLTuner可以帮助你快速检查服务器的设计是否合理,并且定位那些明显有问题的错误配置.这个脚本还会检查计算机的最近使用情况并根据实际的使用提出可以提高性能的改进之处
他不会对系统造成破坏,也不会给系统造成明显负担
使用
在你放数据库的服务器上
wget mysqltuner.pl
chmod +x index.html
./index.html
输入用户名和密码(有访问服务器权限的)
结果
下面是部分结果,其中[!!]是你需要关注的,同时最下面的Recommendations是建议
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 25.2% (2M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/2.7M
[OK] Read Key buffer hit rate: 99.9% (7M cached / 6K reads)
[!!] Write Key buffer hit rate: 3.4% (2M cached / 74K writes)
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /usr/local/mysql/data/izwz93kacqfe8vm3e5xpjfz.err file
Control error line(s) into /usr/local/mysql/data/izwz93kacqfe8vm3e5xpjfz.err file
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (65535) variable
should be greater than table_open_cache (2000)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://bit.ly/2wgkDvS
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 2000)
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.