| SLOW QUERIES |
慢查询检查 |
| SLOW QUERIESThe 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_connectionsper_thread_max_buffers
(read_buffer_size+read_rnd_buffer_size +sort_buffer_size+thread_stack
+join_buffer_size+binlog_cache_size)*max_used_connectionsglobal_buffers
innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+
key_buffer_size+query_cache_sizemax_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-3xthat 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 |
SELECTCOUNT(*) 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=285created_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 |
|