兰若寺

此章主要研究服务器及其配置对 MySQL 性能的影响。

6.1 Performance-Limiting Factors
问题的关键无疑是找出影响 MySQL 性能的瓶颈来。

6.1.1 Disks
毫无疑问，磁盘是系统中最慢的一部分，因此磁盘的 I/O 就是一个瓶颈。如果可能的话，最好买转速快的硬盘。

6.1.2 Memory
你要知道，一个劲儿地添加内存不一定能提高 MySQL 性能，除非操作系统能很好地利用它，缓存更多地数据。如果你的数据库大小为512 MB，你已经有1 G 内存，此时再添加内存便毫无意义。

6.1.2.1 MySQL’s buffers and caches
分配给 MySQL 的内存大多用来做内部各种的 buffer 和 cache。这些 buffer 被分为全局 buffer 和 单连接 buffer。两个最重要的 buffer 是 MyISAM key buffer (keybuffersize) 和 InnoDB’s buffer pool (innodbbufferpool_size)。MyISAM key buffer 是 MySQL 用来缓存经常用到的索引数据。可以想象得到，越少访问磁盘上索引数据，性能就越好。因此可能的话，调整 key buffer 到足够大可以完整缓存经常查询的索引数据。

6.1.3 Network
起初想来，网络应该不是一个瓶颈，其实却不尽然。高延迟和低带宽是可以影响服务器性能的。举个例子，如果用户执行数据量很大的 SELECT 操作，当然此操作因此得到了相关表的读锁定，这样直到这个操作完成，MySQL 都不会释放锁定给其他的写操作。如果网络很慢的话，这个操作要花相当长的时间，但此时 cpu 和 磁盘的却在空闲。

6.2 RAID

6.2.1 Mix and Match
在磁盘的使用上，可以考虑的 RAID 阵列的混合。RIAD 5 和 RAID 10用于存放数据库，RAID 1 用来存放事务和 replication 的日志。

6.2.1.1 Sample configuration
假设目录 / 和 /usr 在 RAID 1 上，而 /data 在 RAID 10 上。 MySQL 安装在 /usr/local/mysql ，默认数据目录为 /usr/local/mysql/data 。我们目标是将 InnoDB 和 replication 日志调整到 RAID-1 卷上，其它都放在 /data 下。可以这样修改 my.cnf 。
datadir = /data/myisam

log-bin = /usr/local/mysql/data/repl/bin-log

innodbdatafile_path = ibdata1:16386M;ibdata2:16385M

innodbdatahome_dir = /data/ibdata

innodbloggrouphomedir = /usr/local/mysql/data/iblog

innodblogarch_dir = /usr/local/mysql/data/iblog

6.2.2 Hardware Versus Software
在中度负载下，软硬 RAID 性能差别不大，当然软 RAID 在 cpu 上要多一些开销。此外，硬 RAID 一般都有写缓存，而软 RAID 没有。

6.2.3 IDE or SCSI?
SCSI 有一项技术被称为 TCQ ，其最早应用于SCSI-2代标准的硬盘上，其可允许硬盘执行从PC同时发来的多重命令。当命令在硬盘的缓存里时，通过硬盘的微处理器，他们会被标记然后重新排序。这样是为了减少硬盘寻找数据头时的时间。

6.2.4 RAID on Slaves
在使用 replication 产生一个 slave 阵列时，建议 slave 服务器使用 RAID 0 ，因为你不需要考虑冗余只需提高性能即可。

6.3 Operating System

6.3.1 Filesystems
在选择文件系统时，应该根据需求，仔细考虑每个文件系统的优势和缺点，从而选出一个合适的。应当指出，文件系统是一个影响 MySQL 性能相对小的因素。

6.3.1.1 Journaling
日志文件系统在概念上类似一个写操作前的事务日志。当文件系统更新时，就会将一个描述事务的纪录添加在日志里；同时，另外一个线程就会去执行事务操作，也就是对文件系统进行写操作，在完成后再对事务纪录做个标记。

6.3.1.2 Other features and tweaks
现代文件系统都具有大目录支持和碎片、剩余空间管理功能。

6.3.1.3 Choosing a filesystem
此节介绍了一些文件系统。

