High Performance MySQL 手稿
服务器优化的6.4节终于翻译完成.这一节对于服务器维护非常有用.6.4 技巧 在所有基本理论和建议之后,是时候来处...
扫描右侧二维码阅读全文
11
2008/04

High Performance MySQL 手稿

<2007-02-04>服务器优化的6.4节终于翻译完成.这一节对于服务器维护非常有用.

6.4 技巧
在所有基本理论和建议之后,是时候来处理现实世界中会遇见的问题了.当你发现你的服务器变慢,你能对它做什么?如何定位瓶颈的位置?哪些工具可用?用什么思路去处理?

第一步是在操作系统的层面来判别瓶颈的类型.使用标准的操作系统工具,来推断哪种服务器资源负载最大.使用top, vmstat, 或者Windows任务管理器(Task Manager), 检查服务器的CPU利用率. 如果它接近100%,那就显然是CPU引起的.使用 top 来验证那个进程导致CPU负载激增.(如果你对于使用操作系统性能工没有太多的经验,考虑向有经验的系统管理员请教.)

如果确实是MySQL占用了大量的CPU时间, 这里有几种技巧能够帮助你尝试减轻CPU 负载,阅读之后的 Section 6.4.2 章节. 如果占用CPU时间最大的进程不是 mysqld, 那很明显你需要解决的问题和 MySQL 无关. 也许那是一个失控的进程或者只是简单的某些程序应该被移到其他服务器上去运行. 不管怎样,这都不是MySQL问题,所以解决这类问题的方案超出了我们的讨论范围.

如果CPU非常繁忙但是却没有显示任何进程或者进程组使用大量CPU时间,那查看一下系统使用和用户使用之间的差距.如果系统(内核)任务占用了以往未出现过的特别多的CPU时间,那可能是Mysql配置问题或者其他一些不相干的事情导致的. Section 6.4.4 中有例子显示了为什么MySQL可能引起内核负载异常增高.

如果CPU相对较空那就是因为它经常在等待磁盘导致的. Section 6.4.1 有相应内容. 通过 vmstat 与/或 iostat 命令,如果显示超过一般数值很多的结果,那就是因为磁盘因素导致的.如果由于交换而导致CPU等待磁盘I/O,那么 Section 6.4.3 会有相对应的解决方案.

6.4.1 解决I/O瓶颈
磁盘 (I/O) 瓶颈是最常见的MYSQL性能问题. 它们经常由低效的查询引起,低效查询使得mysql必须读取太多你需要的行数据到本地. 通常这意味着你的查询没有使用索引, 或者它所使用的索引对实际请求没有产生任何作用.在讨论更多相关细节之前,确认你已经阅读过 Chapter 5 的有关内容.

