MySQL配置文件的优化

文本来自《构建高可用Linux服务器》第三版。

下面我们介绍一些对性能影响较大的参数
以下只列出my.cnf文件中[mysqld]段落中的内容，其他锻炼的内容对mysql的运行性能影响甚微。
[mysqld]组中包括了msyqld服务启动时的参数，它涉及的方面很多，其中有mysql的目录和文件、通信、网络、信息安全、内存管理、优化、查询缓存区，以及mysql日志设置等。

port = 3306
#mysqld服务运行时的端口号

socket = /tmp/mysql.sock
#socket文件在liunx环境下特有的，用户在linux环境下客户端连接可以不通过tcp/ip网络而直接使用unix socket连接mysql(基于安全因素考虑)

skip-external-locking
#避免mysql的外部锁定，减少除出错机率，增强稳定性

skip-name-resolve
#禁止mysql对外部连接进行DNS解析，使用这一选项可以消除mysql进行DNS解析的时间。
#但需要注意的是，如果开启该选项，则所有远程主机授权都要使用IP地址的方式了

back_log = 384
#back_log参数的值指出在MySQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。
#如果系统在短时间内有很多连接，则需要增大该参数的值，该参数的值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列的大小上有它自己的限制。
#如果试图把back_log设定的高于操作系统的限制是无效的。其默认值为50。对于linux系统而言，推荐设置为小于512的整数。

key_buffer_size = 384M
#指定用于索引的缓冲区大小，增加它可得到的更好的索引处理性能。
#对于内存在4GB左右的服务器来说，该参数可设置为256M或384M。不建议把该参数设置的过大，这样反而会降低服务器的整体效率。

max_allowed_packet = 4M
#指在网络传输中一次消息传输量的最大值。系统默认是1MB，最大值是1GB，必须设定为1024的倍数，单位是字节。

thread_stack = 256k
#设置mysql每个线程的堆栈大小，默认值足够大，可满足普通操作。可以设置范围128kb至4GB，默认192kb。

table_cache = 614k
#表高速缓存的大小。
#当mysql访问一个表时，如果在msyql表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区，这样做的好处是可更快速的访问表中的内容。
#一般来说，可以通过查看数据库运行峰值时间的状态值Open_tables和Opended_tables，用以判断是否需要增加table_cache的值
#即如果open_tables接近table_cache的时候，并且opened_tables这个值在逐步增加，那么就要考虑增加这个值的大小了

sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小，系统默认为2MB，从5.1.23版本开始，在除了Windows之外的64位平台可以超出4GB的限制。
#注意：该参数对应的分配内存是每个连接独占的，如果有100个连接，那么实际分配的总排序缓冲区大小为100x6MB=600MB。
#所以，对于内存在4GB左右的服务器来说，推荐将其设置为6~8MB

read_buffer_size = 4M
#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每个连接独占。

join_buffer_size = 8M
#指的是联合查询操作所能使用的缓冲区大小。该参数对应的分配内存也是每个连接独占

myisam_sort_buffer_size = 64M
#用于设置在REPAIR TABLE 或用 CREATE INDEX 创建索引 或ALTER TABLE 的过程中排序索引分配的缓冲区大小，可设置范围4MB~4GB，默认8MB

thread_cache_size = 64
#设置thread cache池中可以缓存的连接线程最大数量，可设置为0~16384，默认为0.
#这个值表示可以重新利用保存在缓存中线程的数量，当断开连接时，如果缓存中还有空间，那么客户端的线程讲被放到缓存中；
#如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。
#通过比较connections和threads_created状态的变量，可以看到这个变量的作用
#我们可以根据物理内存设置规则如下：1GB可以配置为8，2GB可以配置为16，4GB及以上可以给此值为64或更大

query_cache_size = 64M
#mysql查询缓冲区的大小

tmp_table_size = 256M
#设置内存临时表的最大值。如果超过该值，则会将临时表写入磁盘，其范围1KB~4GB

max_connections = 768
#指定mysql运行的最大连接进程数。如果进程出现"Too Many Connecitons"的错误，则需要增大该参数值

max_connect_errors = 1000
#设置每台主机的连接请求异常终端的最大次数，当超过该次数，mysql服务器将禁止host的连接请求，直到msyql服务器重启或者通过flush hosts命令清空此 host的相关信息
#可设置1~18446744073709547520，默认为10

wait_timeout = 10
#用于指定一个请求的最大连接时间，对于4GB内存的服务器来说，可以设置5~10

thread_concurrency = 8
#取值为服务器CPU数量的2倍。在本列中，服务器有2个物理CPU，都支持H.TC超线程，所以实际取值为4x2=8

table_cache = 1024
#物理内存越大，设置就越大。默认为2402，调用512~1024最佳

innodb_additional_mem_pool_size = 4M
#默认为2MB

innodb_flush_log_at_trx_commit = 1
#设置为0就是等到innodb_log_buffer_size队列满后在统一存储，默认为1

innodb_log_buffer_size = 2M
#默认为1M

innodb_thread_concurrency = 8
#服务器CPU有几个就设置为几，建议用默认设置，一般为8

read_rnd_buffer_size = 16M
#设置进行随机读的时候所使用的缓冲区大小。此参数和read_buffer_size所设置的相反，一个是顺序读的时候使用，一个是随机读的时候使用。
#注意：如果key_reads太大，则应该把my.cnf中的key_buffer_size变大，保持key_reads/key_read_requests至少在1/100以上，越小越好；
#如果qcache_lowmem_prunes很大，就要增加query_caceh_size的值

不过，很多时候需要具体情况具体分析，其他参数的变更我们可以等msyql上线稳定一段时间后根据status的值进行调整