MySQL 配置讲解

sher

　　MySQL 配置讲解
　　MySQL安装好后，我们是从安装包的support-files里面复制过来一个末班配置文件，默认MySQL配置文件是在/etc/my.cnf下，其实这个路径或者文件名字我们是可以修改的，在启动脚本中修改。
　　以下是一些常用的设置：
　　[mysqld]
　　socket          = /tmp/mysql.sock
　　#为MySQL客户程序与服务器之间的本地通信制定一个套接字文件（Linux下默认是/var/lib/mysql/mysql.sock文件）
　　port            = 3306
　　#指定MySQL侦听端口
　　skip-name-resolve
　　#禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间，但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求。
　　key_buffer     = 256M
　　#key_buffer是利用索引块的缓冲区大小，增加他可得到更好处理的索引（对所有读和多重写）。索引被所有的线程共享，key_buffer的大小视内存大小而定。
　　table_open_cache = 256
　　#MySQL每打开一个表，都会读入一些数据到table_open_cache 缓存中，当MySQL在这个缓存中找不到相应的信息时，才会去磁盘上读取。默认值时64，假定系统有200并发连接，则需要将此参数设置为200*N（N为每个链接所需要的文件描述符数目）；当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上。
　　max_allowed_packet = 1M
　　#接受的数据包大小：增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当呢发出长查询或MySQLd必须返回大的结果行时才会分配更多内存。该变量之所以去较小默认值是一种预防措施，已捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用打的信息包而导致内存溢出。
　　sort_buffer_size = 1M
　　#MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能可以尝试增加sort_buffer_size 变量的大小。
　　read_buffer_size = 1M
　　#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每链接独享。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓存冲区。如果对标的顺序扫描请求非常频繁，并且认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
　　join_buffer_size =1M

　　#联合查询操作所能使用的缓冲区大小，和>　　query_cache_size= 16M
　　#指定MySQL查询结果缓冲区的大小。
　　read_rnd_buffer_size = 4M
　　#随机读缓冲区大小。当按任意顺序读取行时（例如，按照排序顺序），将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一边缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
　　myisam_sort_buffer_size=64M
　　#myisam_sort_buffer_size 这个缓冲区主要用于修复表过程中排序索引使用的内存或者是建立索引时排序索引用到的内存大小，一般4G内存给64M即可。
　　thread_concurrency =8
　　# thread_concurrency 这个值设置为cpu核数的2倍即可
　　thread_cache_size =8
　　# thread_cache_size表示可以重新利用保存在缓存中线程的数，
　　参考如下值：1G内存  —> 8 2G 内存 —> 16，4G以上设置为64
　　max_connections =1000
　　# max_connections 最大的连接数，根据业务请求量适当调整，设置500足够
　　max_connect_errors=6000
　　# max_connect_errors 是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。与性能并无太大关系。为了避免一些错误我们一般都设置比较大，比如说10000， （对同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST.）
　　open_files_limit = 65535
　　#mysql打开的文件描述符限制，默认最小1024
　　skip_locking
　　#避免MySQL的外部锁定，减少出错几率增强稳定性。
　　wait_timeout =8
　　#表示空闲的连接超出时间，默认是28800s，这个参数是和interactive_timeout一起使用的，也就是说想要让wait_timeout生效，必须同时设置interactive_timeout。
　　interactive-timeout =8
　　long_query_time =1
　　#慢查询日志的超出时间
　　log_slow_queries = /path/to/slow_querics
　　#慢查询日志路径，必须配合上面的参数一同使用
　　MySQL调优
　　MySQL调优可以从几个方面来做：
　　1. 架构层：
　　做从库，实现读写分离；
　　2.系统层次：
　　增加内存；
　　给磁盘做raid0或者raid5以增加磁盘的读写速度；
　　可以重新挂载磁盘，并加上noatime参数，这样可以减少磁盘的i/o;
　　3. MySQL本身调优：
　　(1) 如果未配置主从同步，可以把bin-log功能关闭，减少磁盘i/o
　　(2) 在my.cnf中加上skip-name-resolve,这样可以避免由于解析主机名延迟造成mysql执行慢
　　(3) 调整几个关键的buffer和cache。调整的依据，主要根据数据库的状态来调试。如何调优可以参考5.
　　4. 应用层次：
　　查看慢查询日志，根据慢查询日志优化程序中的SQL语句，比如增加索引
　　5. 调整几个关键的buffer和cache
　　1) key_buffer_size  首先可以根据系统的内存大小设定它，大概的一个参考值：1G以下内存设定128M；2G/256M; 4G/384M;8G/1024M；16G/2048M.这个值可以通过检查状态值Key_read_requests和 Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。注意：该参数值设置的过大反而会是服务器整体效率降低!
　　2) table_open_cache 打开一个表的时候，会临时把表里面的数据放到这部分内存中，一般设置成1024就够了，它的大小我们可以通过这样的方法来衡量： 如果你发现 open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
　　3) sort_buffer_size 查询排序时所能使用的缓冲区大小,该参数对应的分配内存是每连接独占!如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 4 = 400MB。所以，对于内存在4GB左右的服务器推荐设置为4-8M。
　　4) read_buffer_size 读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!
　　5) join_buffer_size 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!
　　6) myisam_sort_buffer_size 这个缓冲区主要用于修复表过程中排序索引使用的内存或者是建立索引时排序索引用到的内存大小，一般4G内存给64M即可。
　　7) query_cache_size MySQL查询操作缓冲区的大小，通过以下做法调整：SHOW STATUS LIKE ‘Qcache%’; 如果Qcache_lowmem_prunes该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。如果该值非常大，则表明经常出现缓冲不够的情况，需要增加缓存大小;Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，我们可以根据实际情况做出调整。一般情况下4G内存设置64M足够了。
　　8) thread_cache_size 表示可以重新利用保存在缓存中线程的数，参考如下值：1G  —> 8 2G  —> 16 3G  —> 32  >3G  —> 64
　　除此之外，还有几个比较关键的参数：
　　9) thread_concurrency 这个值设置为cpu核数的2倍即可
　　10) wait_timeout 表示空闲的连接超时时间，默认是28800s，这个参数是和interactive_timeout一起使用的，也就是说要想让wait_timeout 生效，必须同时设置interactive_timeout，建议他们两个都设置为10
　　11) max_connect_errors 是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。与性能并无太大关系。为了避免一些错误我们一般都设置比较大，比如说10000
　　12) max_connections 最大的连接数，根据业务请求量适当调整，设置500足够
　　13) max_user_connections 是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。通常我们设置为100足够