判断一个查询是否在使用索引是比较简单的. 如果你开启了慢速查询日志(slow query log) (在 Chapter 5 Section 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.3 GB , 包含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 <= 1056869330

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 <= 1056869330

-> order by time_stamp asc \G

*************************** 1. row ***************************

table: access_jeremy_zawodny_com

type: ref

possible_keys: time_stamp,remote_host

key: remote_host

key_len: 6

ref: const

rows: 4902

Extra: Using where; Using filesort

1 row in set (0.00 sec)
MySQL 选择使用 remote_host 索引. 但是它并不总是这样选择.有时候MYSQL决定使用 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 <= 1056869330

-> order by time_stamp asc \G

*************************** 1. row ***************************

table: access_jeremy_zawodny_com

type: range

possible_keys: time_stamp,remote_host

key: time_stamp

key_len: 5

ref: NULL

rows: 20631

Extra: Using where

1 row in set (0.01 sec)
这两个请求的唯一不同点就在于我们查询的IP地址不同. 在每个情况下,MySQL的优化器估算它使用每个索引后所需读取的的行数.在第一个例子中,它判定当使用24.69.255.236 的 remote_host 作为索引时比制定24小时时间范围所需获取的行数更少.在第二个例子中恰恰相反, 它判定使用时间范围作为索引所需的行数更少.

在尝试各种IP地址之后.很快查出MySQL做了错误的选择. 在使用 remote_host 作为索引所需行数更少的情况下.他选择了 ,但是实际上使用 time_stamp 更快.[4] 这怎么可能?

[4] 使用 USE INDEX 在查询内指定条件, 你能够测试每种索引的性能.

先假设所有的行数据在读取上的时间开销大致都是一致的.当然实际情况并不是总是这样的.想像一下在MyISAM表中的数据是如何存储的. Apache将所有的请求日志都记录在表中,并且运行了一年多.所有行都没有被删除过,所以在表以及磁盘中数据已经是按照时间戳timestamp来排序的了(架设只有很小的磁盘碎片).

当你对于表中的数据了如指掌, 那情况可能会不一样. 我们假设给定IP的记录被分散在几百万行记录中, 那很明显,如果使用 remote_host 作为索引,很可能会引起更多的磁盘寻道. 既然磁盘寻道比从磁盘中读取连续数据更慢,那很自然当Mysql判定会作更少的操作(评估更少的行数)的时候,磁盘却需要做更多的寻道工作.这样,整个查询的速度就减慢了.

在日志记录型的应用中,当你经常基于时间范围和其他索引字段查询的时候,这种问题常常会出现并且没有什么通用的解决方案. 但是如果你对你的数据有足够的洞察力并且能够在修改应用程序的话,这就会很有帮助了. 软件可以告诉mysql它需要用哪个索引. 例如,如果你的软件知道某个给定的IP地址最近只是偶尔登录, 那就能强制MySQL使用 time_stamp 区域作为索引:

SELECT ... USE_INDEX(time_stamp) ...
这虽然不是个理想的解决方案,但是如果使用适当的话,却是十分有效的.

6.4.1.2 临时表
另一个在上面没有显示出来的问题就是过多的使用基于磁盘的临时表. 在 EXPLAIN 的输出中, 你常常会看到 Using temporary. 这说明 MySQL 必须创建一个临时表来完成查询. 但是它却不会告诉你到底临时表是在内存中还是磁盘中. 临时表的大小由所需查询的表的大小以及MySQL配置文件中的 tmp_table_size 变量来确定.

如果创建临时表所需的空间小于或者等于 tmp_table_size, MySQL 会将其保存在内存里.这样在读写时就不会有磁盘那样的开销了. 但是如果临时表的空间达到了 tmp_table_size 的大小, 那MySQL 就会在它的 tmpdir 目录下建立基于磁盘的临时表文件 (一般在UNIX系统下是 /tmp 目录.) tmp_table_size 的默认大小是 32 MB.

为了获取mysql使用两种临时表的数量, 可以比较 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 内存溢出(out of memory). 或者, 确定 tmpdir 是指向一个非常高速的磁盘,这样就不需要等待太多的I/O操作.

作为最后的手段, 考虑使用tmpfs (或者ramdisk, mdmfs, 以及任何在你操作系统上基于内存的文件系统) 并且将 $TMPDIR 指向它,然后运行MySQL.

6.4.1.3 缓存
虽然的查询已经优化过并且使用了最有效率的索引,但是仍然有可能在某些时候遭遇到I/O瓶颈. 比如运行过多的查询,不论它们有多么高效,都会造成磁盘过载.如果是这样.那就是时候考虑缓存了.

最简单的就是确认你已经在使用MySQL的查询缓存.从MySQL 4.0开始, 查询缓存在内存中保存了那些经常被执行的 SELECTs 查询的结果,这样MySQL就不用去处理任何磁盘I/O.在 Chapter 5 的 Section 5.4.4 有更详细的描述.

更进一步, 你可以考虑应用层来做缓冲. 如果数据不是经常被修改, 那就可以在查询被执行一次之后保存至内存或者本地磁盘,以供下一次查询使用.

6.4.1.4 分担负载
如果针对以上情况你都按照所建议的做过修改,那看起来你需要更有效的分担I/O负载. 如同先前提到过的.安装拥有更快转速以及更少寻道时间的磁盘会有所帮助.使用RAID (特别是 RAID 0, RAID 5, 或者 RAID 10) 会将压力分摊到多个磁盘上,这样可能解决或者减轻瓶颈问题.

另一种方法是,如果你拥有多个磁盘并且无法简单的配置RAID的话,那就尝试手动分摊磁盘I/O负载. 花一些时间用 iostat 或者 systat (视你的操作系统而定) 来发现I/O大部分发生在哪个位置. 如果你将所有的mysql数据库文件存放在一个单独的磁盘,你可以尝试着将一些存放到其他词胖上.如果大多数的操作集中在一小组表中,考虑将它们移到不同的磁盘中去.

还有一种方法是将主要的随机I/O和主要的顺序存取分开. 存储的日志,例如二进制日志(binary logs),复制分发日志(replication relay logs)以及InnoDB事务日志(InnoDB transaction logs), 分派到和主数据文件不同位置的磁盘上. 这最后就是一个不停测试和修正的过程. 通过使用性能测试(benchmark), 注意数值的变化,还有尽量一次不要变更太多设置.

复制模式总是最后的选择. 如果你的需求超过了单台服务器的负载能力, 它总是最少影响整个架构的解决方案. 关于复制模式的内容,在 Chapter 7 中有讲述.

6.4.2 解决CPU瓶颈
MySQL中的CPU瓶颈很难跟踪. 不像一些数据库软件, MySQL目前没有提供关于每个查询实际工作时间以及等待磁盘操作时间的统计.

很幸运这不会是一个完全的猜谜游戏. 如果你看到一个查询在慢速查询日志 里面并且怀疑它引起CPU符合,简单的对它压力测试(benchmark)就可以了. 把一份MySQL的副本复制出来运行, 在一行上面运行几千次. 然后再另一个窗口上观察top, vmstat, 或者其他你喜欢的系统监控工具. 如果CPU很快达到了100%而这时候同时查询数相对较低,那么这个查询很可能就是引起CPU高负荷的元凶.

如果你发现慢速查询有很多的话, 选择哪一个开始分析呢? 简单: 查看那些调用大量行数的 (几千,几万,或者更多行), 并且注意那些使用MySQL内建数据处理函数的. 一般来说以下一些比较可疑:

格式化或者比较日期

加密数据或计算哈希值

处理复杂的比较,例如正则表达式

你常会发现一些每小时在几百万个值上计算MD5哈希值的查询常常占用太多的CPU时间. 通过将逻辑运算转移到查询数据库的应用服务器, 你能够更有效的释放MySQL的CPU时间.

如果你通过将逻辑运算转移到应用层也无法降低MySQL的运算量, 你就必须查看硬件问题了. 你有两种选择. 你可以简单的升级服务器的CPU或者增加更多的CPU. 或者,你也许会发现增加一台服务器更便宜些并且扩展性更好一些, 把数据复制给新服务器, 并且把流量分摊给它们.在这里利用一下摩尔定律,增加你硬件的性能.

MyISAM表的高CPU负载并不总是坏事. 它可能意味着你正在做的查询操作已经被完全缓存到系统缓冲中去了. 这也许是,或者也许不是一件坏事. 这确实比从磁盘读取好, 但是MySQL每次都必须向操作系统所要一块数据的处理时间,原本可以被更好的分配到其他的查询进程上. 转移到 InnoDB 或者 BDB 表会让 MySQL 自己去缓冲表数据, 这样就不用去向操作系统所要数据记录了.

6.4.3 解决内存瓶颈
在MySQL上调整内存使用量是件很精巧细致的活儿. 和前面提到的一样, MySQL有一些全局内存缓冲还有一些额外的单进程缓冲. 主要的难度在于平衡使用大的全局缓冲所获取的性能以及服务器能够服务的同时在线用户数. 最少而言,你必须拥有足够可用内存来处理MySQL的全局缓冲加上单进程缓冲乘以最大同时连接数的综合.

以下用算式来表示:

最小内存需求 = 全局缓冲大小 + (单进程缓冲大小 * 最大连接数)
单进程缓冲(thread_buffers) 包含以下内容:

排序缓冲(sort_buffer)
myisam表缓冲(myisam_sort_buffer)
读缓冲(read_buffer)
链接缓冲(join_buffer)
随机读缓冲(read_rnd_buffer)
全局缓冲(global_buffers) 包括:

键值缓冲(key_buffer)
innodb缓冲池(innodb_buffer_pool)
innodb日志缓冲(innodb_log_buffer)
innodb附加内存池(innodb_additional_mem_pool)
网络缓冲(net_buffer)
之所以说它是最小内存需求是因为理论上你还需要保留一些作为操作系统本身使用. 例如MyISAM表, "多余" 内存往往被用来缓冲MyISAM 数据文件(.MYD) 内的记录.

在查询进程中所有线程除了其所申请的内存外, 线程本身也需要开销一点内存. thread_stack 参数控制这个值. 在绝大多数的平台上,其默认值为192 KB.[5]

[5] 如果你在FreeBSD上使用linux线程库(LinuxThreads), 这个值将会被硬编码在linux进程库(LinuxThreads)中. 即使改变MySQL的 thread_stack 设置也不会生效. 你必须重新编译库赖改变这个堆栈大小.

这方面的问题很常见,并且很有代表性. 想象你有一台拥有1GB内存的服务器,上面混合了MyISAM 以及 InnoDB 表. 为了满足性能, 在使用 mytop (Appendix B) 观察了key的效率之后,你配置了512MB的 key_buffer , 通过SHOW INNODB STATUS ( Appendix A)检查了缓冲池内存统计之后,配置了256-MB的 innodb_buffer_pool .这样还留下了 256 MB内存可以被操作系统用来缓冲数据文件 , 这些内存被每个线程缓冲用来分配必须的空间. MySQL服务器处理小量的同时用户数, 也许绝大多数时间在20-50个, 每个线程的缓冲大小保留为默认设置.

所有这些都工作的很好,直到有一些新的应用也开始使用这台MySQL服务器. 这些新的应用程序需要大量的同时连接数. 服务器不再只处理大约20-50个同时连接,它现在处理大约300-400的同时连接. 现在, 在同一时刻这些连接所需要分配的每进程缓冲 (和 sort_buffer 一样) 都大大增加了.

这会导致一系列的恶性循环. 如果大量的线程需要分分配额外内存, 很可能因为服务器在处理一个大型的查询负载. 这会引起MySQL申请大量的内存以至于操作系统开始使用交换分区, 这使得性能进一步降低, 每条查询需要更长才能完成. 由于查询运行的更慢了,更多线程会申请更多的内存,最终导致了恶性循环的发生.

唯一的解决方案就是在系统内存和MySQL's的内存需求之间做出一个平衡.这意味着需要做出一下改动.

增加更多的内存

减少max_connections

减少每进程缓冲大小

前提是. 监控服务器上的内存使用状况. 确保在满足最坏情况下的内存需求 (达到了 max_connections 上线并且每个进程都分配了额外内存)的条件下, 仍能有一点空闲内存.

6.4.4 解决内核瓶颈
虽然这并不常见, 你也许发现MySQL并没有使用大量的CPU时间,然而服务器却十分繁忙. 服务器只有只有相当少的CPU空闲. 更进一步观察之后, 你发现更多的CPU时间被开销在"system"进程而不是"user" 或者 "idle". 这种情况可能预示着MySQL正在做一些不常见的操作使得内核进行创建或者回收线程.

这种情况曾经在Yahoo!上发生过,当Yahoo!开放了一个新的web站点之后, 在2002年9月, 工程师开始创建九月11日的纪念性网站:remember.yahoo.com[6]. 在网站上,任何人可以通过选择一个图片以及增加一段个人留言来创建纪念物瓦片("tile") . 任何观看此网站的人都可以看到瓦片. 为了尽快的完成工作, 网站由一些标准的开源工具构成,包括FreeBSD, Apache, PHP, 以及 MySQL

[6] 整个网站由一群Yahoo的工程师通过空闲时间,在两周内构想,设计并且创建发布出来.

整个结构并不简单, 但是我们针对了主要目的简化了它. 一组前端web服务器通过一个硬件负载均衡器连接到一个从服务器. 通过使用从服务器的连接,web服务器获取用来显示瓦片的一些必要数据. 当一个浏览者创建瓦片时, web服务器必须连接到主服务器来插入一些必要记录. 主服务器是一台高端的服务器: 两颗1.2-GHz CPU, 2 GB 内存, 还有一个RAID5的SCSI阵列.

最大情况下, 大约有25-30台web服务器需要连接master服务器. 每个服务器大约运行30-40 Apache 进程. 这意味着 master 需要支持超过 1,000 个同时连接数. 预想到这会消耗掉主服务器的所有资源后, 设计者选择了一个简单的处理方法. 很不幸, web应用(由PHP写成) 配置了持久连接. 所以,为了降低主服务器的连接数, wait_timeout 被设置成低于10秒.

总的来说, 这个方案可以工作. 空闲连接在10秒钟之后被丢弃. 在主服务器上的连接数保持在200以下, 保留了很多空闲资源. 但是这里有个问题: 主服务器上的CPU非常的繁忙. 大多数情况下空闲都小于10%, 将近50%的CPU时间被花费在系统任务(而非用户任务)上.

在大约一个小时的冥思苦想以及观察了系统日志和 SHOW STATUS 的输出之后, Jeremy终于突发灵感. Threads_created 的值太大,并且以令人恐怖的比率增加. 内核忙于创建和回收线程导致它占用了MySQL用来有效服务的那部分资源.

在认识到这点之后, 解决方案就很简单了.将原来 thread_cache 默认的0增加到大约150,这个修改立竿见影.系统占用的CPU时间降低了大约10%, 这样就释放了一定的CPU供给MySQL使用. 这样的结果是, MySQL并不完全需要这部分资源, 所以服务器最终有了20%的空闲CPU时间以供调配

Last modification:November 26th, 2018 at 04:16 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment