6.4 实用技巧

li3p

所有的基础理论和建议我们都介绍了，是时候言归正传了。当你注意到你的服务器变慢了，你能做些什么呢？你该如何确定瓶颈所在？有什么工具可以借助？解决问题的思路又是什么？
第一步是在操作系统级别分辨出瓶颈的类型。通过使用标准的操作系统工具，尝试确定哪种系统资源负担过重？使用top, vmstat或者Windows的任务管理器，检查机器的CPU使用情况。如果CPU负载已经达到或者接近100%，显然是CPU运算能力用尽。使用top来查证哪个进程该主要对CPU利用率承担责任。（如果你对操作系统的性能工具经验不足，那么可向一个合格的系统管理员咨询。）
如果实际上是MySQL本身耗用了大量的CPU时间，有几种技巧你可以用来尝试降低CPU的负载。具体可以查看查看本章后面的6.4.2小节。如果占用大部分CPU时间的进程不是mysqld，无疑你必须先去解决一个与MySQL无关的问题。也许这是一个野进程或者只是一个应该被迁移到另外一台机器的进程。无论是哪一种，都不是MySQL的问题，所以从我们的角度看这个问题就已经是“解决”了。
如果CPU非常忙，但是没有什么进程或者进程组明显的占用了大部分CPU时间，就查看一下CPU的时间系统时间和用户时间的分配情况。如果发现大量的CPU时间被异乎寻常的用于系统（内核）任务，这可能意味着MySQL的配置问题亦或完全不相干的其他原因。本章后的6.4.4小节给出了一个为什么MySQL会给内核带来高负载的例子。
如果CPU相对空闲但是要经常的等待磁盘操作，查看6.4.1小节。通过查看vmstat 和/或 iostat的输出你就会发现这一点。如果CPU是在等待因为交换活动引起的磁盘操作，跳到6.4.3小节。
6.4.1 解决 I/O 瓶颈
磁盘（I/O）往往是MySQL性能问题中最常见的瓶颈。导致这个瓶颈的原因通常是因为低效的查询语句－意味着MySQL必须读取过多的行来定位你所感兴趣的数据。通常这表示你的查询语句没有用上索引，或者用到了一个索引但是此索引对这个特定的查询并非特别有效。在进一步讨论之前，请回顾一下第五章的内容。
诊断一个查询有没有用上索引比较简单。如果你已经打开了耗时查询日志（参见第五章的5.3小节）并且设置log-long-format选项，MySQL自动的会记录任何没有用上索引的查询。你的确需要从这个查询开始：使用EXPLAIN语句，并在该查询有多种编写方法时做一下简单的性能评测。
当你已经查看了所有的耗时查询并对其进行了修正之后，下一件事情就是查看那些更隐蔽的查询语句。在某些情况下，查询的确使用了一个索引而且执行的的相对快速，以至于MySQL从没有认为它是一个耗时查询，但从性能的角度来看，实际上是使用了一个错误的索引。可能存在另外的索引能够让MySQL进一步降低对磁盘I/O的需求。
6.4.1.1 错误的索引
找出使用了错误索引的查询语句更像一个挑战。这要求你对你的数据和运行其上查询语句了然于胸。一个来自真实世界的例子也许能够帮助我们诠释隐蔽的问题究竟有多隐蔽。
Jeremy使用mod_log_sql 这个Apache的模块来记录所有的网站点击到一个名为access_jeremy_zawodny_com 的MyISAM表中。这个表大约有1.3G，包含了超过600万条记录，看起来就像这个样子：
+------------------+----------------------+------+-----+---------+-------+| Field            | Type                 | Null | Key | Default | Extra |+------------------+----------------------+------+-----+---------+-------+| agent            | varchar(255)         | YES  | MUL | NULL    |       || bytes_sent       | int(10) unsigned     | YES  |     | NULL    |       || child_pid        | smallint(5) unsigned | YES  |     | NULL    |       || cookie           | varchar(255)         | YES  |     | NULL    |       || request_file     | varchar(255)         | YES  |     | NULL    |       || referer          | varchar(255)         | YES  |     | NULL    |       || remote_host      | varchar(50)          | YES  | MUL | NULL    |       || remote_logname   | varchar(50)          | YES  |     | NULL    |       || remote_user      | varchar(50)          | YES  |     | NULL    |       || request_duration | smallint(5) unsigned | YES  |     | NULL    |       || request_line     | varchar(255)         | YES  |     | NULL    |       || request_method   | varchar(6)           | YES  |     | NULL    |       || request_protocol | varchar(10)          | YES  |     | NULL    |       || request_time     | varchar(28)          | YES  |     | NULL    |       || request_uri      | varchar(255)         | YES  | MUL | NULL    |       || server_port      | smallint(5) unsigned | YES  |     | NULL    |       || ssl_cipher       | varchar(25)          | YES  |     | NULL    |       || ssl_keysize      | smallint(5) unsigned | YES  |     | NULL    |       || ssl_maxkeysize   | smallint(5) unsigned | YES  |     | NULL    |       || status           | smallint(5) unsigned | YES  |     | NULL    |       || time_stamp       | int(10) unsigned     | YES  | MUL | NULL    |       || virtual_host     | varchar(50)          | YES  |     | NULL    |       |+------------------+----------------------+------+-----+---------+-------+
其中的agent，time_stamp，request_uri和 remote_host四个列上分别建有独立的索引。这样做的意图就是为了能够提供一种有效的途径，来分别基于时间、浏览器类型、和请求的文档（request_uri），或者客户端主机地址（remote_host）来生成统计信息。这四个索引分别对应上面的列。
大多数查询运行非常块，但是一个特定查询就有问题了。它看起来执行的时间要比预期的长。在重复执行并观察vmstat命令的输出之后，很明显的可以确定很多的时间花费在了等待磁盘上。这个查询试图找出一个特定的客户端在特定的时间范围内－通常是一天，请求了那些页面？这个查询会为每个在过去的一天中有请求的客户端执行一次。查询看起来是这个样子的：
  select request_uri from access_jeremy_zawodny_com   where remote_host = '24.69.255.236'     and time_stamp >= 1056782930     and time_stamp order by time_stamp asc
