简介：
 
该脚本使用 "SHOW STATUS LIKE..." 和 "SHOW VARIABLES LIKE..." 命令获得MySQL相关变量和运行状态。然后根据推荐的调优参数对当前的MySQL数据库进行测试。最后根据不同颜色的标识来提醒用户需要注意的各个参数设置。该版本兼容 MySQL 3.23 和更高版本（包含 5.1）
 
当前版本会处理如下这些推荐的参数：
 
 Slow Query Log （慢查询日志） 
 Max Connections （最大连接数） 
 Worker Threads （工作线程） 
 Key Buffer （Key 缓冲） 
 Query Cache （查询缓存） 
 Sort Buffer （排序缓存） 
 Joins （连接） 
 Temp Tables （临时表） 
 Table (Open & Definition) Cache （表缓存） 
 Table Locking （表锁定） 
 Table Scans (read_buffer) （表扫描，读缓冲） 
 Innodb Status （Innodb 状态）
 
下载使用：
 
http://www.day32.com/MySQL/tuning-primer.sh 
 
tuning-primer.sh 有如下运行参数: 
Usage: ./tuning-primer.sh [ mode ]
 
all                    运行所有检测(默认值) 
prompt              提示 
mem, memory     运行有关内存使用方面的检测 
disk, file            运行有关I/O性能和文件处理限制方面的检测 
innodb              运行InnoDB检测 
misc                 其它
 
脚本支持my.cnf文件，可以把user, password, host, socket等连接参数放在~/.my.cnf文件里，如果使用了自定义的socket文件，请修改tuning-primer.sh文件中关于socket文件位置的变量。
 
运行 tuning-primer.sh
 
SLOW QUERIES
 
慢查询检查
 
SLOW QUERIES
 
The slow query log is enabled.
 
说明我已经启用了慢查询记录功能。也就是参数 
slow_query_log = 1
 
Current long_query_time = 5.000000 sec.
 
慢查询的阀值时间。也就是参数 
long_query_time = 5
 
You have 17 out of 638844 that take longer than 5.000000 sec. to complete
 
说明慢查询日志中记录了17条查询时间超过5秒的语句。 
slow_query_log_file=/data/ats_db/mysql-slow.log
 
设置慢查询日志路径。使用
 
mysqldumpslow命令查询慢日志
 
Your long_query_time seems to be fine
 
慢查询阀值时间设置得在推荐的范围内
 
BINARY UPDATE LOG
 
更新二进制日志文件
 
The binary update log is enabled
 
这项说明启用了bin-log日志功能。参数 
log-bin = /data/ats_db/mysql-bin
 
Binlog sync is not enabled, you could loose binlog records during a server crash
 
没有启用 sync_binlog 选项。也即是将二进制日志实时写入到磁盘通过 sync_binlog=1来指定
 
WORKER THREADS
 
工作线程
 
Current thread_cache_size = 8
 
当前线程缓存大小。 
thread_concurrency = 8
 
Current threads_cached = 7
 
Show status like 'threads_cached'
 
Current threads_per_sec = 0
 
脚本先执行Show status like 'Threads_cached'查看当前的线程创建情况，然后sleep 1后在执行相同的命令，最终后者减去前者的数就是每秒线程创建数。
 
Historic threads_per_sec = 0
 
该值是使用Threads_cached /uptime获得的。
 
Your thread_cache_size is fine
 
MAX CONNECTIONS
 
最大连接数
 
Current max_connections = 1024
 
当前配置文件中设置的并发连接数
 
Current threads_connected = 2
 
当前线程连接诶数。 
show status like 'Threads_connected'
 
Historic max_used_connections = 4
 
show status like 'Max_used_connections';
 
The number of used connections is 0% of the configured maximum.
 
这个值使用 Max_used_connections*100/ max_connections得出。
 
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
 
Max_used_connections的值不足max_connections值的10%。设置合适的max_connections值有助于节省内存。
 
MEMORY USAGE
 
内存使用
 
Max Memory Ever Allocated : 841 M
 
Max Memory Ever Allocated = max_memory
 
Configured Max Per-thread Buffers : 28.40 G
 
Configured Max Per-thread Buffers = per_thread_buffers
 
Configured Max Global Buffers : 586 M
 
Configured Max Global Buffers = per_thread_max_buffers
 
Configured Max Memory Limit : 28.97 G
 
Configured Max Memory Limit = total_memory 
这一项很重要，他是将各个缓存的大小累加，然后同max_connections相乘，从而得出当达到max_connections后需要分配的内存有多少。我这里由于max_connections写得很大，造成了最大内存限制超过了真实内存很多，所以建议不要随意增大max_connections的值。减小 max_connections的值，最终保证最大内存限制在真实内存的90%以下。
 
Physical Memory : 7.79 G
 
实际物理内存
 
Max memory limit exceeds 90% of physical memory
 
per_thread_buffers 
(read_buffer_size+read_rnd_buffer_size +sort_buffer_size+thread_stack+ 
join_buffer_size+binlog_cache_size)*max_connections
 
per_thread_max_buffers 
(read_buffer_size+read_rnd_buffer_size +sort_buffer_size+thread_stack 
+join_buffer_size+binlog_cache_size)*max_used_connections
 
global_buffers 
innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+ 
key_buffer_size+query_cache_size
 
max_memory=global_buffers+per_thread_max_buffers 
 
total_memory=global_buffers+per_thread_buffers
 
KEY BUFFER
 
Key 缓冲
 
Current MyISAM index space = 222 K
 
