MySQL服务器调优的几个技巧

查看评论  发表评论2012年02月05日 19:55分         作者：扶凯      来源：互联网

如今,开发人员不断地开发和部署使用 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 每次插入一个查询时就增大.命中次数除以插入次数与hits 的次数之和就是命中比率;用 1 减去这个值就是不命中率.在上面这个例子中,大约有 87% 的查询都在缓存中命中.Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

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 进程,也可以应用于单个客户机会话.