MySQL 性能优化技巧

打开/etc/my.cnf文件,修改以下设置,如果没有,可手动添加。调整设置时,请量力而行,这与你的服务器的配置有关,特别是内存大小。以下设置比较适合于1-2G内存的服务器,但并不绝对。

back_log = 200

要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认数值是50

max_allowed_packet = 4M

一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节,但是可在需要时增加到max_allowed_packet个字节。缺省地,该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列,你必须增加该值。它应该象你想要使用的最大BLOB的那么大。

max_connections = 1024

max_used_connections / max_connections ≈ 85%

允许的同时客户的数量。增加该值增加 mysqld要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 链接过多,请联系空间商 错误。 默认数值是100

table_open_cache = 512

open_tables / opened_tables >= 85%
open_tables / table_open_cache <= 95%

指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。检查峰值时间的状态值,如果open_tables等于table_open_cache,并且opened_tables在不断增长,那么就需要增加table_open_cache的值。注意,不能盲目地把table_open_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

tmp_table_size = 64M

max_heap_table_size = 32M

created_tmp_disk_tables / created_tmp_tables <= 25%

当临时内存表的大小达到一定限制的时候,MySQL就会将临时内存表写入到磁盘,变为临时磁盘表。这个限制由 tmp_table_size 和 max_heap_table_size 这两个变量中的最小值确定。

key_buffer = 384M

指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。

比例key_reads/key_read_requests应该尽可能的低,至少是1:100,1:1000更好。在专有的数据库服务器上,该值可设置为 RAM * 1/4。

key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

sort_buffer_size = 4M

每个线程排序所需的缓冲。如果状态值sort_merge_passes(排序算法使用归并的次数)很大,则应该把sort_buffer_size的值变大。

read_buffer_size = 4M

当一个查询不断地扫描某一个表,MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果你认为连续扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。

表扫描率 = handler_read_rnd_next / com_select
如果表扫描率超过 4000,说明进行了太多表扫描,很有可能索引没有建好,增加 read_buffer_size 值会有一些好处,但最好不要超过8mb。

read_rnd_buffer_size = 8M

加速排序操作后的读数据,提高读分类行的速度。如果正对远远大于可用内存的表执行GROUP BY或ORDER BY操作,应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。仍然不明白这个选项的用处……

thread_cache_size = 128

可以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以这个变量值。如果threads_created/connections很大,则应该把thread_cache_size的值变大。

query_cache_size = 32M

查询结果缓存。第一次执行某条SELECT语句的时候,服务器记住该查询的文本内容和它返回的结果。服务器下一次碰到这个语句的时候,它不会再次执行该语句。作为代替,它直接从查询缓存中的得到结果并把结果返回给客户端。

thread_concurrency = 2

最大并发线程数,cpu数量*2

wait_timeout = 120

设置超时时间,能避免长连接

skip-innodb

skip-bdb

关闭不需要的表类型,如果你需要,就不要加上这个

MySQL5.6参数变化

http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html

相关说法

1、handler_read_rnd 根据固定位置读取行的请求数。如果你执行很多需要排序的查询,该值会很高。你可能有很多需要完整表扫描的查询,或者你使用了不正确的索引用来多表查询。

2、handler_read_rnd_next 从数据文件中读取行的请求数。如果你在扫描很多表,该值会很大。通常情况下这意味着你的表没有做好索引,或者你的查询语句没有使用好索引字段。

3、table_locks_waited 无法立即获得锁定表而必须等待的次数。如果该值很高,且您遇到了性能方面的问题,则应该首先检查您的查询语句,然后使用复制操作来分开表。如果 table_locks_immediate / table_locks_waited > 5000,最好采用innodb引擎,因为innodb是行锁而myisam是表锁,对于高并发写入的应用innodb效果会好些。

扩展阅读

有人提起过依据key_reads/key_read_requests调整key_buffer_size有失偏颇,如果感兴趣,不妨移步这里看看:http://www.baidu.com/s?wd=key_reads+%2f+uptime

特别注意

本文发表时间较早,并未针对innodb进行优化,使用新版数据库或innodb引擎的读者适当参考。

文章作者: 若海; 原文链接: https://www.rehiy.com/post/25/; 转载需声明来自技术写真 - 若海

已有 2 条评论

  1. 吃过午饭,困意油然,见子题一网址于空间说说处,大喜,点击之,进入之,飘过

  2. 你很强!

添加新评论