什么影响了MySQL性能

影响性能的一些常见因素

1. 服务器硬件
2. 服务器系统
3. 数据库存储引擎
   • MyISAM：可以很好的利用内存，但不支持事务，表级锁
   • InnoDB：事务级存储引擎，完美支持行级锁以及事务ACID特性
4. 数据库参数配置
5. 数据库表结构设计和SQL语句执行效率
6. 数据库的版本

---

CPU资源和可用内存大小

在服务器硬件中，最容易影响数据库性能的是CPU资源和可用内存大小以及I/O。

选择CPU时需要考虑的点：

• 我们的应用是否是CPU密集型？如果是CPU密集型，则需要计算能力强的CPU，即频率高的
• 我们的应用并发量如何？如果并发量大的话，就需要选择更多核心的CPU来提高吞吐量。若选择多核心的CPU的话，最好使用MySQL5.6以上的版本，因为低版本对多核心的CPU支持得不是很好
• 注：MySQL目前不支持多CPU对同一SQL进行并发处理。
• 注：不要在64位的CPU上使用32位的操作系统

内存：

• 容量越多越好，但对性能影响有限，因为并不能无限的增加性能
• 主频则选择服务器主板可支持的最高频率最好

---

传统的机械磁盘

使用传统机器磁盘。机械硬盘读取数据的过程：

1. 移动磁头到磁盘表面上的正确位置
2. 等待磁盘旋转，使得所需的数据在磁头之下
3. 等待磁盘旋转过去，所有所需的数据都被磁头读出

注：第一步+第二部=磁盘的访问时间。第三步消耗的时间=磁盘的传输速度

所以选择机械硬盘主要参考以下几个点：

1. 存储容量
2. 传输速度
3. 访问时间
4. 主轴转速
5. 物理尺寸

---

使用RAID增强传统机器磁盘的性能

RAID：

• RAID是磁盘冗余队列的简称（Redundant Arrays of Independent Disks）
• 简单来说RAID的作用就是可以把多个容量较小的磁盘，组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术

常用的RAID模式 - RAID 0：

RAID 0是最早出现的RAID模式,也称之为数据条带。是组建磁盘阵列中最简单的一种形式,只需要2块以上的硬盘即可,成本低,可以提高整个磁盘的性能和吞吐量。 RAID 0没有提供冗余或错误修复能力,但是实现成本是最低的

RAID 0 模式结构图：

常用的RAID模式 - RAID 1：

RAID 1又称磁盘镜像,原理是把一个磁盘的数据镜像到另一个磁盘上,也就是说数据在写入一块磁盘的同时会在另一块闲置的磁盘上生成镜像文件,在不影响性能情况下最大限度的保证系统的可靠性和可修复性。

RAID 1 模式结构图：

常用的RAID模式 - RAID 5：

RAID 5又称之为分布式奇偶校验磁盘阵列通过分布式奇偶校验块把数据分散到多个磁盘.上这样如果任何一个盘数据失效,都可以从奇偶校验块中重建。但是如果两块磁盘失效,则整个卷的数据都无法恢复。

RAID 5 模式结构图：

常用的RAID模式 - RAID 10，适合数据库的模式：

RAID 10又称分片的镜像它是对磁盘先做RAID 1之后对两组RAID 1的磁盘再做RAID 0 ,所以对读写都有良好的性能，相对于RAID 5重建起来更简单,速度也更快。

RAID 10 模式结构图：

RAID级别的选择，可以参考下表：

---

使用固态存储SSD和PCIe卡

相比机械磁盘固态磁盘有更好的随机读写性能，相比机械磁盘固态磁盘有更好的并发支持，相比机械磁盘固态磁盘更容易损坏

固态磁盘的特点：

• 使用SATA接口，可以替换传统磁盘而不需要任何改变
• SATA接口的SSD同样支持RAID技术

PCIe卡的特点：

• 无法使用SATA接口，需要独特的驱动和配置
• 价格相当于SSD贵，性能比SSD高

固态磁盘的使用场景：

• 使用于存在大量随机I/O的场景
• 解决单线程负载的I/O瓶颈

---

使用网络存储NAS和SAN

SAN（Storage Area Network）和NAS（Network-Attached Storage）是两种外部文件存储设备加载到服务器上的方法

SAN设备通过光纤链接到服务器，设备通过块接口访问，服务器可以将其当做硬盘使用：

