根据status信息对MySQL服务器进行优化-2

chinaab

　　续http://xqtesting.blog.51cto.com/4626073/901707，直入主题。
　　六、进程使用情况

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配置：

mysql> show variables like 'thread_cache_size';　　
+-------------------+-------+
　　
| Variable_name     | Value |
　　
+-------------------+-------+
　　
| thread_cache_size | 64    |
　　
+-------------------+-------+

　　示例中的服务器还是挺健康的。
　　七、查询缓存(query cache)

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的配置：

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％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。
　　八、排序使用情况

mysql> show global status like 'sort%';　　
+-------------------+------------+
　　
| Variable_name     | Value      |
　　
+-------------------+------------+
　　
| Sort_merge_passes | 29         |
　　
| Sort_range        | 37432840   |
　　
| Sort_rows         | 9178691532 |
　　
| Sort_scan         | 1860569    |
　　
+-------------------+------------+

　　Sort_merge_passes 包括两步。MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量 Sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，等 MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。实际上，MySQL 会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort_merge_passes 增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 Sort_buffer_size 会减少 Sort_merge_passes 和 创建临时文件的次数。但盲目的增加 Sort_buffer_size 并不一定能提高速度，见 How fast can you sort data with MySQL?（引自http://qroom.blogspot.com/2007/09/mysql-select-sort.html，貌似被墙）
　　另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的好处，参见：http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/
　　九、文件打开数(open_files)

mysql> show global status like 'open_files';　　
+---------------+-------+
　　
| Variable_name | Value |
　　
+---------------+-------+
　　
| Open_files    | 1410  |
　　
+---------------+-------+
　　
mysql> show variables like 'open_files_limit';
　　
+------------------+-------+
　　
| Variable_name    | Value |
　　
+------------------+-------+
　　
| open_files_limit | 4590  |
　　
+------------------+-------+

　　比较合适的设置：Open_files / open_files_limit * 100%  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就足够了。
　　十一、表扫描情况

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 |
　　
+-----------------------+-------------+

　　各字段解释参见http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html，调出服务器完成的查询请求次数：

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上，关于iostat的使用，可以参考：http://www.php-oa.com/2009/02/03/iostat.html