影响mysql重启时间的因素_影响Mysql数据库性能的因素

1.写在前面的话

上半年过去了，恶补了SpringBoot、SpringCloud、SpringSecurity、Nginx、ElasticSearch方面的知识。下半年要恶补的第一块知识是数据库了，虽然平时工作中经常写SQL，但是每次和别人探讨数据库性能、备份、安全、分库分表的知识的时候，还没有体系化的模式。我觉得人不是什么东西都要知道，但是不要什么都不知道，尤其是自己专业领域的知识。

在对Mysql的学习中，先不讲体系知识，而是先思考一下究竟是什么影响了数据库的性能。只有了解了原因才能知道怎么让数据库的性能最大化。所以这也是为什么这篇文章成为我学习Mysql的第一篇文章。

数据库的重要性不言而喻，这里也不多说了，我们的Web服务器可以很容易横向扩展，数据库因为要保证一致性和稳定性，不能像Web服务器那样轻松的横向扩展。

2.影响数据库性能的因素：

sql查询速度

sql查询速度慢，影响QPS(每秒钟处理的查询量)和TPS

大量的并发

数据库连接数被占满

超高的CPU使用率

因CPU资源耗尽而出现宕机

服务器硬件

磁盘IO

磁盘IO性能突然下降，我们可以选择SSD。

最好不要在主库上数据库备份，大型活动前取消这类计划

网卡流量

网卡IO容易被占满。我们需要减少从服务器的数量，增加分级缓存，避免使用select * 以及分离业务网络和服务器网络。

数据库参数配置

数据库参数的配置比服务器硬件、服务器系统、存储引擎的选择影响大很多

3.大表带来的问题

3.1大表的定义

记录数巨大，单表超过千万行

表数据文件巨大，表数据文件超过10G

3.2大表对查询的影响

往往带来慢查询：很难在一定时间内过滤出所需要的数据。比如我们要查询一个大表里面的数据，查询条件诸如像性别这样的查询区分度低，会导致大量的磁盘IO，降低磁盘效率。如下图所示：

image.png

大表建立索引会需要很长时间，mysql<5.5的时候建立索引会锁表，>=5.5不缩表，但是会引起主从延迟。

修改表需要长时间锁表，造成长时间的主从延迟。同时影响正常的数据库操作。

3.3如何处理数据库中的大表

分库分表

难点是分表主键的选择以及分表后跨分区数据的查询和统计

大表的历史数据归档减少对前后端业务的影响

难点是归档时间点的选择，以及如何进行归档操作。当数据量很大的时候，从几亿条数据删除几十万条数据需要讲究方法，否则的话，会造成主从延迟。

4.服务器硬件对性能的影响

4.1.如何选择CPU

4.1.1我们的应用是不是CPU密集型

如果是，那么我们需要更好的CPU而不是更多的CPU。

4.1.2系统并发量如何

高并发的情况下，需要更多的CPU。Web类应用往往核心数量比频率更重要

4.2.内存

内存的大小影响数据库性能

4.3.磁盘的配置和选择

4.3.1目前使用到的磁盘

4.3.1.1传统机器硬盘

存储空间大、价格低、使用最多、最常见、读写较慢

读取数据的过程分为3个步骤

1.移动磁头到磁盘表面上的正确位置

2.等待磁盘旋转，使得所需的数据在磁头之下

3.等待磁盘旋转过去，所有所需的数据都被磁头读出

如何选择传统机器硬盘？根据数据大小，选择存储容量，不过目前存储容量不是多大问题，传输速度很重要，也就是读取步骤中第三步的速度；访问时间也是重要因素，对应第二步；物理尺寸越小，会越快，但是容量也会受限。

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

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

RAID0

RAID1

RAID5

RAID10

4.3.1.3使用固态存储SSD和PCIe卡

相比于机械磁盘有以下特点：

1.更好的随机读写性能

2.更好的支持并发

3.但是更容易损坏

目前的固态硬盘已经支持SATA接口了，但是呢其速度会受到SATA接口的影响。如下图所示：

同时SATA接口的SSD支持RAID技术~

PCIe卡，也就是FushionIO有以下特点：

1.无法使用SATA接口，需要独特的驱动和配置

2.价格相对于SSD要贵，但是性能比SSD更好

那么固态存储适用于什么场景呢？

1.适用于存在大量随机I/O的场景，所谓随机IO是因为数据库中的热数据大于了内存才发生的。

2.适用于解决单线程负载的IO瓶颈。假设我们只有一块SSD，那么我们应该把这个SSD放在从服务器上还是主服务器上呢？答案是从服务器上，因为从服务的数据复制是单线程的，而主库上的复制是多线程的。如果在主库上放SSD还会损害SSD，这点尤为注意。

4.3.1.4使用网络存储NAS和SAN

什么是SAN和NAS？有人说NAS倒过来就是SAN，嗯，说的有道理不扯了SAN(Storage Area Network)和NAS(Network-Attached Storage)是两种外部文件存储设备加载到服务器上的方法

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

SAN

SAN适合大量顺序读写，但是随机读写慢，不如本地RAID磁盘

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

SAN和NAS由于随机读写慢，所以适用于数据库备份，而不是直接用来做数据库的访问。

网络性能其实对数据库影响蛮大的。因为数据库服务器和Web服务器之间会进行网络传输，网络的延迟和带宽对都是影响因素。有人说数据库服务器和Web服务器在内网，应该问题不大。但是如下图所示，假设1000M带宽，Web服务器50个，每个读取2M数据，带宽就没了~~