SAN的优缺点：

• 适合大量顺序读写、读写I/O、 缓存、 I/O合并
• 随机读写慢，不如本地RAID磁盘

NAS设备使用网络链接，通过基于文件的协议如NFS或SMB来访问。

网络存储适合的场景：

• 数据库备份

网络对性能的影响：

• 延迟、吞吐量/带宽、网络质量（丢包）
• 建议：
  • 采用高性能和高带宽的网络接口设备和交换机
  • 对多个网卡进行绑定，增强可用性和带宽
  • 尽可能的进行网络隔离，不要把数据库暴露到外网上

---

总结：服务器硬件对性能的影响

CPU：

• 64为的CPU一定要工作在64位的系统下
• 对于并发比较高的场景CPU的数量比频率终于
• 对于CPU密集型场景和复杂SQL则频率越高越好

内存：

• 选择主板所能支持的最高频率的内存
• 内存的大小对性能很重要，所以尽可能的大

I/O子系统：

• PCIe -> SSD -> Raid10 -> 磁盘 -> SAN

---

操作系统对性能的影响-MySQL适合的操作系统

MySQL在不同操作系统需要注意的事项：

• Windows下对数据库的库名表名没有大小写敏感，但是Unix/Linux上却是大小写敏感的，所以尽量统一小写
• 使用FreeBSD的话，需要使用最新版本，因为老版本对MySQL的支持不是很好
• Solaris系统上支持MySQL，它以稳定性著称

---

CentOS系统参数优化

内核相关参数（/etc/sysctl.conf）：

• 设置最大监听队列的长度：net.core.somaxconn=65535
• 每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目，即完成连接上限：net.core.netdev_max_backlog=65535
• 指定所能接受SYN同步包的最大客户端数量，即半连接上限：net.ipv4.tcp_max_syn_backlog=65535
• 如果套接字由本端要求关闭，这个参数决定了它保持在FIN-WAIT-2状态的时间：net.ipv4.tcp_fin_timeout=10
• 开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭：net.ipv4.tcp_tw_reuse=1
• 开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭：net.ipv4.tcp_tw_recycle=1
• 默认的TCP数据发送窗口大小(字节)：net.core.wmem_default=87380
• 最大的TCP数据发送窗口大小(字节)：net.core.wmem_max=16777216
• 表示接收套接字缓冲区大小的默认值(字节)：net.core.rmem_default=87380
• 表示接收套接字缓冲区大小的最大值(字节)：net.core.rmem_max=16777216
• TCP发送keepalive探测消息的间隔时间（秒），用于确认TCP连接是否有效：net.ipv4.tcp_keepalive_time=120
• 探测消息未获得响应时，重发该消息的间隔时间（秒）：net.ipv4.tcp_keepalive_intvl=30
• 在认定TCP连接失效之前，最多发送多少个keepalive探测消息：net.ipv4.tcp_keepalive_probes=3
• Linux内核参数中最重要的参数之一，用于定义单个共享内存段的最大值：kernel.shmmax=429496295
  • 这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个的Innodb缓冲池的大小
  • 这个值的大小对于64位Linux系统，可取的最大值为物理内存的-1byte。建议值为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可，也可以取物理内存-1byte
• 这个参数当内存不足时会对性能产生比较明显的影响，这个参数就是告诉Linux内核除非虚拟内存完全满了，否则不要使用交换分区：vm.swappiness=0

参考：

• https://www.cnblogs.com/DengGao/p/tcp_parameter.html

Linux系统内存交换分区：

• 在Linux系统安装时都会有一个特殊的磁盘分区，称之为系统交换分区，即swap分区
• 当操作系统因为没有足够的内存时就会将一些虚拟内存写到磁盘的交换分区中，这样就会发生内存交换

在MySQL服务器上是否要使用交换分区有一些争议：

• 有些人认为在MySQL服务所在的Linux系统上应该完全禁用交换分区
• 有些人则认为禁用交换分区会带来以下风险：
  • 降低操作系统性能
  • 容易造成内存溢出、奔溃，或进程都被操作系统kill掉
• 结论：所以在MySQL服务器上保留交换分区还是很有必要的，但是要控制何时使用交换分区，这时就需要使用到我们上文中所提到的vm.swappiness=0参数了

---

增加资源限制（/etc/security/limit.conf），这个文件实际上是Linux PAM也就是插入式认证模块的配置文件。通过我们会在文件末尾加入以下参数来控制打开文件数的限制：

