MYSQL调优文档1
时间：2008-12-15 来源：bbzsxjj
1 前言
在Web应用程序体系架构中，数据持久层（通常是一个关系数据库）是关键的核心部分，它对系统的性能有非常重要的影响。MySQL是目前使用最多的开源数据库，但是MySQL数据库的默认设置不能充分发挥现有硬件性能，因此在项目应用中使用MySQL数据库必须进行必要的优化。目前有 3 种方法可以加快 MySQL 服务器的运行速度，效率从低到高依次为：
1、优化服务器硬件
2、对 MySQL 进程的设置进行调优
3、对SQL语句进行优化
升级硬件通常是我们的第一考虑，主要原因是数据库会占用大量资源。不过这种解决方案也就仅限于此了。实际上，通常可以让中央处理器（CPU）或磁盘速度加倍，也可以加更多的内存来实现。
第二种方法是对 MySQL 服务器（也称为 mysqld）进行调优。对这个进程进行调优意味着适当地分配内存，并让 mysqld 了解将会承受何种类型的负载。加快磁盘运行速度不如减少所需的磁盘访问次数。类似地，确保 MySQL 进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务（如处理临时磁盘表或打开和关闭文件）上的时间。对 mysqld 进行调优是本文的重点。
最好的方法是确保查询已经进行了优化。这意味着对表应用了适当的索引，查询是按照可以充分利用 MySQL 功能的方式来编写的。
2 优化硬件
1）如果需要大表(>2G)，应该考虑使用64位硬件，像Alpha,Sparc或者IA64。由于MySQL使用大量内部的64位整型，64位的CPU将有更好的表现。
2）对于大数据库，优化顺序通常是内存，硬盘，CPU。
3）更多的内存可以通过把更多的索引页保留在内存中来提高索引更新的速度 。
4）如果不使用事务安全表或者有一个大硬盘并且想避免大文件检查，有一个UPS是个好办法，它能在断电的时候使系统正常关闭。
5）对于数据库运行在专用的服务器上的系统来说，应该考虑1G的网络，传输延迟和吞吐量同样重要。
3 优化mysql进程设置
3.1 记录慢速查询
在一个 SQL 服务器中，数据表都是保存在磁盘上的。索引为服务器提供了一种在表中查找特定数据行的方法，而不用搜索整个表。当必须要搜索整个表时，就称为表扫描。通常来说，您可能只希望获得表中数据的一个子集，因此全表扫描会浪费大量的磁盘 I/O，因此也就会浪费大量时间。当必须对数据进行连接时，这个问题就更加复杂了，因为必须要对连接两端的多行数据进行比较。
当然，表扫描并不总是会带来问题；有时读取整个表反而会比从中挑选出一部分数据更加有效（服务器进程中查询规划器用来作出这些决定）。如果索引的使用效率很低，或者根本就不能使用索引，则会减慢查询速度，而且随着服务器上的负载和表大小的增加，这个问题会变得更加显著。执行时间超过给定时间范围的查询就称为慢速查询。
我们可以配置 mysqld 将这些慢速查询记录到适当命名的慢速查询日志中。管理员然后会查看这个日志来帮助他们确定应用程序中有哪些部分需要进一步调查。
设置方法：
|
[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-not-using-indexes |
这3个设置可以记录执行时间超过 5 秒和没有使用索引的查询。慢速查询日志都保存在 MySQL 数据目录中，名为 hostname-slow.log。如果希望使用一个不同的名字或路径，可以在 my.cnf 中使用 log-slow-queries = /home/data/log//file 实现此目的。
3.2 对查询进行缓存
很多 LAMP 应用程序都严重依赖于数据库，但却会反复执行相同的查询。每次执行查询时，数据库都必须要执行相同的工作 —— 对查询进行分析，确定如何执行查询，从磁盘中加载信息，然后将结果返回给客户机。MySQL 有一个特性称为查询缓存，它将查询结果保存在内存中。在很多情况下，这会极大地提高性能。
可以将my.cnf文件的中 query_cache_size = 32M 修改为 query_cache_size = 64M或更大，来增大启用的查询缓存。
在启用查询缓存之后，重要的是要理解它是否得到了有效的使用。MySQL 有几个可以查看的变量，可以用来了解缓存中的情况。
|
mysql> show status like 'qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 3 | | Qcache_free_memory | 31452528 | | Qcache_hits | 2775714 | | Qcache_inserts | 487019 | | Qcache_lowmem_prunes | 339481 | | Qcache_not_cached | 117528584 | | Qcache_queries_in_cache | 480 | | Qcache_total_blocks | 974 | +-------------------------+-----------+ 8 rows in set (0.00 sec)
这些项的解释如下所示： 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 占用了很长时间，那就说明缓存太大了。
3.3 强制限制
可以在 mysqld 中强制一些限制来确保系统负载不会导致资源耗尽的情况出现。可以在my.cnf中添加以下内容：
|
set-variable=max_connections=1024 set-variable=wait_timeout=7200 set-variable=max_connect_errors=2048 |
连接最大个数是在第一行中进行管理的。与 Apache 中的 MaxClients 类似，其想法是确保只建立服务允许数目的连接。要确定服务器上目前建立过的最大连接数，请执行 SHOW STATUS LIKE 'max_used_connections'。
第 2 行告诉 mysqld 终止所有空闲时间超过 7200 秒的连接。在 LAMP 应用程序中，连接数据库的时间通常就是 Web 服务器处理请求所花费的时间。有时候，如果负载过重，连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取！
最后一行是一个安全的方法。如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到 FLUSH HOSTS 之后才能运行。默认情况下，10 次失败就足以导致锁定了。将这个值修改为 2048 会给服务器足够的时间来从问题中恢复。如果重试 2048 次都无法建立连接，那么使用再高的值也不会有太多帮助，可能它根本就无法连接。
3.4 显示线程使用统计信息
|
mysql> show status like 'thread%'; +-------------------+--------+ | Variable_name | Value | +-------------------+--------+ | Threads_cached | 62 | | Threads_connected | 2 | | Threads_created | 233455 | | Threads_running | 1 | +-------------------+--------+ 4 rows in set (0.00 sec) |
此处重要的值是 Threads_created，每次 mysqld 需要创建一个新线程时，这个值都会增加。如果这个数字在连续执行 SHOW STATUS 命令时快速增加，就应该尝试增大线程缓存。例如，可以在 my.cnf 中使用 thread_cache_size = 64 来实现此目的。
3.5 确定关键字效率
|
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。
3.6 显示排序统计信息
|
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 = 64M 将排序缓冲区设置为 64MB。
3.7 确定表扫描比率
MySQL 也会分配一些内存来读取表。理想情况下，索引提供了足够多的信息，可以只读入所需要的行，但是有时候查询（设计不佳或数据本性使然）需要读取表中大量数据。要理解这种行为，需要知道运行了多少个 SELECT 语句，以及需要读取表中的下一行数据的次数（而不是通过索引直接访问）。实现这种功能的命令如下：
|
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 = 16M。
4 优化SQL语句
把SQL用在它擅长的地方，用应用程序做其他事。用SQL服务器来做：
●通过where条件找到行
●联合表
●GROUP BY
●ORDER BY
●DISTINCT
不要用SQL服务器做：
●检查数据(像日期)
●作为计算器
5 必不可少的工具
尽管在了解具体设置时，SHOW STATUS 命令会非常有用，但是您还需要一些工具来解释 mysqld 所提供的大量数据。发现以下几个工具是必不可少的。
5.1 mytop
大部分系统管理员都非常熟悉 top 命令，它为任务所消耗的 CPU 和内存提供了一个不断更新的视图。 mytop 对 top 进行了仿真；它为所有连接上的客户机以及它们正在运行的查询提供了一个视图。mytop 还提供了一个有关关键字缓冲区和查询缓存效率的实时数据和历史数据，以及有关正在运行的查询的统计信息。这是一个很有用的工具，可以查看系统中（比如 10 秒钟之内）的状况，您可以获得有关服务器健康信息的视图，并显示导致问题的任何连接。具体安装使用：
|
##安装 apt-get install mytop ##使用 mytop -u root -p 123456 -d mydb_name // -d 指定要监控的数据库
MySQL on localhost (5.0.51a-log) up 5+18:58:20 [16:25:49] Queries: 388.6M qps: 814 Slow: 0.0 Se/In/Up/De(%): 01/00/00/00 qps now: 537 Slow qps: 0.0 Threads: 5 ( 2/ 61) 00/00/00/00 Key Efficiency: 100.0% Bps in/out: 0.0/ 3.0 Now in/out: 8.4/ 1.3k
Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- ---------- 40634615 root localhost gaokao 0 Query show full processlist 40697871 gaokao 121.194.3.133 gaokao 0 Sleep 40697910 gaokao 121.194.3.143 gaokao 0 Execut select count(1) from (select distinct zyid,fsyear,zyname from (select 40254173 gaokao 219.224.99.251 gaokao 2171 Sleep 40440950 root localhost 3466 Sleep
第一行显示了主机名称，还有至今 MySQL 的运行时间 (以 days+hour:minutes:seconds 为格式)。 第二行的 Queries 显示了至今执行的 SQL 查询语句总数，另外还有目前每秒处理的查询数和速度。 第三行的 Key Efficiency 就是传说中的缓存命中率了，如果太低了你可能要调整你的 MySQL 设置，或者调整一下表的结构，后面还有目前的进出速度。 最下方的区域就是目前链接到数据库的各个线程，你可以按 k 杀死一个线程，或者按 f 了解特定线程的信息。
|
5.2 mysqlard
mysqlard 是一个连接到 MySQL 服务器上的守护程序，负责每 5 分钟搜集一次数据，并将它们存储到后台的一个 Round Robin Database 中。有一个 Web 页面会显示这些数据，例如表缓存的使用情况、关键字效率、连接上的客户机以及临时表的使用情况。尽管 mytop 提供了服务器健康信息的快照，但是 mysqlard 则提供了长期的健康信息。作为奖励，mysqlard 使用自己搜集到的一些信息针对如何对服务器进行调优给出一些建议。
5.3 mysqlreport
搜集 SHOW STATUS 信息的另外一个工具是 mysqlreport。其报告要远比 mysqlard 更加复杂，因为需要对服务器的每个方面都进行分析。这是对服务器进行调优的一个非常好的工具，因为它对状态变量进行适当计算来帮助确定需要修正哪些问题。
5.4 iostat
iostat是I/O statistics（输入/输出统计）的缩写，iostat工具将对系统的磁盘操作活动进行监视。它的特点是汇报磁盘活动统计情况，同时也会汇报出CPU使用情况。 我们可以根据iostat提供的统计数据，分析磁盘读写性能是否为数据库瓶颈。
|
##安装iostat apt-get install sysstat //iostat是sysstat套件工具之一
##每3秒抓取一次数据 iostat -x 3
Linux 2.6.18-4-686 (autodeskftpd1) 07/03/2008
avg-cpu: %user %nice %system %iowait %steal %idle 1.22 0.00 1.00 0.08 0.00 97.70
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.06 0.91 0.49 0.77 52.20 13.48 52.27 0.01 9.87 3.42 0.43
avg-cpu: %user %nice %system %iowait %steal %idle 0.00 0.00 0.08 0.33 0.00 99.58
rrqm/s: 每秒进行 merge 的读操作数目。即 delta(rmerge)/s wrqm/s: 每秒进行 merge 的写操作数目。即 delta(wmerge)/s r/s: 每秒完成的读 I/O 设备次数。即 delta(rio)/s w/s: 每秒完成的写 I/O 设备次数。即 delta(wio)/s rsec/s: 每秒读扇区数。即 delta(rsect)/s wsec/s: 每秒写扇区数。即 delta(wsect)/s rkB/s: 每秒读K字节数。是 rsect/s 的一半，因为每扇区大小为512字节。 wkB/s: 每秒写K字节数。是 wsect/s 的一半。 avgrq-sz: 平均每次设备I/O操作的数据大小 (扇区)。即 delta(rsect+wsect)/delta(rio+wio) avgqu-sz: 平均I/O队列长度。即 delta(aveq)/s/1000 (因为aveq的单位为毫秒)。 await: 平均每次设备I/O操作的等待时间 (毫秒)。即 delta(ruse+wuse)/delta(rio+wio) svctm: 平均每次设备I/O操作的服务时间 (毫秒)。即 delta(use)/delta(rio+wio) %util: 一秒中有百分之多少的时间用于 I/O 操作，或者说一秒中有多少时间 I/O 队列是非空的。即 delta(use)/s/1000 (因为use的单位为毫秒)
我们着重关注 %util ，如果 %util 接近 100%，说明产生的I/O请求太多，I/O系统已经满负荷，该磁盘可能存在瓶颈。 |
相关阅读 更多 +
