MySQL服务器运行各种状态值
|
1 |
mysql> show global status; |
查询MySQL服务器配置信息语句
|
1 |
mysql> show variables; |
一、慢查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> show variables like ‘%slow%‘; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | log_slow_queries | ON | | slow_launch_time | 2 | +------------------+-------+ mysql> show global status like ‘%slow%‘; +———————+——-+ | Variable_name | Value | +———————+——-+ | Slow_launch_threads | 0 | | Slow_queries | 4148 | +———————+——-+ |
配置中打开了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有4148个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内。
打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。
二、连接数
经常会遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：
|
1 2 3 4 5 6 |
mysql> show variables like ‘max_connections‘; +—————–+——-+ | Variable_name | Value | +—————–+——-+ | max_connections | 256 | +—————–+——-+ |
这台MySQL服务器最大连接数是256，然后查询一下服务器响应的最大连接数：
|
1 |
mysql> show global status like ‘Max_used_connections‘; |
MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是
|
1 |
Max_used_connections / max_connections * 100% ≈ 85% |
最大连接数占上限连接数的85％左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。
三、临时表
|
1 2 3 4 5 6 7 8 |
mysql> show global status like ‘created_tmp%‘; +————————-+———+ | Variable_name | Value | +————————-+———+ | Created_tmp_disk_tables | 21197 | | Created_tmp_files | 58 | | Created_tmp_tables | 1771587 | +————————-+———+ |
每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 1.20%，应该相当好了。我们再看一下MySQL服务器对临时表的配置：
|
1 2 3 4 5 6 7 |
mysql> show variables where Variable_name in (‘tmp_table_size‘, ‘max_heap_table_size‘); +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table_size | 536870912 | +———————+———–+ |
只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表。
四、进程使用情况
|
1 2 3 4 5 6 7 8 9 |
mysql> show global status like ‘Thread%‘; +——————-+——-+ | Variable_name | Value | +——————-+——-+ | Threads_cached | 46 | | Threads_connected | 2 | | Threads_created | 570 | | Threads_running | 1 | +——————-+——-+ |
如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器thread_cache_size配置：
|
1 2 3 4 5 6 |
mysql> show variables like ‘thread_cache_size‘; +——————-+——-+ | Variable_name | Value | +——————-+——-+ | thread_cache_size | 64 | +——————-+——-+ |
示例中的服务器还是挺健康的。
五、查询缓存(query cache)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> show global status like ‘qcache%‘; +————————-+———–+ | Variable_name | Value | +————————-+———–+ | Qcache_free_blocks | 22756 | | Qcache_free_memory | 76764704 | | Qcache_hits | 213028692 | | Qcache_inserts | 208894227 | | Qcache_lowmem_prunes | 4010916 | | Qcache_not_cached | 13385031 | | Qcache_queries_in_cache | 43560 | | Qcache_total_blocks | 111212 | +————————-+———–+ |
MySQL查询缓存变量解释：
Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory：缓存中的空闲内存。
Qcache_hits：每次查询在缓存中命中时就增大
Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。
Qcache_total_blocks：缓存中块的数量。
我们再查询一下服务器关于query_cache的配置：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> show variables like ‘query_cache%‘; +——————————+———–+ | Variable_name | Value | +——————————+———–+ | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 203423744 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +——————————+———–+ |
各字段的解释：
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小
query_cache_size：查询缓存大小
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服务器 查询缓存碎片率 ＝ 20.46％，查询缓存利用率 ＝ 62.26％，查询缓存命中率 ＝ 1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。
六、表锁情况
|
1 2 3 4 5 6 7 |
mysql> show global status like ‘table_locks%‘; +———————–+———–+ | Variable_name | Value | +———————–+———–+ | Table_locks_immediate | 490206328 | | Table_locks_waited | 2084912 | +———————–+———–+ |
Table_locks_immediate表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。示例中的服务器Table_locks_immediate / Table_locks_waited ＝ 235，MyISAM就足够了。
七、表扫描情况
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show global status like ‘handler_read%‘; +———————–+————-+ | Variable_name | Value | +———————–+————-+ | Handler_read_first | 5803750 | | Handler_read_key | 6049319850 | | Handler_read_next | 94440908210 | | Handler_read_prev | 34822001724 | | Handler_read_rnd | 405482605 | | Handler_read_rnd_next | 18912877839 | +———————–+————-+ |
调出服务器完成的查询请求次数：
|
1 2 3 4 5 6 |
mysql> show global status like ‘com_select‘; +—————+———–+ | Variable_name | Value | +—————+———–+ | Com_select | 222693559 | +—————+———–+ |
计算表扫描率：
表扫描率 ＝ Handler_read_rnd_next / Com_select
如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。
后记：
文中提到一些数字都是参考值，了解基本原理就可以，除了MySQL提供的各种status值外，操作系统的一些性能指标也很重要，比如常用的top,iostat等，尤其是iostat，现在的系统瓶颈一般都在磁盘IO上
转载请注明：PHP笔记 » MySQL性能优化方案大汇总
