| mysql性能优化 |
|
mySQL 性能优化
mysql> show variables like ‘max_connections‘; +-----------------+-------+ | variable_name | value | +-----------------+-------+ | max_connections | 256 | +-----------------+-------+
这台mysql服务器最大连接数是256，然后查询一下服务器响应的最大连接数：
mysql> show global status like ‘max_used_connections‘;
mysql服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是
max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85％左右，如果发现比例在10%以下，mysql服务器连接数上限设置的过高了。 1.1 max_connections +-------------------+-------+ | 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 | +-------------------+-------+
示例中的服务器还是挺健康的。 2.1 thread_cache_size +---------------+-------+ | 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% <= 75％ 3.1.1 open_files +---------------+-------+ | variable_name | value | +---------------+-------+ | open_tables | 919 | | opened_tables | 1951 | +---------------+-------+
open_tables表示打开表的数量，opened_tables表示打开过的表数量，如果opened_tables数量过大，说明配置中table_cache( 5.1.3之后这个值叫做table_open_cache )值可能太小，我们查询一下服务器table_cache值：
mysql> show variables like ‘table_cache‘; +---------------+-------+ | variable_name | value | +---------------+-------+ | table_cache | 2048 | +---------------+-------+
比较合适的值为：
open_tables / opened_tables * 100% >= 85%
open_tables / table_cache * 100% <= 95%
3.2.1.1 open_tables +-------------------------+---------+ | 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服务器对临时表的配置：
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以下的临时表才能全部放内存，超过的就会用到硬盘临时表。 3.2.2.1 created_tmp_tables +-----------------------+-----------+ | 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就足够了。 3.2.3.1 table_locks_immediate +-----------------------+-------------+ | 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。 3.2.4.1 handler_read_rnd_next
mysql> show variables like ‘key_buffer_size‘;
+-----------------+------------+ | variable_name | value | +-----------------+------------+ | key_buffer_size | 536870912 | +-----------------+------------+
分配了 512mb 内存给 key_buffer_size ，我们再看一下 key_buffer_size 的使用情况：
mysql> show global status like ‘key_read%‘; +------------------------+-------------+ | variable_name | value | +------------------------+-------------+ | key_read_requests | 27813678764 | | key_reads | 6798830 | +------------------------+-------------+
一共有27813678764个索引读取请求，有6798830个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
key_cache_miss_rate ＝ key_reads / key_read_requests * 100%
比如上面的数据，key_cache_miss_rate为0.0244%，4000个索引读取请求才有一个直接读硬盘，已经很bt了，key_cache_miss_rate在0.1%以下都很好（每1000个请求有一个直接读硬盘），如果key_cache_miss_rate在0.01%以下的话，key_buffer_size分配的过多，可以适当减少。
【注意】key_read_buffer 默认值为 8M 。在专有的数据库服务器上，该值可设置为 RAM * 1/4
mysql服务器还提供了key_blocks_*参数：
mysql> show global status like ‘key_blocks_u%‘; +------------------------+-------------+ | variable_name | value | +------------------------+-------------+ | key_blocks_unused | 0 | | key_blocks_used | 413543 | +------------------------+-------------+
key_blocks_unused表示未使用的缓存簇(blocks)数，key_blocks_used表示曾经用到的最大的blocks数，比如这台服务器，所有的缓存都用到了，要么增加key_buffer_size，要么就是过渡索引了，把缓存占满了。比较理想的设置：
key_blocks_used / (key_blocks_unused + key_blocks_used) * 100% ≈ 80% 3.3.1 key_reads +-------------------+------------+ | variable_name | value | +-------------------+------------+ | sort_merge_passes | 29 | | sort_range | 37432840 | | sort_rows | 9178691532 | | sort_scan | 1860569 | +-------------------+------------+
另外，增加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/ 3.4.1 sort_buffer_size +-------------------------+-----------+ | 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会对缓存中的碎片进行整理，从而得到一个空闲块。
我们再查询一下服务器关于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：超过此大小的查询将不缓存
3.5.1 qcache_free_blocks +------------------+-------+ | 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打补丁：http://www.percona.com/docs/wiki/release:start，记得找对应的版本。 |
