linux高负载下彻底优化mysql数据库【转贴】

同时在线访问量继续增大 对于1G内存的服务器明显感觉到吃力严重时甚至每天都会死机 或者时不时的服务器卡一下 这个问题曾经困扰了我半个多月MySQL使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。

安装好mysql后，配制文件应该在/usr/local/mysql/share/mysql目录中，配制文件有几个，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境，当然需要有不同的配制文件了。

一般的情况下，my-medium.cnf这个配制文件就能满足我们的大多需要；一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了，使用mysqladmin variables extended-status –u root –p 可以看到目前的参数，有３个配置参数是最重要的，即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只对MyISAM表起作用，

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。一般我们设为16M,实际上稍微大一点的站点　这个数字是远远不够的，通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例 key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。 或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql，以下的状态值都是本人通过phpmyadmin获得的实例分析:

这个服务器已经运行了20天
key_buffer_size – 128M
key_read_requests – 650759289
key_reads - 79112
比例接近1:8000 健康状况非常好

另外一个估计key_buffer_size的办法　把你网站数据库的每个表的索引所占空间大小加起来看看以此服务器为例:比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大。

从4.0.1开始，MySQL提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。

通过调节以下几个参数可以知道query_cache_size设置得是否合理
Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks

Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲

我设置:
query_cache_size = 32M
query_cache_type= 1

得到如下状态值:
Qcache queries in cache 12737 表明目前缓存的条数
Qcache inserts 20649006
Qcache hits 79060095 　看来重复查询率还挺高的
Qcache lowmem prunes 617913　有这么多次出现缓存过低的情况
Qcache not cached 189896 　　
Qcache free memory 18573912　　目前剩余缓存空间
Qcache free blocks 5328 这个数字似乎有点大　碎片不少
Qcache total blocks 30953
如果内存允许32M应该要往上加点

table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现 open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。



对于有1G内存的机器，推荐值是128－256。

笔者设置
table_cache = 256

得到以下状态:
Open tables 256
Opened tables 9046
虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说,已经运行了20天，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值 那就要考虑增大table_cache。

如果你不需要记录2进制log 就把这个功能关掉，注意关掉以后就不能恢复出问题前的数据了，需要您手动备份，二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外，如果做同步复制( Replication )的话，也需要使用二进制日志传送修改情况。

log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字引，每次启动服务时，都会重新生成一个新的二进制文件。此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定记录的数据库；使用binlog- ignore-db可以指定不记录的数据库。注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。



关掉这个功能只需要在他前面加上#号
#log-bin

开启慢查询日志( slow query log )

慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询，如果需要，还可以记录不使用索引的记录。下面是一个慢查询日志的例子：

开启慢查询日志，需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。

log_slow_queries指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值，缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的参数，它指示记录不使用索引的查询。笔者设置 long_query_time=10

笔者设置:

sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
interactive_timeout=120
thread_concurrency = 4

参数说明:

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


max_connections
并发连接数目最大，120 超过这个值就会自动恢复，出了问题能自动解决

thread_cache
没找到具体说明，不过设置为32后 20天才创建了400多个线程 而以前一天就创建了上千个线程 所以还是有用的

thread_concurrency
#设置为你的cpu数目x2,例如，只有一个cpu,那么thread_concurrency=2
#有2个cpu,那么thread_concurrency=4

skip-innodb
#去掉innodb支持

相关资料

shell> mysqladmin variables

每个选项在下面描述。对于缓冲区大小、长度和栈大小的值以字节给出，你能用于个后缀“K”或“M” 指出以K字节或兆字节显示值。例如，16M指出16兆字节。后缀字母的大小写没有关系；16M和16m是相同的。

你也可以用命令SHOW STATUS自一个运行的服务器看见一些统计。见7.21 SHOW语法(得到表、列的信息)。

back_log
    要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
connect_timeout
    mysqld服务器在用Bad handshake（糟糕的握手）应答前正在等待一个连接报文的秒数。
delayed_insert_timeout
    一个INSERT DELAYED线程应该在终止之前等待INSERT语句的时间。
delayed_insert_limit
    在插入delayed_insert_limit行后，INSERT DELAYED处理器将检查是否有任何SELECT语句未执行。如果这样，在继续前执行允许这些语句。
delayed_queue_size
    应该为处理INSERT DELAYED分配多大一个队列(以行数)。如果排队满了，任何进行INSERT DELAYED的客户将等待直到队列又有空间了。
flush_time
    如果这被设置为非零值，那么每flush_time秒所有表将被关闭(以释放资源和sync到磁盘)。
interactive_timeout
    服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户。也可见wait_timeout。