当前数据库MyISAM表中索引占用磁盘空间
 
Current key_buffer_size = 512 M
 
MySQL配置文件中key_buffer_size 设置的大小
 
Key cache miss rate is 1 : 3316
 
Key_read_requests/ Key_reads 这里说明3316次读取请求中有1次丢失(也就是说1次读取磁盘)
 
Key buffer free ratio = 81 %
 
key_blocks_unused * key_cache_block_size / key_buffer_size * 100
 
Your key_buffer_size seems to be fine
 
QUERY CACHE
 
Query 缓存
 
Query cache is enabled
 
该项说明 我们指定了query_cache_size 的值。如果query_cache_size=0的话这里给出的提示是： 
Query cache is supported but not enabled 
Perhaps you should set the query_cache_size
 
Current query_cache_size = 64 M
 
当前系统query_cache_size 值大小 [F]
 
Current query_cache_used = 1 M
 
query_cache_used =query_cache_size-qcache_free_memory
 
Current query_cache_limit = 128 M
 
变量 query_cache_limit 大小
 
Current Query cache Memory fill ratio = 1.79 %
 
query_cache_used/query_cache_size *100%
 
Current query_cache_min_res_unit = 4 K
 
show variables like 'query_cache_min_res_unit';
 
Your query_cache_size seems to be too high. 
Perhaps you can use these resources elsewhere
 
这项给出的结论是query_cache_size的值设置的有些过高。其比对标准是 "Query cache Memory fill ratio"的值如果小于<25%就会给出这个提示。可以将这些资源应用到其他的地方
 
MySQL won't cache query results that are larger than query_cache_limit in size
 
MySQL不会将大于query_cache_limit的查询结果进行缓存
 
show status like 'Qcache%';
 
Qcache_free_blocks        10        
Qcache_free_memory        65891984     
Qcache_hits            14437        
Qcache_inserts            707         
Qcache_lowmem_prunes    0        
Qcache_not_cached        216         
Qcache_queries_in_cache    540        
Qcache_total_blocks        1191
 
SORT OPERATIONS
 
SORT 选项
 
Current sort_buffer_size = 6 M
 
show variables like 'sort_buffer%';
 
Current read_rnd_buffer_size = 16 M
 
show variables like 'read_rnd_buffer_size%';
 
Sort buffer seems to be fine
 
JOINS
 
JOINS
 
Current join_buffer_size = 132.00 K
 
show variables like 'join_buffer_size%';
 
join_buffer_size= join_buffer_size+4kb
 
You have had 6 queries where a join could not use an index properly
 
这里的6是通过 show status like 'Select_full_join'; 获得的
 
You should enable "log-queries-not-using-indexes" 
Then look for non indexed joins in the slow query log. 
If you are unable to optimize your queries you may want to increase your 
join_buffer_size to accommodate larger joins in one pass.
 
Note! This script will still suggest raising the join_buffer_size when 
ANY joins not using indexes are found.
 
你需要启用 "log-queries-not-using-indexes" 然后在慢查询日志中看是否有取消索引的joins语句。如果不优化查询语句的话，则需要增大join_buffer_size。
 
OPEN FILES LIMIT
 
文件打开数限制
 
Current open_files_limit = 1234 files
 
show variables like 'open_files_limit%';
 
The open_files_limit should typically be set to at least 2x-3x
 
that of table_cache if you have heavy MyISAM usage.
 
如果系统中有很多的MyISAM类型的表，则建议将open_files_limit 设置为2X~3X的table_open_cache 
show status like 'Open_files';
 
open_files_ratio= open_files*100/open_files_limit 
如果open_files_ratio 超过75% 则需要加大open_files_limit
 
Your open_files_limit value seems to be fine
 
TABLE CACHE
 
TABLE 缓存
 
Current table_open_cache = 512 tables
 
show variables like 'table_open_cache';
 
Current table_definition_cache = 256 tables
 
show variables like ' table_definition_cache ';
 
You have a total of 368 tables
 
SELECT
 
COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
 
You have 371 open tables.
 
show status like 'Open_tables';
 
The table_cache value seems to be fine
 
Open_tables /table_open_cache*100% < 95%
 
You should probably increase your table_definition_cache value.
 
table_cache_hit_rate =open_tables*100/opened_tables
 
TEMP TABLES
 
临时表
 
Current max_heap_table_size = 16 M
 
show variables like 'max_heap_table_size';
 
Current tmp_table_size = 16 M
 
show variables like 'tmp_table_size';
 
Of 285 temp tables, 11% were created on disk
 
Created_tmp_tables=285
 
created_tmp_disk_tables*100/ 
(created_tmp_tables+created_tmp_disk_tables)=11%
 
Created disk tmp tables ratio seems fine
 
TABLE SCANS
 
扫描表
 
Current read_buffer_size = 6 M
 
show variables like 'read_buffer_size';
 
Current table scan ratio = 9 : 1
 
read_rnd_next =show global status like 'Handler_read_rnd_next'; 
com_select= show global status like 'Com_select'; 
full_table_scans=read_rnd_next/com_select 
Current table scan ratio = full_table_scans : 1" 
如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。
 
read_buffer_size seems to be fine
 
TABLE LOCKING
 
TABLE LOCKING
 
Current Lock Wait ratio = 0 : 5617
 
show global status like
 
'Table_locks_waited'; 
show global status like
 
'Questions'; 
如果 Table_locks_waited=0 
Current Lock Wait ratio = 0: Questions
 
Your table locking seems to be fine