6.3.1.4 FreeBSD
FreeBSD 上有 UFS 和 UFS2 文件系统。UFS2 除了可以处理尺寸多大1 TB 的文件，还支持访问控制列表（ACL）和扩展属性。如果你有大目录，可以在内核上启用 UFS_DIRHASH 。

6.3.1.5 Do you need a filesystem at all?
InnoDB 表可以直接用 raw 磁盘，不需要文件系统，而且性能提高2-5%。缺点是备份工具太少。

6.3.2 Swap
理想情况下，服务器不需要使用交换区。交换就意味着你的内存不够或者什么配置的有问题—也许 MySQL 的 key buffer 配置的太大了，或者你启用了太多无用的服务进程。不过有些操作系统即使用空余内存也会使用交换区的。

6.3.3 Threading
作为一个多线程的服务，MySQL 可以在那些支持线程的操作系统上发挥最大的效力。

6.4 Techniques
如果发现服务器很慢，怎么办？首先应该在操作系统层面找出瓶颈的类型来。使用 top、vmstat 和 iostat 等系统工具来找出系统中哪种资源紧缺。

6.4.1 Solving I/O Bottlenecks
磁盘瓶颈大多是因效率差的查询引起的，效率差意味着 MySQL 必须经过大量的检索才能找到你想要的数据。这都因为是查询没有使用索引或者使用了不合适的索引。在 slow query 日志里很容易查找出没有使用索引的查询。

6.4.1.1 Wrong index
查找使用错误索引查询的难度很大，这要对数据和查询有深刻的理解才行。

6.4.1.2 Temporary tables
有时 MySQL 必须产生临时表才能完成查询，如果临时表的大小小于 tmptablesize ，那么它就会被放在内存里，否则就会在磁盘上。
mysql> SHOW STATUS LIKE ‘Createdtmp%’;

如果发现 Createdtmpdisktables 的次数过多，那么就要考虑增加 tmptable_size 的大小。另外，可以考虑将 tmpdir 放在 I/O 较快的磁盘上。

6.4.1.3 Caching
即使你的查询是优化过的而且使用的也是最有效的索引，仍然有可能在某些点上遭遇 I/O 瓶颈的问题。比如太多的查询蜂拥而来，无论他们怎么有效，也不能让硬盘速度跟得上。这时候我们就要考虑缓存。启用查询缓存可以让执行过的 SELECT 操作留在内存里，这样就会降低磁盘的开销。

6.4.1.4 Spread the load
使用 RAID 来分担磁盘的负载当然是最好不过。不过如果你有多个磁盘，但不能使用 RAID 时，你可以花些时间来观察 iostat 和 systat 的输出从而找出活动最频繁的那个表，把它放在一个单独的磁盘上。

6.4.2 Solving CPU Bottlenecks
有关 MySQl 的 CPU 瓶颈难以追踪，因为和别的数据库服务不一样，MySQL 不能提供每个查询的状态，也就是说查询所花的 CPU 时间里你不知道哪些是用来真正干活抑或只是等待磁盘 I/O。但还是有办法的。如果你在慢速查询日志里发现一个查询，并且怀疑它导致 CPU 瓶颈，那么你可以测试它。将此查询放在 super-smack 里运行个几百次，然后观察 top 和 vmstat 的输出，如果发现 CPU 利用率接近100，但是并发查询的数目相对较小，那么你找对了。

一般来讲涉及到格式或者比较日期的，加密数据或者计算哈希值的，进行复杂比较的（例如正则表达式）都可能导致 CPU 占用率较高。

6.4.3 Solving Memory Bottlenecks
MySQL 最小内存占用公式（这里不包括用于缓存的内存）：
minmemoryneeded = globalbuffers + (threadbuffers * max_connections)

globalbuffers 包括：
keybuffer
innodbbufferpool
innodblogbuffer
innodbadditionalmempool
netbuffer

threadbuffers 包括：
sortbuffer
myisamsortbuffer
readbuffer
joinbuffer
readrndbuffer

需要注意的是，线程本身也需要内存的开销。thread_stack 此变量在大多数平台下默认为192 Kb。

实际上在 MySQL 服务器上调整内存使用是一个平衡的艺术，目标是在最大连接的情况下，系统仍然有呼吸的空间。

6.4.4 Solving Kernel Bottlenecks
此节讲述了一个案例，有时 MySQL 也会折磨内核，例如让它不停地产生和杀死线程。