MySQL 参数调优工具--tuning-primer

 

最近发现了一款有意思的工具，tuning-primer.sh，它其实是一个脚本。下载地址：http://www.day32.com/MySQL/tuning-primer.sh 

这个脚本是通过 ”show statu like ...” 和 “show variables like ...” 获取信息，来生成一些服务器参数调整的建议。目前它支持以下内容：

• Slow Query Log
• Max Connections
• Worker Threads
• Key Buffer
• Query Cache
• Sort Buffer
• Joins
• Temp Tables
• Table (Open & Definition) Cache
• Table Locking
• Table Scans (read_buffer)
• Innodb Status

 

使用的方法：

chmod 755 ./tuning-primer.sh

./tuning-primer.sh

执行前需要将密码信息写入到配置文件中：

[client]

user = root

password= 1234

socket = /usr/local/mysql/mysql.sock

在我服务器上执行后的结果：

[root@bogon tools]# ./tuning-primer.sh
 
-- MYSQL PERFORMANCE TUNING PRIMER --
     - By: Matthew Montgomery -

MySQL Version 5.6.40-log x86_64

Uptime = 1 days 22 hrs 33 min 29 sec
Avg. qps = 0
Total Questions = 2226
Threads Connected = 1
 
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 2247 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commandsanually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/5.6/en/purge-master-logs.html
Binlog sync is not enabled, you could loose binlog records during a server crash

WORKER THREADS
Current thread_cache_size = 9
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 1
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

...

 

从上面的红色部分可以看到，我MySQL服务器有两个问题：

1.没有设置 expire_logs_days 参数

2.max_connections 参数可能过大

 

根据这个脚本的输出信息，能够清晰的看到MySQL的参数设置是否合理。再根据我们的需要来决定是否调整。

另外这个脚本已经很老了，我在github上找到了它的新版（不是原作者更新维护），地址是：https://github.com/BMDan/tuning-primer.sh