MYSQL学习总结（六）：my.cnf各参数配置及根据状态优化

#这里只说[mysqld]部分
Datadir = /data/mysql //mysql的数据目录
Skip-locking //减少出错机率
Key_buffer = 384M //索引缓冲区大小，后面会详细讲如何设置
Max_allowed_packet = 1M //网络传输中一次消息传输量的最大值
Table_cache = 512 //表缓冲区大小
Sort_buffer_size = 2M //查询排序时，缓冲区大小，后面会详细讲
Read_buffer_size=2M//查询操作所能使用的缓冲区大小
Read_rnd_buffer_size = 8M //随机查询操作所能作用的内存大小
Myisam_sort_buffer_size=64M //MyISAM表发生变化时重新排序所需的缓冲
Thread_cache_size = 8M //设置Tread Cache池中可以缓存的连接线程最大数量，内存4G以上配64或更大的值
Query_cache_size = 32M //查询缓冲区大小
Thread_concurrency = 8 //设置为逻辑服务器数量*2
Thread_stack = 256K //MYSQL每个线程的堆栈大小，默认值够大，当出现ERROR:1436 Thread stack overrun时，可提高该值
Skip-name-resolve //最好加上，去除连接过来的机器的dns解析，常出现ERROR:2013是由这个引起
Log-bin=mysql-bin //打开二进制日志
Log-slow-queries = /usr/local/mysql/var/slowquery.log //打开慢查询日志，可以判断哪些查询超时，及哪些没有索引的查询。慢查询时间默认是5秒，超过5秒的查询被记录下来。可以使用set long_query_time = 2来设置慢查询时间，同时也可使用mysqldumpslow对慢查询日志进行分析，如：分析出访问次数最多的20次语句，mysqldumpslow –s c –t 20 /usr/local/mysql/var/slowquery.log

#通过查看MYSQL状态，来修改各参数的值

#查看MYSQL状态主要使用show命令，一般查系统状态，及变量参数的值
如：查看目前系统有多少慢查询日志
Show global status like “%slow%”; //可以得出慢查询条数 slow_queries
查看目前慢查询设置情况
Show variables like “%slow%”; //可以得出slow_launch_time
#设置max_connections的值及查看
Show variables like “max_connections”;
Show global status like “max_used_connections”;
若 max_used_connections 快接近max_connections值，则需要增大max_connections值。Set max_connections = 256 (重启后失效，最好写进my.cnf中)

#设置key_buffer_size的值
Show global status like ”key_read%”; //查看key_buffer_size的使用情况

计算公式：Key_reads/ Key_read_requests < 0.1%最好，但若Key_reads/ Key_read_requests<0.01%的话，说明key_buffer_size分配太多了。

#查看进程使用情况
Show global status like “Thread%”;

如果Threads_created过大的话，说明MYSQL服务器一直在创建线程，这时可调整thread_cache_size的值。

#查看查询缓存（qurey_cache_size）设置情况
Show global status like “qcache%”;

Show variables like “query_cache%”; //查看query_cache配置情况

查询这里有三个指标：
查询缓存碎片率=Qcache_free_blocks/Qcache_total_blocks,如果超过20%，可以使用flush query cache整理缓存碎片。
查询缓存利用率=(query_cache_size-Qcache_free_memory)/query_cache_size,如果在25%以下的话，说明 query_cache_size设置过大。如果在80%以上而且Qcache_lowmem_prunes>50,则说明query_cache_size有点小。
查询缓存命中率=(Qcache_hits-Qcache_inserts)/Qcache_hits,这个只是给参考吧，如果太小，说明读写频繁，而且有碎片。

#查看文件打开数情况
Show global status like “open_files”;

Show variables like “open_files_limit”;

一般地，打开文件数/限制打开文件数应<75%.

综上面所述，平时我们应关注的就是以下参数：

Key_buffer = 384M //根据实际状态调整
max_connections = 256 //根据实际状态调整
Thread_cache_size = 64M //内存大于4G则配64或更大
Query_cache_size = 64M
Tmp_table_size = 256M //内存临时表最大值
Table_cache = 614 //访问表分配的内存，可降低IO
Skip-name-resolve //加上后避免访问超时等问