所以，建议采用高性能和高带宽的网络接口设备和交换机，对多个网卡进行绑定，增强可用性和带宽，尽可能的进行网络隔离来减少网络对性能的影响。

4.3.1.5硬件性能总结

对于CPU的选择

64位的CPU一定要工作在64位的系统下

对于并发比较高的场景CPU的数量比平路重要

对于CPU密集性场景和复杂SQL则频率越高越好

对于内存的选择

选择主板所能使用的最高平路的内存

内存的大小对性能很重要，所以尽可能的大，它可以把随机读写变成顺序读写

IO子系统

性能上选择：PCIe->SSD->Raid10->磁盘->SAN

5.操作系统对性能的影响

5.1内核相关参数(/etc/sysctl.conf)

net.core.somaxconn=65535

net.core.netdev_max_backlog=65535

net.ipv4.tcp_max_synn_backlog=65535

对于一个TCP连接，Server与Client需要通过三次握手来建立网络连接.当三次握手成功后,我们可以看到端口的状态由LISTEN转变为ESTABLISHED,接着这条链路上就可以开始传送数据了。每一个处于监听(Listen)状态的端口,都有自己的监听队列。

net.core.somaxconn表示socket监听(listen)的backlog上限。什么是backlog呢？backlog就是socket的监听队列，当一个请求(request)尚未被处理或建立时，他会进入backlog。

net.core.netdev_max_backlog表示当每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许发送到队列的数据包的最大数目，一般默认值为128

backlog其实是一个连接队列，在Linux内核2.2之前，backlog大小包括半连接状态和全连接状态两种队列大小。

半连接状态为：服务器处于Listen状态时收到客户端SYN报文时放入半连接队列中，即SYN queue(服务器端口状态为：SYN_RCVD)。

全连接状态为：TCP的连接状态从服务器(SYN+ACK)响应客户端后，到客户端的ACK报文到达服务器之前，则一直保留在半连接状态中；当服务器接收到客户端的ACK报文后，该条目将从半连接队列搬到全连接队列尾部，即 accept queue (服务器端口状态为：ESTABLISHED)。

在Linux内核2.2之后，分离为两个backlog来分别限制半连接(SYN_RCVD状态)队列大小和全连接(ESTABLISHED状态)队列大小。

SYN queue 队列长度由 /proc/sys/net/ipv4/tcp_max_syn_backlog 指定，默认为2048。

Accept queue 队列长度由 /proc/sys/net/core/somaxconn

net.ipv4.tcp_fin_timeout=10 表示如果套接字由本端要求关闭，这个参数决定了它保持在FIN-WAIT-2状态的时间

net.ipv4.tcp_tw_reuse=1 表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭；

net.ipv4.tcp_tw_recycle=1 表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。

四次挥手

net.core.wmem_default=87380

net.core.wmem_max=16777216

net.core.rmem_default=87380

net.core.rmem_max=16777216

tcp连接接收和发送的缓冲区的默认值和最大值

net.ipv4.tcp_keepalive_time=120 开启keepalive的闲置时长。当keepalive起用的时候，TCP发送keepalive消息的频度

net.ipv4.tcp_keepalive_intvl=30 keepalive探测包的发送间隔

net.ipv4.tcp_keepalive_probes=3 如果对方不予应答，探测包的发送次数

kernel.shmmax=4294967295 Linux内核参数中最重要的参数之一，用于定义单个共享内存段的最大值，这个参数应该设置的足够大，以便能在一个共享内存段容纳下整个的Innodb缓冲池的大小。这个值的大小对于64位的Linux系统，可取的最大值为物理内存值-1byte，建议值为大小物理内存的一般，一般取值大于Innodb缓冲池的大小即可，可以去物理内存-1byte。

vm.swappiness=0 这个参数当内存不足时会对性能产生比较明显的影响

Linux系统内存交换区是一个特殊的磁盘分区，如果我们使用free -m可以在系统中看到swap交换分区。如下图所示：

vm.swappiness=0 表示当操作系统因为没有足够的内存时就会将一些虚拟内存写到磁盘的交换区中，这样就会发生内存交换。

有人认为在Mysql服务器上应该完全禁用交换分区。这样做容易降低操作系统的性能，另外还有可能造成内存溢出，崩溃，或者被操作系统kill掉。所以在Mysql服务器上保留交换区还是很必要的，但是要控制何时使用交换分区。0这个值表示告诉Linux内核除非虚拟内存完全满了，否则不要使用交换区。

5.2增加资源限制(/etc/security/limit.conf)

这个文件实际上是Linux PAM也就是插入式认证模块的配置文件。可以用打开文件数的限制，如下图所示：

注意同一资源soft不能比hard高。把可打开的文件数量增加到了65535个以保证可以打开足够多的文件句柄。

5.3磁盘调度策略(/sys/block/devname/queue/scheduler)

cat /sys/block/sda/queue/scheduler 可以看到磁盘的调度策略

5.3.1 电梯时调度策略

NOOP实现了一个FIFO队列，当一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一介质。NOOP倾向饿死读而利于写，因此NOOP对于闪存设备、RAM及嵌入式系统是最好的选择。

5.3.2截止时间调度策略

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

5.3.3预料IO调度策略

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

image.png

6.文件系统对性能的影响

Linux中建议使用XFS~