• * soft nofile 65535
• * hard nofile 65535

说明：

• * 表示对所有用户有效
• soft 指的是当前系统生效的设置，对于同一资源，soft的值不能比hard高
• hard 表明系统中所能设定的最大值
• nofile 表示所限制的资源是打开文件的最大数目
• 65535 就是限制的次数

由于系统默认的可打开文件句柄的数量是比较小的，所以一般我们都会将MySQL服务所在的操作系统的可打开的文件数量增加到65535个以保证可以打开足够多的文件句柄。需要注意的是，这个文件的修改需要重启系统才生效。

---

磁盘调度策略（/sys/block/vda/queue/scheduler）：

• 在Linux上磁盘队列调度的算法决定了块设备的请求发送到底层设备的顺序，默认使用cfq这种完全公平队列
• 这种cfq一般用于桌面级的系统，但是用在跑MySQL服务的系统就不太合适了，因为在MySQL的工作负载下，cfq会在队列中插入一些不必要的请求导致响应时间比较差

除了默认的cfq策略外还可以选择以下几种策略：

1. noop（电梯式调度策略）：

   NOOP实现了一个FIFO队列，它像电梯的工作方式一样对I/O请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一个介质。NOOP倾向于饿死读而利于写，因此NOOP对于闪存设备，RAM以及嵌入式是最好的选择。

2. deadline（介质时间调度策略）：

   Deadline确保了在一个截至时间内服务请求，这个截至时间是可调整的，而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象。Deadline对数据库类应用是最好的选择。

3. anticipatory（预料I/O调度策略）：

   本质上与Deadline一样，但在最后一次读操作后，要等待6ms，才能继续进行对其他I/O请求进行调度。它会在每个6ms中插入新的I/O操作，而会将一些小写入流合并成一个大写入流，用写入延时换取最大的写入吞吐量。AS适合于写入较多的环境，比如文件服务器，AS对数据库环境表现很差。

修改磁盘调度策略的命令，例如我将策略改为deadline：

echo deadline > /sys/block/vda/queue/scheduler

---

文件系统对性能的影响

服务器所使用的文件系统对服务器的I/O性能是有一定影响的，而文件系统的选择十分依赖于操作系统，例如Windows下就只有FAT和NTFS可供选择：

Linux下则有EXT3、EXT4、XFS，这三种文件系统都是具有日志功能的，这一点对于数据的安全性十分重要。其中XFS性能要比EXT3和EXT4高：

如果使用EXT3和EXT4的话，有几个挂载参数可以了解一下。EXT3/4文件系统的挂载参数可以在/etc/fstab 文件中配置：

• 首先是data参数，有三个可选的值，这三个值代表了不同的日志策略：data=writeback | ordered | journal ，其中 writeback 是Innodb最好的选择
  • writeback 意味着只有原数据写入日志，原数据写入和数据写入并不是同步的，这是最快的一种配置，因为Innodb有自己的事务日志，所以选择Innodb是最好的选择
  • ordered选项只会记录原数据，但提供了一些一致性的保证，在写原数据之前的会先写数据，使他们保持一致，这个选项比writeback 慢一些，但是如果出现崩溃呢更加安全
  • journal提供了原子日志的一种行为，在数据写入到最终位置之前，将记录到日志中，这个选项对Innodb来说是没有必要的，在这三个选项中journal是最慢的一个

然后我们再来看看另外两个重要的参数，在介绍这两个参数之前，我们需要了解默认情况下Linux操作系统会把文件访问的时间atime做一个记录，文件系统在文件被访问、创建、修改等的时候记录下了文件的一些时间戳。比如：文件创建时间、最近一次修改时间和最近一次访问时间；这在绝大部分的场合都是没有必要的。因为系统运行的时候要访问大量文件，如果能减少一些动作（比如减少时间戳的记录次数等）将会显著提高磁盘 IO 的效率、提升文件系统的性能。如果遇到机器IO负载高或是CPU WAIT高的情况时，可以尝试使用noatime和nodiratime禁止记录最近一次访问时间戳。

所以 noatime 和 nodiratime，是用于禁止记录文件的访问时间和读取目录的时间的，禁用了这两个时间的选项后，可以减少一些，写的操作。系统在读取文件和目录时候，不必写操作来记录以上两个时间。