1 . 硬件和系统调优概览
本文我们运用“独孤九剑”，从I/O、CPU、网络、进程等方面汇总介绍如何破解硬件和系统层面的性能瓶颈，料敌机先，攻其必救。
2.I/O ：MySQL 80%的性能瓶颈所在
不仅仅是MySQL，所有的数据库系统对I/O都特别敏感，80%的系统性能瓶颈基本都在I/O上。[1]
存储设备的性能如表1所示。
表1 存储性能对照表
| 硬件 | ns | us | ms | 性能比较 |
| L1 级缓存 | 0.5 ns | |||
| L2 级缓存 | 7 ns | 14倍 L1级缓存 | ||
| 内存访问 | 100 ns | 20倍 L2级缓存200倍 PCIe 4k写 | ||
| Flash/NVMe 访问 | 20,000 ns | 20 us | 200倍 内存访问 | |
| SSD 4k 随机写 | 65,000 ns | 65 us | 600倍 内存访问 | |
| 机械磁盘寻道 | 10,000,000 ns | 10,000 us | 10 ms | 100,000倍 内存访问 |
从CPU缓存到内存，从SSD到HDD，CPU访问数据的效率在逐步下降，这也是为什么数据库管理员需要着重优化和提升I/O性能，解决数据库瓶颈的原因。
[1]这里的I/O是指广义上的I/O系统，包括硬件上的内存和存储设备，以及系统上的I/O相关参数和文件系统。
1 ．存储设备
（1）强烈建议使用Flash设备，优先使用NVME和PCIe接口的Flash设备
如果事务并发修改较多的话，底层存储设备的读写压力会非常大，若存储设备的性能无法支撑这么大量的I/O请求，MySQL将被堵塞，无法提升数据库并发处理能力。
普通的SAS机械磁盘使用盘片，通过机械手臂、磁头等访问和存储数据，由于转速限制，IOPS一般在150次/秒左右，I/O访问延迟在毫秒（ms）级别。而Flash存储是电子设备，IOPS一般为4～10万次/秒，延迟一般在微秒（μs）级别。对于数据库这种对I/O敏感的系统，能够大幅提高并发处理能力。
随着Flash设备的逐渐普及，出现了不同接口类型的Flash设备，如SATA、PCIe、NVMe。
SATA接口的SSD在外观上与普通的机械磁盘一样，以Intel S3700为例，4KB随机写的IOPS能达到3.6万次（是普通磁盘的200倍），延迟降到65μs（是普通机械磁盘的1/10）。SSD的外观如图18-1所示。
PCIe Flash卡需要插在主板的专门接口上，不通过硬盘背板和RAID卡的转发，速率更高。以HGST的Ultrastar SN150 为例，IOPS为14万次是普通SSD的3倍，延迟在20μs以下，是普通SSD的1/3。但是它的更换需要打开机箱，相比SATA接口的SSD不用开机箱，可以随时拔插磁盘，在运维方面会更麻烦一点。Flash卡的外观如图2所示。
图1 SSD固态磁盘 图2 Flash卡
SATA接口的磁盘使用的是AHCI接口标准，无法充分发挥Flash介质的性能；PCIe Flash介质采用的都是各个厂商私有的接口标准，无法统一管理。2011年，Intel牵头发布了NVMe接口标准，以适配高速低延迟存储设备。支持NVMe接口标准的固态硬盘和PCIe Flash卡同样可以达到十万次以上的IOPS和20μs以下的I/O延迟。
（2）把binlog、redo日志文件和数据文件分开
为了保证数据库的数据不会丢失，一般会设置sync_binlog=1和innodb_flush_ log_at_trx_commit=1。由于数据库是WAL日志先行的，对日志型的I/O访问最主要的要求是顺序写延迟低；而普通的数据由于是异步I/O方式的，对底层存储最主要的要求是随机读写IOPS高、并发I/O处理能力强。在I/O请求非常高的情况下，如果日志型的请求和数据文件型的请求都落在同一个存储系统上，会出现相互冲突的情况。
由于RAID卡的缓存速率跟内存的速率是同一个级别的，普通HDD和普通SSD的顺序I/O也能达到200MB/s以上，对绝大多数binlog和redo日志的顺序I/O都是满足的。将binlog和redo日志等对顺序读请求和延迟要求较高的请求放在带有RAID卡缓存的普通磁盘上，既能获得内存级别的响应延迟，也能获得快速的顺序I/O能力，并且成本较低。
数据文件要求随机I/O并发处理能力强，只能通过PCIe Flash存储或者不带缓存（Write Through）的SSD提供十万级别以上的高性能随机I/O读写能力。不使用RAID的缓存主要是为了避免抢占日志型存储的资源，避免竞争，而且SSD本身的IOPS能力已经足够支撑大部分数据库的I/O并发，不需要在缓存中合并了。
（3）如果使用SSD的话，请关闭RAID卡的自动一致性读和充放电配置
关于RAID卡的一致性读和充放电配置这里不再赘述。
（4）考虑SSD批次问题
SSD由于其电子设备的特性，有可能在同一个批次上具有相同的缺陷，如果用于同一台物理机或者主备库中，有可能出现整台物理机或者主备库的数据失效、无法提供服务的问题。有条件的话，建议在同一台物理机或者主备库中采用不同批次的SSD。
2 ．内存
下面主要探讨在内存方面有哪些地方可以优化。
（1）内存空间越大越好
原则上，内存空间越大越好，如果业务需要访问的数据都可以放在内存中，那么访问的效率一定是最高的。
（2）建议关闭NUMA
如果追求极致性能的话，则可以考虑每个NUMA 节点一个MySQL实例，否则请关闭NUMA。
NUMA（Non-Uniform Memory Access，非一致存储访问结构）是最新的内存管理技术，它和SMP（Symmetric Multi-Processor，对称多处理器结构）是对应的。SMP和NUMA架构的对比如图18-3所示。
图3 NUMA架构
通过图18-3我们可以直观地看到，在SMP架构下，访问内存的代价都是一样的；但是在NUMA架构下，对本地内存的访问和对非本地内存的访问代价是不一样的。根据这个特性，在操作系统中可以设置进程的内存分配方式。目前支持的方式包括：
- --interleave=nodes，内存交互分配策略。内存会在nodes 上轮询（Round Robin）分配。
- --membind=nodes，绑定内存在nodes上。如果该nodes上没有内存可供使用，则内存分配将会失败。
- --cpunodebind=nodes，绑定在nodes的CPU上。
- --physcpubind=cpus，绑定在指定的cpus核上。
- --localalloc，只在当前node上分配。
- --preferred=node，优先在node上分配内存，如果node内存不够，则可以到其他node上分配。
简而言之，就是可以指定内存是在本地分配，还是在指定的几个CPU节点上分配或者轮询分配的。对于--interleave=nodes轮询分配方式，只要物理内存还有剩余，数据库申请内存是可以在任意NUMA节点上分配的；否则，即使其他NUMA节点上还有内存剩余，Linux也不会把剩余的内存分配给这个进程，从而导致数据库内存被交换出去。有经验的系统管理员或者DBA都知道内存swap导致的数据库性能下降有多么大。
所以最简单的方法还是关闭这个特性。当然，如果你对NUMA非常熟悉，一台服务器上有多个MySQL实例，并且CPU本地内存足够，希望就在本地分配内存，也可以指定NUMA节点分配。
这里介绍一下关闭NUMA的方法。如下三种方法简单、有效，能够避免NUMA导致的交换。
- 在BIOS中直接关闭NUMA。由于各种BIOS类型的区别，如何关闭NUMA千差万别，如图4所示是在DELL的BIOS中设置内存NUMA关闭的截图。
图4 BIOS设置NUMA
- 在操作系统内核启动时关闭NUMA。在操作系统中关闭，可以直接在/etc/grub.conf文件的kernel行的最后添加numa=off。
kernel /vmlinuz-2.6.32-220.el6.x86_64 ro root=/dev/mapper/VolGroup-root rd_NO_LUKS LANG=en_US.UTF-8 rd_LVM_LV=VolGroup/root rd_NO_MD quiet SYSFONT=latarcyrheb-sun16 rhgb crashkernel=auto rd_LVM_LV=VolGroup/ swap rhgb crashkernel=auto quiet KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM numa=off
并且在sysctl.conf文件中设置vm.zone_reclaim_mode=0尽量回收内存。
- 在进程启动时关闭NUMA。在启动MySQL时关闭NUMA特性。
numactl --interleave=all mysqld &
（3）设置较小的vm.swappiness值
vm.swappiness是操作系统控制物理内存交换的策略，它的值是一个百分比的值，最小为0，最大为100，默认值为60。将vm.swappiness设置为0表示尽量少交换，设置为100表示尽量将inactive内存交换出去。在MySQL所在的服务器中建议设置vm.swappiness=10。
当内存基本满时，系统会根据这个参数来判断是把内存中很少用到的inactive 内存交换出去（顾名思义inactive内存，就是指那些被应用程序申请（mapped），但是“长时间”不用的内存），还是释放老数据的缓存（缓存中存放的是从磁盘读出来的数据，根据程序的局部性原理，这些数据在接下来的程序访问中有可能会被用到）。
我们可以使用vmstat来查看inactive内存的大小：
$ vmstat -an 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free inact active si so bi bo in cs us sy id wa st
1 0 0 27522384 326928 1704644 0 0 0 153 11 10 0 0 100 0 0
0 0 0 27523300 326936 1704164 0 0 0 74 784 590 0 0 100 0 0
0 0 0 27523656 326936 1704692 0 0 8 8 439 1686 0 0 100 0 0
0 0 0 27524300 326916 1703412 0 0 4 52 198 262 0 0 100 0 0
通过/proc/meminfo可以看到更详细的信息：
$ cat /proc/meminfo | grep -i inact
Inactive: 326972 kB
Inactive(anon): 248 kB
Inactive(file): 326724 kB
MySQL DBA对inactive内存到底是什么可能不太了解，这里详细介绍一下。在Linux中内存可能处于三种状态：free、active和inactive。众所周知，Linux内核在内部维护了很多LRU列表用来管理内存，比如LRU_INACTIVE_ANON、LRU_ACTIVE_ANON、LRU_ INACTIVE_FILE、LRU_ACTIVE_FILE、LRU_UNEVICTABLE。其中LRU_INACTIVE_ANON、LRU_ACTIVE_ANON用来管理匿名页，LRU_INACTIVE_FILE、LRU_ACTIVE_FILE用来管理页缓存，刚访问过的页面放进active list，长时间未访问过的页面放进inactive list。
一般来说，MySQL特别是InnoDB管理内存缓存，占用的内存比较多，不经常访问的内存也会不少，这些内存如果被Linux错误地交换出去，将浪费很多CPU和I/O资源。
所以，在MySQL服务器上最好设置vm.swappiness=10。可以通过在sysctl.conf文件中添加如下一行，并使用sysctl -p来使该设置生效。
echo "vm.swappiness = 10" >>/etc/sysctl.conf
（4）考虑开启大页
Oracle DBA会告诉你，服务器上的大页一定要开启，这样每个内存页为2MB而不是4KB，保证记录进程所映射的内存的页表（假设为w）足够小，避免很多进程（假设为n）各自都映射了大容量的缓存空间，导致页表浪费大量的内存空间n×w（页表占用内存空间的大小）。大页确实能大幅降低页表的大小（降低为2MB/4KB）。大页的另一个作用是：避免内存交换。
MySQL是单进程多线程的，页表再大，也只是一个进程的页表占用内存多一点，并不会像Oracle的多进程一样，每个进程的页表都要浪费那么多的内存空间。
MySQL使用大页有一些好处，但是也给运维人员造成了一定的困扰。Linux并不能把所有空间都分配为大页，当总大页内存分配不足时，MySQL只会使用非大页内存空间启动。由于大页并不会自动释放，使得预分配的大页内存闲置，而MySQL内存只能在非大页的剩余空间中分配，导致发生交换等问题。
（5）在BIOS中设置为最大性能模式。
在BIOS中将Memory Speed 等设置为最大性能。
3 ．文件系统
（1）文件系统选择XFS或者Ext4
不像Oracle经常利用ASM运行在裸设备上，MySQL一般运行在文件系统上，那么对文件系统的选择就非常关键了。XFS和Ext4对大文件的读写更友好（使用drop table删除一个文件非常快），也经过了大量线上环境的验证。
（2）文件系统的mount参数加上noatime、nobarrier
如果使用noatime mount的话，文件系统在程序访问对应的文件或者文件夹时，不会更新对应的Access时间。一般来说，Linux的文件记录了三个时间：Change时间、Modify时间和Access时间。我们可以通过stat来查看文件的三个时间：
$ stat libnids-1.16.tar.gz
File: `libnids-1.16.tar.gz'
Size: 72309 Blocks: 152 IO Block: 4096 regular file
Device: 302h/770d Inode: 4113144 Links: 1
Access: (0644/-rw-r--r--) Uid: ( 0/ root) Gid: ( 0/ root)
Access: 2008-05-27 15:13:03.000000000 +0800
Modify: 2004-03-10 12:25:09.000000000 +0800
Change: 2008-05-27 14:18:18.000000000 +0800
其中Access时间指文件最后一次被读取的时间，Modify时间指文件的文本内容最后发生变化的时间，Change时间指文件的inode（比如位置、用户属性、组属性等）最后发生变化的时间。
一般来说，文件都是读多写少的，而我们也很少关心某个文件最近什么时间被访问了。所以建议采用noatime，这样文件系统就不会记录Access时间，避免浪费资源。
现在很多文件系统都会在提交数据时强制底层设备刷新缓存，称之为Write Barriers（写屏障）。但是数据库服务器底层存储设备要么采用RAID卡，要么采用Flash卡，它们都有自我保护机制，保证数据不会丢失，所以我们可以安全地使用nobarrier挂载文件系统。设置方法如下：
对于Ext3、Ext4和ReiserFS，可以在挂载时指定barrier=0；对于XFS，可以指定nobarrier选项。
（3）将调度策略设置为Deadline
在文件系统上还有一个提高I/O性能的优化万能钥匙，那就是Deadline。
在采用Flash技术之前，我们都是使用机械磁盘来存储数据的，机械磁盘的寻道时间是影响其速度的最重要因素，直接导致它的IOPS非常有限。为了尽量排序和合并多个请求，以达到一次寻道能够满足多次I/O请求的目的，Linux文件系统设计了多种I/O调度策略，以适用各种场景和存储设备。
Linux的I/O调度策略包括：Deadline、Anticipatory、CFQ（Completely Fair Queuing）和NOOP。这里主要介绍CFQ和Deadline。CFQ是Linux内核2.6.18之后的默认调度策略，它声称对每一个I/O请求都是公平的，这种调度策略对大部分应用都适用。但是如果数据库有两个请求，一个请求3次I/O，一个请求10000次I/O，由于绝对公平，3次I/O的这个请求需要跟10000次I/O的请求竞争，可能要等待上千次I/O完成才能返回，导致它的响应时间非常长。并且如果在处理的过程中，又有很多I/O请求陆续发送过来，部分I/O请求甚至可能一直无法得到调度被“饿死”。而Deadline要求I/O在指定的时间内被调度到，避免一个请求在队列中长时间得不到处理，导致“饿死”。这种调度策略对于数据库应用来说更加适用。
我们可以通过如下命令将SDA的调度策略设置为Deadline。
echo deadline >/sys/block/sda/queue/scheduler
3 . CPU
当底层存储节点I/O问题解决以后，CPU的瓶颈就凸显出来了。本节介绍底层CPU硬件和系统可以进行哪些调整优化。
1 ．选择频率较高、核数较多的CPU
MySQL早期版本对多核CPU的支持较弱，到5.6版本以后，MySQL能使用48核以上的CPU。但是由于MySQL不支持SQL语句并行执行，所以CPU的频率较高、核数较多会更有优势。在硬件选型时，请尽量选择频率较高、核数较多的CPU。
2 ．关闭节能模式
需要从BIOS到操作系统内核层面都关闭CPU的节能模式。
4 . 网络
1 ．避免域名反解析
MySQL默认在内存中会维护Host缓存，保存IP地址和主机名的映射关系。如果客户端连接使用的IP地址不在Host缓存中，MySQL需要反解析域名，导致客户端连接时间较长。建议设置skip_name_resolve=on避免域名解析，客户端授权用localhost或者IP地址表示，而不要使用DNS名称表示。
2 ．关闭iptables和SELinux
很多数据安全公司提供的检查项都建议在数据库服务器上开启iptables或者SELinux。但是对于高并发和大压力情况下的MySQL数据库来说，开启了iptables会造成队列满，开启了SELinux会导致MySQL访问文件权限出现问题，远远大于所谓的安全检查项带来的好处。采用SSH登录端口修改，在网络交换机上设置安全策略比在数据库上开启iptables和SELinux要好得多。
3 ．网卡多队列避免CPU的IRQ瓶颈
网卡发送和接收数据包需要通过IRQ中断请求CPU处理，但是在Linux系统上，部分网卡上的所有中断请求只通过一个CPU处理，从而导致该CPU成为性能瓶颈。使用mpstat查看信息如下：
04:18:08 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle
04:18:09 PM all 46.88 0.00 9.45 1.23 0.00 3.77 0.00 0.00 38.66
04:18:09 PM 0 7.14 0.00 2.04 0.00 0.00 90.82 0.00 0.00 0.00
04:18:09 PM 1 64.95 0.00 11.34 3.09 0.00 0.00 0.00 0.00 20.62
04:18:09 PM 2 60.82 0.00 13.40 2.06 0.00 0.00 0.00 0.00 23.71
在上述信息中，CPU 0的soft IRQ占了90.82%，idle为0%，成为性能瓶颈。
解决这个问题很简单，可以使用多队列的网卡（一般Intel的较新网卡都支持多队列），或者在确定是IRQ中断的问题时，手动将网卡软中断均分到多个CPU上。
4 ．在短连接下降低TIME_WAIT Socket连接
关于TIME_WAIT状态的Socket连接在前面的章节中已经介绍过了，这里不再赘述。
5 ．sysctl.conf考虑调整参数
net.core.rmem_default = 16777216
net.core.wmem_default = 16777216
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_syncookies = 0
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
5 其他
1 ．进程资源限制
nofile进程资源限制导致MySQL连接数受限的问题，其实在/etc/security/limits.conf文件中还有很多参数可以调整。下面是推荐的配置，建议读者根据自己的MySQL业务系统的压力进行适当调整。
soft nproc 16384
hard nproc 16384
soft nofile 65536
hard nofile 65536
hard memlock unlimited
soft memlock unlimited
soft stack 32768
hard stack 32768
2 ．使用64位Linux系统
不知道还有没有DBA在32位的Linux系统上使用MySQL，如果还有的话，请自己找一个角落面壁思过吧。