通过EXPLAIN来运行查询会发现非常有意思：
mysql> explain select request_uri from access_jeremy_zawodny_com    ->    where remote_host = '24.69.255.236'    ->      and time_stamp >= 1056782930    ->      and time_stamp     -> order by time_stamp asc \G*************************** 1. row ***************************        table: access_jeremy_zawodny_com         type: refpossible_keys: time_stamp,remote_host          key: remote_host      key_len: 6          ref: const         rows: 4902        Extra: Using where; Using filesort1 row in set (0.00 sec)
MySQL选择使用在remote_host 列上的索引。但是它并不总是一直使用这个索引。有时它会决定使用在time_stamp列上的索引。这里有个例子：
mysql> explain  select request_uri from access_jeremy_zawodny_com    ->    where remote_host = '67.121.154.34'    ->      and time_stamp >= 1056782930    ->      and time_stamp     -> order by time_stamp asc \G*************************** 1. row ***************************        table: access_jeremy_zawodny_com         type: rangepossible_keys: time_stamp,remote_host          key: time_stamp      key_len: 5          ref: NULL         rows: 20631        Extra: Using where1 row in set (0.01 sec)
这两个查询唯一的区别就是我们所查找的IP地址。在两种情况下，MySQL的查询优化器针对两个索引，分别估算了满足这个查询所要读取的记录条数。在第一个例子中，它判定出remote_host字段为24.69.255.236的记录要少于24小时的时间范围内的记录数。在第二个例子与此相反，MySQL判定时间范围条件所返回的记录数要少于主机地址所返回的记录数。
多尝试几个不同的IP地址，用不了一会儿就会发现MySQL为某次查询作出了错误的判断。它在实际上使用time_stamp索引更快的情况先仍然选择使用remote_host －尽管remote_host 需要读取的行数最少。这怎么可能？
[4] 通过在查询语句中使用 USE INDEX 子句，可以测试每个索引的性能。
优化器所基于的假设是所有的行的读取时间大致上是相等的。但是这个假设并非总是成立的。考虑一下在这个MySQL表中的数据是如何存储的：Apache时刻都在将请求信息记入这个表中，而且已经记了一年之久。记录行也从来没有清除过，所以表中和磁盘上的记录数据已经是按照时间戳进行排序的了（假设在磁盘的碎片最小的情况下）。
一旦你有个像这种情况一样的海量表，规则就有些不同了。如果我们假设某个特定IP的访问记录被均匀的分布在上百万条的记录中，显然使用remote_host索引会引起多得多的磁盘寻道操作。并且因为磁盘随即存取要比连续读取数据块慢，这会导致MySQL花在实际工作上（评估更少的行）的时间更少，而磁盘做了更多的工作－使用昂贵的磁盘寻道时间还会拖慢其它的查询语句。
在日志应用程序中，当你经常像基于其他索引字段一样基于时间范围查询数据的时候，问题就更加突出而且没有一个普遍适用的解决方案。如果你能够洞察你的数据并将这种洞察能力加入到生成查询语句的软件中，就会大有帮助。这个软件就可以通过配置来告诉MySQL应该使用哪个索引。比如，如果你的软件知道特定IP地址最近非常不活跃，它就可以强制MySQL使用time_stamp范围索引：
SELECT ... USE_INDEX(time_stamp) ...
这并非一个理想的解决方案，但是恰当的使用它将会非常有效。
6.4.1.2 临时表（Temporary tables）
另外一个在耗时查询日志中无法反映出来的问题，就是对基于磁盘的临时表的过多使用。在EXPLAIN命令的输出中，你经常会看到Using temporary。这就表示MySQL 必须创建一个临时表来完成这个查询，然而它并不会告诉你这个临时表是创建在内存中的还是磁盘上的。这取决于表的大小，以及MySQL中的tmp_table_size变量的值。
如果构建此临时表所需的空间少于或者等于tmp_table_size，MySQL就将它放在内存里，而不是将数据写入磁盘并再次读出来，导致额外的负载和耗时。然而如果空间需求超过了tmp_table_size，MySQL就会在tmpdir目录下创建基于磁盘的临时表，缺省的tmp_table_size大小是32MB。
找出这种问题发生的频率，可以比较Created_tmp_tables 和 Created_tmp_disk_tables两个计数器的相对大小。
mysql> SHOW STATUS LIKE 'Created_tmp_%';+-------------------------+-------+| Variable_name           | Value |+-------------------------+-------+| Created_tmp_disk_tables | 18    || Created_tmp_tables      | 203   || Created_tmp_files       | 0     |+-------------------------+-------+
如果你发现创建了很多基于磁盘的临时表，可尝试在安全的限度内增加tmp_table_size的值。谨记如果这个值设的过高，会导致大量的磁盘交换操作，或者MySQL会在过多的线程同时分配内存临时表的情况下耗尽内存。另外一种办法，确保tmpdir指向的目录位于一个没有过多的I/O操作的非常快速的硬盘上。
作为最后的手段，尝试使用一个快速临时文件系统（比如RAMDISK，或者MDMFS，或者其它被你的操作系统所支持的基于内存的文件系统），并在启动MySQL的时候，设置$TMPDIR环境变量指向这个文件系统。
6.4.1.3 缓存（Caching）
有时即使你的查询已经优化了并能使用最有效的索引，你还是可能会遭遇I/O瓶颈。简单的同时运行很多的查询，无论查询本身多么高效，对磁盘而言都会因为查询数量太多而跟不上节奏。如果情况果真如此，那么就是时候考虑缓存了。
最简单的缓存方式就是确保你正在使用MySQL的查询缓存。MySQL从 4.0就提供这个功能，查询缓存将经常执行的SELECT查询的结果保存在内存中，从而使得MySQL不需要执行任何磁盘I/O操作。第五章的5.4.4小节有更详细的信息。
更进一步，你可能需要在应用程序一级进行缓存。如果数据并不会被经常改变，在某个时候查询一次并将其保存在内存或者本地磁盘上，直到再次对其进行查询。
6.4.1.4 分散负载
如果你已经检查了上面列出的病因并实施了对应的对策，那么看来你需要更加有效的分散I/O负载了。如前面所述，安装更快转速和更短寻道时间的硬盘可能会有帮助。使用RAID（尤其是RAID 0，RAID 5，RAID 10）将I/O负载分担到多个磁盘上，也可能会消弥或者降低I/O瓶颈。
如果你有多个磁盘并且无法简单的配置RAID，还有一个选择，就是尝试人工的均衡磁盘I/O。花些时间使用iostat或者systat（取决于你的操作系统）来找到I/O的主要负载在哪里。如果你将你所有的MySQL数据都放置在单个硬盘上，你可以尝试将其数据迁移到另外一个磁盘。如果I/O活动的主要部份都是集中在一小组表上，考虑将这些表迁移到一个独立的磁盘上。
另外一个途径就是将占主要地位的随机I/O操作，与大多数的串行I/O操作分开。将各种日志如二进制日志、复制传递日志，以及InnoDB事务日志等保存在与实际数据文件不同的独立硬盘上。到最后这就是一个尝试、出错再尝试的游戏。就像做性能评估一样，眼睛紧盯着评测值并且不要一次改变太多的东西。
最后，复制永远是一个可行的办法。如果你已经遇到单纯的超过单台机器处理能力的问题，复制就是一个最小的分治解决方案。阅读第七章可学到所有有关复制的信息。
6.4.2 解决CPU瓶颈
MySQL中CPU瓶颈很难被跟踪到。不像有些数据库服务器，MySQL没有为每个查询统计实际运行时间以及等待磁盘I/O完成时间等信息。
幸运的是这也不因此就成了个猜谜游戏。如果你在耗时查询日志中看到一个慢速查询日志并怀疑它可能比较耗CPU，就对其进行性能评测。快照一组MYSQL super-smak，一连运行几千次。然后，在另外一个终端窗口，使用top/vmstat或者其它你喜欢的系统检测工具查看负载。如果即使是在并发连结相对较低的情况下，CPU也迅速的达到100%的利用率，该查询语句就是非常有可能是耗CPU的。
如果一开始你发现自己找到了为数众多的慢速查询，你该如何确定从哪条查询入手？非常简单：查找那些要检查很多行（一千，上万，或者更多）的查询，同时关注那些使用了某些MySQL内置数据处理函数的查询。通常值得怀疑的内容有：
·         格式化或者比较日期
·         加密数据或者哈希计算
·         执行复杂的比较，比如正则表达式
通常你很容易就会发现，每小时在上万的值上计算MD5哈希会过多的耗费CPU时间。通过将计算逻辑迁移到进行数据库查询的应用服务器上，你就能释放出更多的CPU时间，让MySQL专心完成只有它才能有效完成的工作。
如果你无法简单的通过迁移计算逻辑到应用层来减少MySQL的压力，你总是可以选择通过硬件升级来解决问题。有一两种办法能够达到目的。你可以简单的升级服务器的CPU，或者在有可用插槽的情况下增加更多的CPU。除此之外，你也许会发现增加新的服务器也不昂贵而且会有更高的扩展性，只要复制数据到其上，然后在各服务器之间分摊负载。为了自己的利益偶尔利用利用摩尔定律不是什么坏事。
MyISAM类型的表导致的高CPU利用率并非总是坏事。这也许意味着你所查询的表已经被整个的缓存在了操作系统的缓存中。这可能是也可能不是坏事。这肯定要比从磁盘读取数据来的好，但每次MySQL向操作系统请求一块数据的时间，如果能用在出来其它请求上就会更好。迁移到InnoDB或者BDB表引擎可以让MySQL自己来缓存数据，因此他们不需要向操作系统来请求记录数据。
6.4.3 解决内存瓶颈
在MySQL服务器上调整内存的使用是个瓷器活儿。如前所述，MySQL有一些全局内存缓冲区和一些每线程缓存。平衡性能获益的技巧来自于通过调整的全局缓存，满足指定数量的并发用户的要求。作为最低的条件，你应该拥有的可用内存，应该至少能够满足MySQL的全局缓存加上你想最多的提供的并发连接数量与每线程缓存的和。
用数学公式表达如下：
min_memory_needed = global_buffers + (thread_buffers * max_connections)
而thread_buffers 包含以下的缓存内容：
sort_buffer
myisam_sort_buffer
read_buffer
join_buffer
read_rnd_buffer
global_buffers则包含：
key_buffer
innodb_buffer_pool
innodb_log_buffer
innodb_additional_mem_pool
net_buffer
之所以说这是最低的内存需求，是因为理想情况下你应该留出一些内存给操作系统使用。对于MyISAM类型的表而言，“后备”的内存通常会被用来存放MyISAM的数据文件（.MYD）。
线程内存除了被分配来处理查询的内存之外，线程本身也需要点可用内存。thread_stack变量控制着这个开销。对大多数平台而言，192KB是缺省值。
[5]
[5]如果你恰巧在FreeBSD平台上使用LinuxThreads线程库，这个值是硬编码在LinuxThreads库里的。修改MySQL的thread_stack参数不会产生什么效果。你必须重新编译库来改变运行栈的大小。
有种潜在的问题也会经常见到。设想一下你的服务器有1G的物理内存，混合运行MyISAM和InnoDB表－以MyISAM为主。为了获得最高的性能，你在通过mytop （参见附录B）查看键有效性信息之后，配置512M内存给key_buffer；同时通过SHOW INNODB STATUS （参见附录A）查看缓冲池和内存统计之后，分配256M内存给innodb_buffer_pool。余下的256M内存用来在操作系统级别缓存数据文件，以及以按需分配的模式给每线程分配所需的缓冲。MySQL服务器服务着相对较少的并发用户，大多数情况下20－50个连接，而且每线程的缓冲大小按照缺省大小分配。
一切都工作的很好，直到有几个新的应用程序也开始使用这个MySQL服务器。新的应用程序需要相当数量的并发连接。不再是平均20－50个，服务器处理的并发连接数量达到了300－400个。当这一切发生时，少数连接同时所需的每线程缓冲区（比如sort_buffer）的开销增加变的相当可观。
这会引发一系列恶劣的事件。如果大量的线程需要分配额外的内存，比如可能是因为服务器的处理负载很重。这会导致MySQL的内存分配引发操作系统开始虚拟内存交换，这更加剧了性能的下降，也意味着每个查询所需要的查询时间更长。当查询执行的更慢，每线程的所需的内存开销还会增加。这成了一个恶性循环。
唯一的解决方案就是恢复系统内存和MySQL所需内存之间的平衡。这意味着采取以下方案之一：
·         增加更多的内存
·         减少 max_connections
·         减少某些每线程缓冲区的大小
要未雨绸缪。监控你服务器的内存使用情况。通过计算来保证在最恶劣（达到max_connections并且每个线程分配都分配额外内存）的情况下，你的内存仍然有周旋的空间。
6.4.4 解决内核瓶颈
虽然并不常见，仍可能会遇到一种情况：看起来MySQL并没有得到很多的CPU时间，而服务器仍然相当繁忙。CPU可用的时间非常少。进一步查找问题，你发现大量的CPU时间耗费再“系统”级而非“用户”级别或者保持空闲。这通常意味着MySQL的某些不寻常的操作考验着系统内核－通常是创建或者销毁线程。
这种情况在Yahoo!发布一个新网站时有发生过。在2002年的九月份，Yahoo！的工程师忙于创建一个名为remember.yahoo.com
[6]
的九.一一事件纪念网站。在网站上，每个人都可以通过选择图象和加入自创的文字而创建一块纪念“瓦片”。瓦片也可以被后来访问此网站的任何人看到。为了能够尽可能快的完成项目，这个网站使用了标准的开源工具构建，包括FreeBSD，Apache，PHP以及MySQL。
[6]整个网站是由有限几个Yahoo的工程师在大概两周时间内，利用业余时间构思、设计、构建并发布的。
从体系结构上看是相对直观的，但为了说明主要问题我们还要简化一下。一组前端Web服务器通过硬件负载均衡器连接到一个MySQL从服务器上。通过到从服务器的连接，服务器可以检索必要的信息来显示瓦片。当一个访问者创建了一块瓦片，Web服务器需要连接到主服务器插入若干条数据记录。主服务器当时配置强劲：双1.2G的CPU，2G的内存，一个SCSI的RAID5阵列。
在峰值情况下，有大概25－30台Web服务器需要使用MySQL主服务器。每个Web服务器上配置了大概30－40个Apache进程。这意味着主服务器需要支持超过1,000个并发连接。通过这些信息能够计算出主服务器上实际所需的资源，设计者选择了一种简单的途径。不幸的是，Web应用程序（使用PHP编写）配置成使用持久连接（persistent connection）。因此，在主服务器持久的连接要断开，对应的wait_timeout设的非常低－大概10秒钟。
总的来说，这种方案是可以工作的。空闲的连接在10秒钟后会被断开。主服务器上的连接数量保持在200以下，多数的资源都是空闲的。但是问题是：主服务器上CPU使用率一直居高不下。多数情况下空闲的CPU时间都少于10%，而且接近50%的CPU时间花在了系统级（而不是用户级）任务上。
抓破头皮想了一个多小时，不断的查看系统日志和SHOW STATUS命令的输出。Jermey的脑袋里终于灵光一现：Threads_created的值非常大并且以超过警戒的速率在增加。操作系统的内核都忙于创建和销毁线程的工作上，以致于吃掉了MySQL有效的利用CPU的能力。
状况搞清楚了，解决起来也变得简单。将thread_cache 的数量 从0增加到大约150，性能的提升就立杆见影。系统的CPU利用率降到了大概10%以下，因此MySQL得到了很多的CPU时间可用。MySQL所需的并非全部的CPU时间，最终整个服务器都保持着20%的可用CPU时间－绰绰有余。



本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/7114/showart_264496.html