影响MySQL性能的 5大配置参数

来源：http://www.crazycoder.cn/DataBase/Article79916.html

 

以下文章主要是对MySQL性能影响关系紧密 5大配置参数介绍我前几天在相关网站WebSite看见对MySQL性能影响关系紧密 5大配置参数资料觉得挺好就拿出来供大家分享望你能有所收获

()连接

连接通常来自Web服务器下面列出了些和连接有关参数以及该如何设置它们

1、max_connections

这是Web服务器允许最大连接数记住每个连接都要使用会话内存(有关会话内存文章后面有涉及)

2、max_packet_allowed

最大数据包大小通常等于你需要在个大块中返回最大数据集大小如果你在使用远程mysqldump那它值需要更大

3、aborted_connects

检查系统状态计数器确定其没有增长如果数量增长介绍说明客户端连接时遇到了

4、thread_cache_size

入站连接会在MySQL中创建个新线程MySQL中打开和关闭连接都很廉价速度也快它就没有象其它数据库如Oracle那么多持续连接了但线程预先创建并不会节约时间这就是为什么要MySQL线程缓存Cache原因了

如果在增长请密切注意创建线程让你线程缓存Cache更大对于2550或100thread_cache_size内存占用也不多

( 2)查询缓存Cache

( 3)临时表

内存速度是相当快因此我们希望所有排序操作都在内存中进行我们可以通过调整查询让结果集更小以实现内存排序或将变量设置得更大

tmp_table_size

max_heap_table_size

无论何时在MySQL中创建临时表它都会使用这两个变量最小值作为临界值除了在磁盘上构建临时表外还会创建许多会话这些会话会抢占有 限制资源因此最好是调整查询而不是将这些参数设置得更高同时需要注意是有BLOB或TEXT字段类型表将直接写入磁盘 深入浅出MySQL双向复制技术

( 4)会话内存

MySQL中每个会话都有其自己内存这个内存就是分配给SQL查询内存因此你想让它变得尽可能大以满足需要但你不得不平衡同时间数 据库内致性会话数量这里显得有点黑色艺术是MySQL是按需分配缓存Cache因此你不能只添加它们并乘以会话数量这样估算下来比MySQL典型 使用要大得多

最佳做法是启动MySQL连接所有会话然后继续关注顶级会话VIRT列mysqld行数目通常保持相对稳定这就是实际内存 总用量减去所有静态MySQL内存区域就得到了实际所有会话内存然后除以会话数量就得到平均值

1、read_buffer_size

缓存Cache连续扫描块这个缓存Cache是跨存储引擎不只是MyISAM表

2、sort_buffer_size

执行排序缓存Cache区大小最好将其设置为1M-2M然后在会话中设置为个特定查询设置更高值

3、join_buffer_size

执行联合查询分配缓存Cache区大小将其设置为1M-2M大小然后在每个会话中再单独按需设置

4、read_rnd_buffer_size

用于排序和order by操作最好将其设置为1M然后在会话中可以将其作为个会话变量设置为更大值

( 5)慢速查询日志

慢速查询日志是MySQL很有用个特性

1、log_slow_queries

MySQL参数中log_slow_queries参数在my.cnf文件中设置它将其设置为on默认情况下MySQL会将文件放到数据目录文件以“主机名-slow.log”形式命名但你在设置这个选项时候也可以为其指定个名字

2、long_query_time

默认值是10秒你可以动态设置它值从1到将其设置为on如果数据库启动了默认情况下日志将关闭截至5.1.21和安装了 Google补丁版本这个选项可以以微秒设置这是个了不起功能旦你消除了所有查询时间超过1秒查询介绍说明调整非常成功这样可以帮助 你在问题变大的前消除问题SQL

3、log_queries_not_using_indexes

开启这个选项是个不错主意它真实地记录了返回所有行查询

小结

我们介绍了MySQL参数 5大类设置平时我们般都很少碰它们在进行MySQL性能调优和故障诊断时这些参数还是非常有用

MySQL中缓存Cache查询包括两个解析查询计划以及返回数据集如果基础表数据或结构有变化将会使查询缓存Cache中项目无效

1、query_cache_min_res_unit

MySQL参数中query_cache_min_res_unit查询缓存Cache中块是以这个大小进行分配使用下面公式计算查询缓存Cache平均大小根据计算结果设置这个变量MySQL就会更有效地使用查询缓存Cache缓存Cache更多查询减少内存浪费

2、query_cache_size

这个参数设置查询缓存Cache总大小

3、query_cache_limit

这个参数告诉MySQL丢掉大于这个大小查询般大型查询还是比较少见如运行个批处理执行个大型报表统计因此那些大型结果集不应该填满查询缓存Cache
复制代码 代码如下:

qcache hit ratio = qcache_hits / (qcache_hits + com_select) 


使用 
复制代码 代码如下:

SQL> show status like ‘qcache%'; 
SQL> show status like ‘com_%'; 


找到这些变量 
复制代码 代码如下:

average query size = (query_cache_size – qcache_free_memory)/qcache_queries_in_cache 


使用 
复制代码 代码如下:

SQL> show variables like ‘query%'; 
qcache_* status variables you can get with: 
SQL> show status like ‘qcache%';