join_buffer_size
    用于全部联结(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通常得到快速联结的最佳方法是增加索引。）
key_buffer_size
    索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。记住既然MySQL不缓存读取的数据，你将必须为OS文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度，使用LOCK TABLES。见7.24LOCK TABLES/UNLOCK TABLES语法。
long_query_time
    如果一个查询所用时间超过它(以秒计)，Slow_queries记数器将被增加。
max_allowed_packet
    一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大。
max_connections
    允许的同时客户的数量。增加该值增加mysqld要求的文件描述符的数量。见下面对文件描述符限制的注释。见18.2.4 Too many connections错误。
max_connect_errors
    如果有多于该数量的从一台主机中断的连接，这台主机阻止进一步的连接。你可用FLUSH HOSTS命令疏通一台主机。
max_delayed_threads
    不要启动多于的这个数字的线程来处理INSERT DELAYED语句。如果你试图在所有INSERT DELAYED线程在用后向一张新表插入数据，行将被插入，就像DELAYED属性没被指定那样。
max_join_size
    可能将要读入多于max_join_size个记录的联结将返回一个错误。如果你的用户想要执行没有一个WHERE子句、花很长时间并且返回百万行的联结，设置它。
max_sort_length
    在排序BLOB或TEXT值时使用的字节数(每个值仅头max_sort_length个字节被使用；其余的被忽略)。
max_tmp_tables
    （该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。
net_buffer_length
    通信缓冲区在查询之间被重置到该大小。通常这不应该被改变，但是如果你有很少的内存，你能将它设置为查询期望的大小。（即，客户发出的SQL语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。）
record_buffer
    每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
sort_buffer
    每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。见18.5 MySQL在哪儿存储临时文件。
table_cache
    为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符，见下面对文件描述符限制的注释。对于表缓存如何工作的信息，见10.2.4 MySQL怎样打开和关闭表。
tmp_table_size
    如果一张临时表超出该大小，MySQL产生一个The table tbl_name is full形式的错误，如果你做很多高级GROUP BY查询，增加tmp_table_size值。
thread_stack
    每个线程的栈大小。由crash-me测试检测到的许多限制依赖于该值。缺省队一般的操作是足够大了。见10.8 使用你自己的基准。
wait_timeout
    服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。

MySQL的Query Cache


QueryCache(下面简称QC)是根据SQL语句来cache的。一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用 QC。每个Cache都是以SQL文本作为key来存的。在应用QC之前，SQL文本不会被作任何处理。也就是说，两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE。不过SQL文本有可能会被客户端做一些处理。例如在官方的命令行客户端里，在发送SQL给服务器之前，会做如下处理：

    * 过滤所有注释
    * 去掉SQL文本前后的空格,TAB等字符。注意，是文本前面和后面的。中间的不会被去掉。

下面的三条SQL里，因为SELECT大小写的关系，最后一条和其他两条在QC里肯定是用的不一样的存储位置。而第一条和第二条，区别在于后者有个注释，在不同客户端，会有不一样的结果。所以，保险起见，请尽量不要使用动态的注释。在PHP的mysql扩展里，SQL的注释是不会被去掉的。也就是三条SQL会被存储在三个不同的缓存里，虽然它们的结果都是一样的。

select * FROM people where name='surfchen';
select * FROM people where /*hey~*/name='surfchen';
SELECT * FROM people where name='surfchen';

目前只有select语句会被cache，其他类似show,use的语句则不会被cache。

因为QC是如此前端，如此简单的一个缓存系统，所以如果一个表被更新，那么和这个表相关的SQL的所有QC都会被失效。假设一个联合查询里涉及到了表A和表B，如果表A或者表B的其中一个被更新（update或者delete），这个查询的QC将会失效。

也就是说，如果一个表被频繁更新，那么就要考虑清楚究竟是否应该对相关的一些SQL进行QC了。一个被频繁更新的表如果被应用了QC，可能会加重数据库的负担，而不是减轻负担。我一般的做法是默认打开QC，而对一些涉及频繁更新的表的SQL语句加上SQL_NO_CACHE关键词来对其禁用 CACHE。这样可以尽可能避免不必要的内存操作，尽可能保持内存的连续性。

那些查询很分散的SQL语句，也不应该使用QC。例如用来查询用户和密码的语句——“select pass from user where name=’surfchen’”。这样的语句，在一个系统里，很有可能只在一个用户登陆的时候被使用。每个用户的登陆所用到的查询，都是不一样的SQL 文本，QC在这里就几乎不起作用了，因为缓存的数据几乎是不会被用到的，它们只会在内存里占地方。
存储块

在本节里“存储块”和“block”是同一个意思QC缓存一个查询结果的时 候，一般情况下不是一次性地分配足够多的内存来缓存结果的。而是在查询结果获得的过程中，逐块存储。当一个存储块被填满之后，一个新的存储块将会被创建， 并分配内存（allocate）。单个存储块的内存分配大小通过query_cache_min_res_unit参数控制，默认为4KB。最后一个存储块，如果不能被全部利用，那么没使用的内存将会被释放。如果被缓存的结果很大，那么会可能会导致分配内存操作太频繁，系统系能也随之下降；而如果被缓存的结果都很小，那么可能会导致内存碎片过多，这些碎片如果太小，就很有可能不能再被分配使用。

除了查询结果需要存储块之外，每个SQL文本也需要一个存储块，而涉及到的表也需要一个存储块（表的存储块是所有线程共享的，每个表只需要一个存储块）。存储块总数量=查询结果数量*2+涉及的数据库表数量。也就是说，第一个缓存生成的时候，至少需要三个存储块：表信息存储块，SQL文本存储块，查询结果存储块。而第二个查询如果用的是同一个表，那么最少只需要两个存储块：SQL文本存储块，查询结果存储块。

通过观察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每个缓存结果占用的存储块。它们的 比例如果接近1:2，则说明当前的query_cache_min_res_unit参数已经足够大了。如果Qcache_total_blocks比 Qcache_queries_in_cache多很多，则需要增加query_cache_min_res_unit的大小。

Qcache_queries_in_cache*query_cache_min_res_unit（sql文本和表信息所在的block占用的 内存很小，可以忽略）如果远远大于query_cache_size-Qcache_free_memory，那么可以尝试减小 query_cache_min_res_unit的值。
调整大小

如果Qcache_lowmem_prunes增长迅速，意味着很多缓存因为内存不够而被释放，而不是因为相关表被更新。尝试加大query_cache_size，尽量使Qcache_lowmem_prunes零增长。
启动参数

show variables like ‘query_cache%’可以看到这些信息。

query_cache_limit:如果单个查询结果大于这个值，则不Cache
query_cache_size: 分配给QC的内存。如果设为0，则相当于禁用QC。要注意QC必须使用大约40KB来存储它的结构，如果设定小于40KB,则相当于禁用QC。QC存储的最小单位是1024 byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。
query_cache_type:0 完全禁止QC，不受SQL语句控制（另外可能要注意的是，即使这里禁用，上面一个参数所设定的内存大小还是会被分配）；1启用QC，可以在SQL语句使用 SQL_NO_CACHE禁用；2可以在SQL语句使用SQL_CACHE启用。
query_cache_min_res_unit:每次给QC结果分配内存的大小

状态

show status like ‘Qcache%’可以看到这些信息。

Qcache_free_blocks: 当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。这些blocks将会被统计到这个值来。可以用FLUSH QUERY CACHE语句来清空free blocks。
Qcache_free_memory:可用内存，如果很小，考虑增加query_cache_size
Qcache_hits:自mysql进程启动起，cache的命中数量
Qcache_inserts:自mysql进程启动起，被增加进QC的数量
Qcache_lowmem_prunes:由于内存过少而导致QC被删除的条数。加大query_cache_size，尽可能保持这个值0增长。
Qcache_not_cached:自mysql进程启动起，没有被cache的只读查询数量（包括select,show,use,desc等）
Qcache_queries_in_cache:当前被cache的SQL数量
Qcache_total_blocks: 在QC中的blocks数。一个query可能被多个blocks存储，而这几个blocks中的最后一个，未用满的内存将会被释放掉。例如一个QC结果 要占6KB内存，如果query_cache_min_res_unit是 4KB，则最后将会生成3个blocks，第一个block用来存储sql语句文本，这个不会被统计到query+cache_size里，第二个block为4KB，第三个block为2KB（先allocate4KB，然后释放多余的2KB）。每个表，当第一个和它有关的SQL查询被CACHE的时候，会使用一个block来存储表信息。也就是说，block会被用在三处地方：表信息，SQL文本，查询结果。

从 MySQL 4.0.1 开始，MySQL server 有一个重要的特征：Query Cache。 当在使用中，查询缓存会存储一个 SELECT 查询的文本与被传送到客户端的相应结果。如果之后接收到一个同样的查询，服务器将从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。

注意：查询缓存绝不返回过期数据。当数据被修改后，在查询缓存中的任何相关词条均被转储清除。

在某些表并不经常更改，而你又对它执行大量的相同查询时，查询缓存将是非常有用的。对于许多 WEB 服务器使用大量的动态信息，这是一个很典型的情况。

下面是查询缓存的一个性能数据。(这些结果的产生，是通过在一个 a Linux Alpha 2 x 500 MHz、2GB RAM 和 64MB 查询缓存上执行 MySQL 基准套件和到的)：

    * 如果你执行的所有查询均是简单的(比如从表中一行一行的选取)；但是仍然是不同的，所以该查询不能被缓冲，查询缓存处于活动时，开销为 13%。这可以被看作是最差的情况。然而，在实际情况下，查询是比我们的简单示例要复杂得多的，所以开销通常显著得低。   
    * 在只有一行记录表中搜索一行后，搜索将快 238% 。这可以被认为是接近于对一个被缓冲的查询所期望的最小的加速。   
    * 如果你希望禁用查询缓存，设置 query_cache_size=0。禁用了查询缓存，将没有明显的开销。(在配置选项 --without-query-cache 的帮助下，查询缓存可以被排除在外码之外)   

查询缓存如何运作

查询在分析之前先被比较，因而

SELECT * FROM tbl_name

和

Select * from tbl_name

对于查询缓存被当作是不同的查询，因而查询需要严格的一致(字节对字节的)，才会被认为是同样的。 另外，如果一个客户端使用一个新的连接协议格式或不同于其它客户端的另一个字符集，一个查询将被视为不同的。

使用不同数据库的，使用不同协议版本的，或使用不同的缺省字符串的查询将被认为是不同的查询，并将分别的缓冲。

高速缓冲不对 SELECT CALC_ROWS ... 和 SELECT FOUND_ROWS() ... 类型的查询起作用，因为找到的行的数目也是被存储在缓冲里的。

如果查询结果被从查询缓存中返回，那么状态变量 Com_select 将不会被增加，但是 Qcache_hits 却会增加。查看章节 6.9.4 查询缓存的状态和维护。

如果一个表发生的改变 (INSERT, UPDATE, DELETE, TRUNCATE, ALTER 或 DROP TABLE|DATABASE)，那么所有这张表使用的缓冲的查询(可能通过一个 MRG_MyISAM 表！)将被得失效，并从缓冲中移除。

InnoDB 表的事务所做的更改将在一个 COMMIT 被完成时，使数据失效。

如果一个查询包括下面的函数，它将不能被缓冲：   
函数         函数         函数
User-Defined Functions         CONNECTION_ID         FOUND_ROWS
GET_LOCK         RELEASE_LOCK         LOAD_FILE
MASTER_POS_WAIT         NOW         SYSDATE
CURRENT_TIMESTAMP         CURDATE         CURRENT_DATE
CURTIME         CURRENT_TIME         DATABASE
ENCRYPT (只有一个参数调用)         LAST_INSERT_ID         RAND
UNIX_TIMESTAMP (无参数调用)         USER         BENCHMARK

如果一个查询包含用户变量，引用 MySQL 系统数据库，或下列之一的格式，SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE ..., SELECT ... INTO DUMPFILE ... 或 SELECT * FROM AUTOINCREMENT_FIELD IS NULL (检索最后一个插入 ID - ODBC 语句)，该查询亦不可以被缓存。

然而，FOUND ROWS() 将返回正确的值，即使先前的查询是从缓存中读取的。

万一一个查询不使用任何表，或使用临时表，或用户对任何相关表有一个列权限，那么查询将不会被缓存。

在一个查询从查询缓存中读取前，MySQL 将检查用户对所有相关的数据库和表有 SELECT 权限。如果不是这种情况，缓存的结果将不能被使用。

查询缓存设置

查询缓存为了 mysqld 添加了几个 MySQL 系统变量，它可以在配置文件中被设置，或在启动 mysqld 时的命令行上设置。

    * query_cache_limit 不缓存大于这个值的结果。(缺省为 1M)   

    * query_cache_min_res_unit 这个变量从 4.1 被引进。 查询的结果 (已被传送到客户端的数据) 在结果检索期间被存储到查询缓存中。因而，数据不会以一个大块地处理。查询缓存在需要时分配块用于处理这个数据，所以当一个块被填充后，一个新的块被分配。甚为内存分配操作是昂贵的，查询缓存以最小的尺寸 query_cache_min_res_unit 分配块。当一个查询执行完成，最后的结果块被修整到实际数据的尺寸大小，以便未使用的内存被释放。   
          o query_cache_min_res_unit 的缺省值为 4 KB，在大多数据情况下已够用了。   
          o 如果你有许多查询返回一个较小的结果，缺省的块尺寸可能会引起内存碎片 (显示为一个很大数量的空闲块(Qcache_free_blocks)，这将引起查询缓存不得不因缺乏内存(Qcache_lowmem_prunes)而从缓存中删除查询)。在这种情况下，你应该减少 query_cache_min_res_unit。   
          o 如果你的主要查询返回的是大的结果集(查看 Qcache_total_blocks 和 Qcache_queries_in_cache)，你可以通过增加 query_cache_min_res_unit 来增加性能。然而，要小心不要将它设得太大。   

    * query_cache_size 为了存储老的查询结果而分配的内存数量 (以字节指定) 。如果设置它为 0 ，查询缓冲将被禁止(缺省值为 0 )。   
    * query_cache_type 这个可以被设置为 (只能是数字)   
      选项         含义
      0         (OFF, 不缓存或重新得到结果)
      1         (ON, 缓存所有的结果，除了 SELECT SQL_NO_CACHE ... 查询)
      2         (DEMAND, 仅缓存 SELECT SQL_CACHE ... 查询)

在一个线程(连接)内，查询缓存的行为可以被改变。句法如下所示：

QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2

选项         含义
0 or OFF         不缓存或重新得到结果
1 or ON         缓存所有的结果，除了 SELECT SQL_NO_CACHE ... 查询
2 or DEMAND         仅缓存 SELECT SQL_CACHE ... 查询
在 SELECT 中的查询缓存选项

有两个可能的查询缓存相关的参数可以在一个 SELECT 查询中被指定：

选项         含义
SQL_CACHE         如果 QUERY_CACHE_TYPE 为 DEMAND，允许该查询被缓存。如果 QUERY_CACHE_TYPE 为 ON，这是缺省的。如果 QUERY_CACHE_TYPE 为 OFF，它不做任何事
SQL_NO_CACHE         使这个查询不被缓存，不允许这个查询被存储到高速缓存中
查询缓存的状态和维护

使用 FLUSH QUERY CACHE 命令，你可以整理查询缓存，以更好的利用它的内存。这个命令不会从缓存中移除任何查询。FLUSH TABLES 会转储清除查询缓存。

RESET QUERY CACHE 使命从查询缓存中移除所有的查询结果。

你可以检查查询缓存在你的 MySQL 是否被引进：

mysql> SHOW VARIABLES LIKE 'have_query_cache';+------------------+-------+| Variable_name       | Value |+------------------+-------+| have_query_cache | YES      |+------------------+-------+1 row in set (0.00 sec)

在 SHOW STATUS 中，你可以监视查询缓存的性能：
变量         含义
Qcache_queries_in_cache         在缓存中已注册的查询数目
Qcache_inserts         被加入到缓存中的查询数目
Qcache_hits         缓存采样数数目
Qcache_lowmem_prunes         因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached         没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory         查询缓存的空闲内存总数
Qcache_free_blocks         查询缓存中的空闲内存块的数目
Qcache_total_blocks         查询缓存中的块的总数目

Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

查询缓存使用变长的块，因而 Qcache_total_blocks 和 Qcache_free_blocks 可能显示查询缓存的碎片。在 FLUSH QUERY CACHE 之后，只有剩余一个单独的(大的)空闲块。

注意：每个查询最小需要两个块(一个用于存储查询文本，另一个或多个用于存储查询结果)。同样的，每个被一个查询使用的表需要一个块，但是，如果有两个或更多的查询使用同一张表，仅仅只需要分配一个块就行了。

你可以使用状态变量 Qcache_lowmem_prunes 来谐调查询缓存尺寸。它计数被从缓存中移除的查询，该查询的移除是为了释放内存，以缓存新建的查询。查询缓存使用一个 least recently used (LRU) 策略来判断从缓存中移除哪个查询。

如何对MySQL 服务器进行调优

如今，开发人员不断地开发和部署使用 LAMP（Linux®、Apache、MySQL 和 PHP/Perl）架构的应用程序。但是，服务器管理员常常对应用程序本身没有什么控制能力，因为应用程序是别人编写的。这份 共三部分的系列文章将讨论许多服务器配置问题，这些配置会影响应用程序的性能。本文是本系列文章的第三部分，也是最后一部分，将重点讨论为实现最高效率而对数据库层进行的调优。

关于 MySQL 调优

有 3 种方法可以加快 MySQL 服务器的运行速度，效率从低到高依次为：
替换有问题的硬件。 对 MySQL 进程的设置进行调优。 对查询进行优化。

替换有问题的硬件通常是我们的第一考虑，主要原因是数据库会占用大量资源。不过这种解决方案也就仅限于此了。实际上，您通常可以让中央处理器（CPU）或磁盘速度加倍，也可以让内存增大 4 到 8 倍。

第二种方法是对 MySQL 服务器（也称为 mysqld）进行调优。对这个进程进行调优意味着适当地分配内存，并让 mysqld 了解将会承受何种类型的负载。加快磁盘运行速度不如减少所需的磁盘访问次数。类似地，确保 MySQL 进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务（如处理临时磁盘表或打开和关闭文件）上的时间。对 mysqld 进行调优是本文的重点。

最好的方法是确保查询已经进行了优化。这意味着对表应用了适当的索引，查询是按照可以充分利用 MySQL 功能的方式来编写的。尽管本文并没有包含查询调优方面的内容（很多著作中已经针对这个主题进行了探讨），不过它会配置 mysqld 来报告可能需要进行调优的查询。

虽然已经为这些任务指派了次序，但是仍然要注意硬件和 mysqld 的设置以利于适当地调优查询。机器速度慢也就罢了，我曾经见过速度很快的机器在运行设计良好的查询时由于负载过重而失败，因为 mysqld 被大量繁忙的工作所占用而不能服务查询。

记录慢速查询

在一个 SQL 服务器中，数据表都是保存在磁盘上的。索引为服务器提供了一种在表中查找特定数据行的方法，而不用搜索整个表。当必须要搜索整个表时，就称为表扫描。通常来说，您可能只希望获得表中数据的一个子集，因此全表扫描会浪费大量的磁盘 I/O，因此也就会浪费大量时间。当必须对数据进行连接时，这个问题就更加复杂了，因为必须要对连接两端的多行数据进行比较。

当然，表扫描并不总是会带来问题；有时读取整个表反而会比从中挑选出一部分数据更加有效（服务器进程中查询规划器用来作出这些决定）。如果索引的使用效率很低，或者根本就不能使用索引，则会减慢查询速度，而且随着服务器上的负载和表大小的增加，这个问题会变得更加显著。执行时间超过给定时间范围的查询就称为慢速查询。

您可以配置 mysqld 将这些慢速查询记录到适当命名的慢速查询日志中。管理员然后会查看这个日志来帮助他们确定应用程序中有哪些部分需要进一步调查。清单 1 给出了要启用慢速查询日志需要在 my.cnf 中所做的配置。

清单 1. 启用 MySQL 慢速查询日志

               
[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don't use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes

这三个设置一起使用，可以记录执行时间超过 5 秒和没有使用索引的查询。请注意有关 log-queries-not-using-indexes 的警告：您必须使用 MySQL 4.1 或更高版本。慢速查询日志都保存在 MySQL 数据目录中，名为 hostname-slow.log。如果希望使用一个不同的名字或路径，可以在 my.cnf 中使用 log-slow-queries = /new/path/to/file 实现此目的。

阅读慢速查询日志最好是通过 mysqldumpslow 命令进行。指定日志文件的路径，就可以看到一个慢速查询的排序后的列表，并且还显示了它们在日志文件中出现的次数。一个非常有用的特性是 mysqldumpslow 在比较结果之前，会删除任何用户指定的数据，因此对同一个查询的不同调用被计为一次；这可以帮助找出需要工作量最多的查询。

对查询进行缓存

很多 LAMP 应用程序都严重依赖于数据库，但却会反复执行相同的查询。每次执行查询时，数据库都必须要执行相同的工作 —— 对查询进行分析，确定如何执行查询，从磁盘中加载信息，然后将结果返回给客户机。MySQL 有一个特性称为查询缓存，它将（后面会用到的）查询结果保存在内存中。在很多情况下，这会极大地提高性能。不过，问题是查询缓存在默认情况下是禁用的。

将 query_cache_size = 32M 添加到 /etc/my.conf 中可以启用 32MB 的查询缓存。

监视查询缓存

在启用查询缓存之后，重要的是要理解它是否得到了有效的使用。MySQL 有几个可以查看的变量，可以用来了解缓存中的情况。清单 2 给出了缓存的状态。

清单 2. 显示查询缓存的统计信息

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+------------+
| Variable_name            | Value       |
+-------------------------+------------+
| Qcache_free_blocks       | 5216        |
| Qcache_free_memory       | 14640664    |
| Qcache_hits              | 2581646882 |
| Qcache_inserts           | 360210964   |
| Qcache_lowmem_prunes     | 281680433   |
| Qcache_not_cached        | 79740667    |
| Qcache_queries_in_cache | 16927       |
| Qcache_total_blocks      | 47042       |
+-------------------------+------------+
8 rows in set (0.00 sec)

这些项的解释如表 1 所示。

表 1. MySQL 查询缓存变量
变量名         说明
Qcache_free_blocks         缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory         缓存中的空闲内存。
Qcache_hits         每次查询在缓存中命中时就增大。
Qcache_inserts         每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。
Qcache_lowmem_prunes         缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。
Qcache_not_cached         不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。
Qcache_queries_in_cache         当前缓存的查询（和响应）的数量。
Qcache_total_blocks         缓存中块的数量。

通常，间隔几秒显示这些变量就可以看出区别，这可以帮助确定缓存是否正在有效地使用。运行 FLUSH STATUS 可以重置一些计数器，如果服务器已经运行了一段时间，这会非常有帮助。

使用非常大的查询缓存，期望可以缓存所有东西，这种想法非常诱人。由于 mysqld 必须要对缓存进行维护，例如当内存变得很低时执行剪除，因此服务器可能会在试图管理缓存时而陷入困境。作为一条规则，如果 FLUSH QUERY CACHE 占用了很长时间，那就说明缓存太大了。

强制限制

您可以在 mysqld 中强制一些限制来确保系统负载不会导致资源耗尽的情况出现。清单 3 给出了 my.cnf 中与资源有关的一些重要设置。

清单 3. MySQL 资源设置

set-variable=max_connections=500
set-variable=wait_timeout=10
max_connect_errors = 100

连接最大个数是在第一行中进行管理的。与 Apache 中的 MaxClients 类似，其想法是确保只建立服务允许数目的连接。要确定服务器上目前建立过的最大连接数，请执行 SHOW STATUS LIKE 'max_used_connections'。

第 2 行告诉 mysqld 终止所有空闲时间超过 10 秒的连接。在 LAMP 应用程序中，连接数据库的时间通常就是 Web 服务器处理请求所花费的时间。有时候，如果负载过重，连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设 低一点并不可取！

最后一行是一个安全的方法。如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到 FLUSH HOSTS 之后才能运行。默认情况下，10 次失败就足以导致锁定了。将这个值修改为 100 会给服务器足够的时间来从问题中恢复。如果重试 100 次都无法建立连接，那么使用再高的值也不会有太多帮助，可能它根本就无法连接。

缓冲区和缓存

MySQL 支持超过 100 个的可调节设置；但是幸运的是，掌握少数几个就可以满足大部分需要。查找这些设置的正确值可以通过 SHOW STATUS 命令查看状态变量，从中可以确定 mysqld 的运作情况是否符合我们的预期。给缓冲区和缓存分配的内存不能超过系统中的现有内存，因此调优通常都需要进行一些妥协。

MySQL 可调节设置可以应用于整个 mysqld 进程，也可以应用于单个客户机会话。

服务器端的设置

每个表都可以表示为磁盘上的一个文件，必须先打开，后读取。为了加快从文件中读取数据的过程，mysqld 对这些打开文件进行了缓存，其最大数目由 /etc/mysqld.conf 中的 table_cache 指定。清单 4 给出了显示与打开表有关的活动的方式。

清单 4. 显示打开表的活动

               
mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables    | 5000   |
| Opened_tables | 195    |
+---------------+-------+
2 rows in set (0.00 sec)

清单 4 说明目前有 5,000 个表是打开的，有 195 个表需要打开，因为现在缓存中已经没有可用文件描述符了（由于统计信息在前面已经清除了，因此可能会存在 5,000 个打开表中只有 195 个打开记录的情况）。如果 Opened_tables 随着重新运行 SHOW STATUS 命令快速增加，就说明缓存命中率不够。如果 Open_tables 比 table_cache 设置小很多，就说明该值太大了（不过有空间可以增长总不是什么坏事）。例如，使用 table_cache = 5000 可以调整表的缓存。

与表的缓存类似，对于线程来说也有一个缓存。 mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接。

清单 5 显示如何确定是否缓存了足够的线程。

清单 5. 显示线程使用统计信息

               
mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name      | Value   |
+-------------------+--------+
| Threads_cached     | 27      |
| Threads_connected | 15      |
| Threads_created    | 838610 |
| Threads_running    | 3       |
+-------------------+--------+
4 rows in set (0.00 sec)

此处重要的值是 Threads_created，每次 mysqld 需要创建一个新线程时，这个值都会增加。如果这个数字在连续执行 SHOW STATUS 命令时快速增加，就应该尝试增大线程缓存。例如，可以在 my.cnf 中使用 thread_cache = 40 来实现此目的。

关键字缓冲区保存了 MyISAM 表的索引块。理想情况下，对于这些块的请求应该来自于内存，而不是来自于磁盘。清单 6 显示了如何确定有多少块是从磁盘中读取的，以及有多少块是从内存中读取的。

清单 6. 确定关键字效率

               
mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name      | Value      |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads          | 98247      |
+-------------------+-----------+
2 rows in set (0.00 sec)

Key_reads 代表命中磁盘的请求个数， Key_read_requests 是总数。命中磁盘的读请求数除以读请求总数就是不中比率 —— 在本例中每 1,000 个请求，大约有 0.6 个没有命中内存。如果每 1,000 个请求中命中磁盘的数目超过 1 个，就应该考虑增大关键字缓冲区了。例如，key_buffer = 384M 会将缓冲区设置为 384MB。

临时表可以在更高级的查询中使用，其中数据在进一步进行处理（例如 GROUP BY 字句）之前，都必须先保存到临时表中；理想情况下，在内存中创建临时表。但是如果临时表变得太大，就需要写入磁盘中。清单 7 给出了与临时表创建有关的统计信息。

清单 7. 确定临时表的使用

               
mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name            | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files        | 2      |
| Created_tmp_tables       | 32912 |
+-------------------------+-------+
3 rows in set (0.00 sec)

每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables。对于这个比率，并没有什么严格的规则，因为这依赖于所涉及的查询。长时间观察 Created_tmp_disk_tables 会显示所创建的磁盘表的比率，您可以确定设置的效率。 tmp_table_size 和 max_heap_table_size 都可以控制临时表的最大大小，因此请确保在 my.cnf 中对这两个值都进行了设置。

每个会话的设置

下面这些设置针对于每个会话。在设置这些数字时要十分谨慎，因为它们在乘以可能存在的连接数时候，这些选项表示大量的内存！您可以通过代码修改会话中的这些数字，或者在 my.cnf 中为所有会话修改这些设置。

当 MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。如果 sort_merge_passes 状态变量很大，这就指示了磁盘的活动情况。清单 8 给出了一些与排序相关的状态计数器信息。

清单 8. 显示排序统计信息

               
mysql> SHOW STATUS LIKE "sort%";
+-------------------+---------+
| Variable_name      | Value    |
+-------------------+---------+
| Sort_merge_passes | 1        |
| Sort_range         | 79192    |
| Sort_rows          | 2066532 |
| Sort_scan          | 44006    |
+-------------------+---------+
4 rows in set (0.00 sec)

如果 sort_merge_passes 很大，就表示需要注意 sort_buffer_size。例如， sort_buffer_size = 4M 将排序缓冲区设置为 4MB。

MySQL 也会分配一些内存来读取表。理想情况下，索引提供了足够多的信息，可以只读入所需要的行，但是有时候查询（设计不佳或数据本性使然）需要读取表中大量数据。要理解这种行为，需要知道运行了多少个 SELECT 语句，以及需要读取表中的下一行数据的次数（而不是通过索引直接访问）。实现这种功能的命令如清单 9 所示。

清单 9. 确定表扫描比率

               
mysql> SHOW STATUS LIKE "com_select";
+---------------+--------+
| Variable_name | Value   |
+---------------+--------+
| Com_select     | 318243 |
+---------------+--------+
1 row in set (0.00 sec)mysql> SHOW STATUS LIKE "handler_read_rnd_next";
+-----------------------+-----------+
| Variable_name          | Value      |
+-----------------------+-----------+
| Handler_read_rnd_next | 165959471 |
+-----------------------+-----------+
1 row in set (0.00 sec)

Handler_read_rnd_next / Com_select 得出了表扫描比率 —— 在本例中是 521:1。如果该值超过 4000，就应该查看 read_buffer_size，例如 read_buffer_size = 4M。如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了！

3 个必不可少的工具

尽管在了解具体设置时，SHOW STATUS 命令会非常有用，但是您还需要一些工具来解释 mysqld 所提供的大量数据。我发现有 3 个工具是必不可少的；在 参考资料 一节中您可以找到相应的链接。

大部分系统管理员都非常熟悉 top 命令，它为任务所消耗的 CPU 和内存提供了一个不断更新的视图。 mytop 对 top 进行了仿真；它为所有连接上的客户机以及它们正在运行的查询提供了一个视图。mytop 还提供了一个有关关键字缓冲区和查询缓存效率的实时数据和历史数据，以及有关正在运行的查询的统计信息。这是一个很有用的工具，可以查看系统中（比如 10 秒钟之内）的状况，您可以获得有关服务器健康信息的视图，并显示导致问题的任何连接。

mysqlard 是一个连接到 MySQL 服务器上的守护程序，负责每 5 分钟搜集一次数据，并将它们存储到后台的一个 Round Robin Database 中。有一个 Web 页面会显示这些数据，例如表缓存的使用情况、关键字效率、连接上的客户机以及临时表的使用情况。尽管 mytop 提供了服务器健康信息的快照，但是 mysqlard 则提供了长期的健康信息。作为奖励，mysqlard 使用自己搜集到的一些信息针对如何对服务器进行调优给出一些建议。

搜集 SHOW STATUS 信息的另外一个工具是 mysqlreport。其报告要远比 mysqlard 更加复杂，因为需要对服务器的每个方面都进行分析。这是对服务器进行调优的一个非常好的工具，因为它对状态变量进行适当计算来帮助确定需要修正哪些问题。

结束语

本文介绍了对 MySQL 进行调优的一些基础知识，并对这个针对 LAMP 组件进行调优的 3 部分系列文章进行了总结。调优很大程度上需要理解组件的工作原理，确定它们是否正常工作，进行一些调整，并重新评测。每个组件 —— Linux、Apache、PHP 或 MySQL —— 都有各种各样的需求。分别理解各个组件可以帮助减少可能会导致应用程序速度变慢的瓶颈。

好东西..对我来说是巨大的精神食粮 感谢版主分享

不错，好东西。收藏了，谢谢LZ

当这些都做完了，考虑缓存，如 Memcached
再考虑 表的切分，search 的分离等。