MySQL 服务器调优
上一篇 / 下一篇 2009-09-02 15:24:04 / 个人分类:性能测试
关于 MySQL 调优51Testing软件测试网#l8B1T!]IlGp
51Testing软件测试网_r7f'x3v有 3 种方法可以加快 MySQL 服务器的运行速度,效率从低到高依次为:51Testing软件测试网&b nkI V
- 替换有问题的硬件。
- 对 MySQL 进程的设置进行调优。
- 对查询进行优化。
替换有问题的硬件通常是我们的第一考虑,主要原因是数据库会占用大量资源。不过这种解决方案也就仅限于此了。实际上,您通常可以让中央处理器(CPU)或磁盘速度加倍,也可以让内存增大 4 到 8 倍。
l9A1Da c.r4ZS0~D&Q_lA]A0第二种方法是对 MySQL 服务器(也称为mysqld
)进行调优。对这个进程进行调优意味着适当地分配内存,并让mysqld
了解将会承受何种类型的负载。加快磁盘运行速度不如减少所需的磁盘访问次数。类似地,确保 MySQL 进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务(如处理临时磁盘表或打开和关闭文件)上的时间。对mysqld
进行调优是本文的重点。
最好的方法是确保查询已经进行了优化。这意味着对表应用了适当的索引,查询是按照可以充分利用 MySQL 功能的方式来编写的。尽管本文并没有包含查询调优方面的内容(很多著作中已经针对这个主题进行了探讨),不过它会配置mysqld
来报告可能需要进行调优的查询。
9E#Oo$~id0虽然已经为这些任务指派了次序,但是仍然要注意硬件和mysqld
的设置以利于适当地调优查询。机器速度慢也就罢了,我曾经见过速度很快的机器在运行设计良好的查询时由于负载过重而失败,因为mysqld
被大量繁忙的工作所占用而不能服务查询。51Testing软件测试网/e]@ E7r.?xM.@
记录慢速查询51Testing软件测试网8Xr+`f!tMv
51Testing软件测试网-Fd}:{~0q在一个 SQL 服务器中,数据表都是保存在磁盘上的。索引为服务器提供了一种在表中查找特定数据行的方法,而不用搜索整个表。当必须要搜索整个表时,就称为表扫描。通常来说,您可能只希望获得表中数据的一个子集,因此全表扫描会浪费大量的磁盘 I/O,因此也就会浪费大量时间。当必须对数据进行连接时,这个问题就更加复杂了,因为必须要对连接两端的多行数据进行比较。51Testing软件测试网m&oXyXeKC
51Testing软件测试网tQj LE%Z当然,表扫描并不总是会带来问题;有时读取整个表反而会比从中挑选出一部分数据更加有效(服务器进程中查询规划器用来作出这些决定)。如果索引的使用效率很低,或者根本就不能使用索引,则会减慢查询速度,而且随着服务器上的负载和表大小的增加,这个问题会变得更加显著。执行时间超过给定时间范围的查询就称为慢速查询。51Testing软件测试网N B1J3I X^jf MWw
5N"L*~p*E0您可以配置mysqld
将这些慢速查询记录到适当命名的慢速查询日志中。管理员然后会查看这个日志来帮助他们确定应用程序中有哪些部分需要进一步调查。清单 1 给出了要启用慢速查询日志需要在 my.cnf 中所做的配置。
0A @
K*w1H/W v9X*p
w0
2d"Yh2uU@!hv1B0清单 1. 启用 MySQL 慢速查询日志
8|*X+` {6[G7U_DR0
[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 |
dq:LX#d i08dL2}sgzM051Testing软件测试网7U j@V%k6Y3aN(q{
这三个设置一起使用,可以记录执行时间超过 5 秒和没有使用索引的查询。请注意有关log-queries-not-using-indexes
的警告:您必须使用 MySQL 4.1 或更高版本。慢速查询日志都保存在 MySQL 数据目录中,名为hostname-slow.log。如果希望使用一个不同的名字或路径,可以在 my.cnf 中使用log-slow-queries = /new/path/to/file
实现此目的。51Testing软件测试网&y
K@%Mo6HtB
阅读慢速查询日志最好是通过mysqldumpslow
命令进行。指定日志文件的路径,就可以看到一个慢速查询的排序后的列表,并且还显示了它们在日志文件中出现的次数。一个非常有用的特性是mysqldumpslow
在比较结果之前,会删除任何用户指定的数据,因此对同一个查询的不同调用被计为一次;这可以帮助找出需要工作量最多的查询。
对查询进行缓存51Testing软件测试网kcb@,X_o
51Testing软件测试网k }4h[u$o/Y?0q很多 LAMP 应用程序都严重依赖于数据库,但却会反复执行相同的查询。每次执行查询时,数据库都必须要执行相同的工作 —— 对查询进行分析,确定如何执行查询,从磁盘中加载信息,然后将结果返回给客户机。MySQL 有一个特性称为查询缓存,它将(后面会用到的)查询结果保存在内存中。在很多情况下,这会极大地提高性能。不过,问题是查询缓存在默认情况下是禁用的。
:JP(EPaF}@D d051Testing软件测试网'B$z$q-X"Aq将query_cache_size = 32M
添加到 /etc/my.conf 中可以启用 32MB 的查询缓存。51Testing软件测试网CE|r(}by [
??9o
by3M!@8iv9n0监视查询缓存
$E2EGO Cd7K;rz_051Testing软件测试网j(QWpC$Hc'[c9o在启用查询缓存之后,重要的是要理解它是否得到了有效的使用。MySQL 有几个可以查看的变量,可以用来了解缓存中的情况。清单 2 给出了缓存的状态。51Testing软件测试网~ i~#@u8O3]'a5E
51Testing软件测试网DR"a5PO51Testing软件测试网*a-Ys {:Ct n'g c
清单 2. 显示查询缓存的统计信息51Testing软件测试网*|3M&b$}5M.zj
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) |
'f3\!r%x#^051Testing软件测试网]K*L on4q U51Testing软件测试网;]"~Ew K~ WN:p
这些项的解释如表 1 所示。
0eyO:hM _grY051Testing软件测试网M-jU/|-Y*m&]-aYm F
\0`S~$p8A:v3Yj0表 1. MySQL 查询缓存变量