MySQL 配置文件优化

454luikty

MySQL 配置文件优化


查看MySQL服务器配置信息：
show variables;

查看MySQL服务器运行的各种状态值：
show global status;

1. 慢查询
show variables like '%slow%';
show global status like '%slow%';
mysqldumpslow -s c -t 20 host -slow.log
分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内，如果你需要微秒级别的慢查询，可以考虑给MySQL打补丁：http://www.percona.com/docs/wiki/release:start，记得找对应的版本。

2. 连接数
经常会遇见”MySQL: ERROR 1040: Too manyconnections” 的情况：
一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力。
一种情况是MySQL配置文件中max_connections值过小。
查看最大连接数：
show variables like 'max_connections';

查看mysql服务器过去的最大连接数：
show global status like 'max_used_connections';
理想值：max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

3. key_buffer_size
key_buffer_size是对MyISAM表性能影响最大的一个参数，不过数据库中多为Innodb
查看key_buffer_size设置大小：
show variables like 'key_buffer_size';

查看key_buffer_size使用情况：
show global status like 'key_read%';
计算索引未命中缓存的概率：key_cache_miss_rate = Key_reads / Key_read_requests * 100%
key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘)，如果key_cache_miss_rate在0.01%以下的话，key_buffer_size分配的过多，可以适当减少。

4. key_blocks_*参数
show global status like 'key_blocks_u%';
Key_blocks_unused：表示未使用的缓存簇(blocks)数
Key_blocks_used：表示曾经用到的最大的blocks数
理想值：Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

5. 临时表
当执行语句时，关于已经被创造了的隐含临时表的数量，查看命令：
show global status like 'created_tmp%';
每次创建表时Created_tmp_tables 都会增加，如果在磁盘上创建，Created_tmp_disk_tables也会增加，Created_tmp_files表示服务器创建的临时文件数
理想值：Created_tmp_disk_tables / Created_tmp_tables * 100% ≤25%

查看服务器对临时表的配置：
show variables where variable_name in('tmp_table_size','max_heap_table_size');

6.打开表的情况
show global status like 'open%tables%';
open_tables 表示打开表的数量，opened_tables表示打开过的表数量，如果opened_tables数量过大，说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小

查询服务器table_cache值：
show variables like 'table_open_cache';
理想值：open_tables / opened_tables * 100% ≥ 85%
理想值：open_tables / table_cache * 100% ≤95%

7. 进程使用情况
如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数：
show global status like 'thread%';
如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置：
show variables like 'thread_cache_size';

8. 查询缓存(query cache)
查看服务器query_cache配置情况：
show variables like 'query_cache%';
参数解释：
query_cache_limit：超过此大小的查询将不缓存。
query_cache_min_res_unit：缓存块的最小值。
query_cache_size：查询缓存大小。
query_cache_type：缓存类型，决定缓存什么样的查询。
query_cache_wlock_invalidate：表示当前客户端，正在对MyISAM表进行写操作时，读请求是要等SRITE LOCK释放资源后再查询，还是允许直接从Query Cache中读取结果，默认为OFF（可以直接从Query Cache 中读取结果）。

查看服务器query_cache使用情况：
show global status like 'qcache%';
参数解释：
Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory：缓存中的空闲内存。
Qcache_hits：每次查询在缓存中命中时就增大
Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字不断增长，表示可能碎片非常严重，或内存很少。
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。
Qcache_total_blocks：缓存中块的数量。

9. 排序使用情况
它表示系统中对数据进行排序时所使用Buffer，查看命令：
show global status like 'sort%';
增大sort_buffer_size 会减少Sort_merge_passes和创建临时文件的次数，但盲目增加并不一定能提高速度。

10. 文件打开数
当open_files大于open_files_limit值时，mysql数据库就会发生卡住的现象，导致web服务器打开不响应的页面。
查看open_files命令：
show global status like 'open_files';

查看open_files_limit命令：
show variables like 'open_files_limit';
理想值：open_files / open_files_limit *100% ≤ 75%

11.Innodb_buffer_pool_size 的合理设置
show status like 'Innodb_bufferpool_%';

根据实际运行场景进行调整
read 命中率:
(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) /  Innodb_buffer_pool_read_requests = ?
write 命中率:
Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total