MySQL原理简介—6.简单的生产优化案例

大纲

1.MySQL日志的顺序写和数据文件的随机读指标

2.Linux存储系统软件层原理及IO调度优化原理

3.数据库服务器使用的RAID存储架构介绍

4.数据库Too many connections故障定位

1.MySQL日志的顺序写和数据文件的随机读指标

(1)磁盘随机读操作

(2)磁盘顺序写操作

(1)磁盘随机读操作

MySQL在执行增删改查时，会从表空间的磁盘文件里，读取数据页出来。这个过程是典型的磁盘随机读操作。

下图有一个磁盘文件，里面有很多数据页。可能要在一个随机位置读取一个数据页到缓存，这就是磁盘随机读。因为要读取的这个数据页可能在磁盘的任意一个位置，所以读取磁盘里的数据页时只能使用随机读的这种方式。

磁盘随机读的性能是比较差的，不可能每次更新数据都进行磁盘随机读。必须是读取一个数据页之后放到Buffer Pool的缓存里，下次要更新时就可以直接更新Buffer Pool里的缓存页。

对于磁盘随机读来说，主要关注的性能指标是IOPS和响应延迟latency。

一.影响磁盘随机读的IOPS

这个IOPS，就是指底层存储系统每秒可以执行多少次磁盘随机读写操作。比如每秒执行1000个还是200个磁盘随机读写操作，很影响数据库性能。IOPS指标对数据库的增删改查操作的QPS影响非常大，某种程度上决定了MySQL每秒能执行多少条SQL语句。底层存储的IOPS越高，数据库的并发能力就越强。

二.影响磁盘随机读的响应延迟latency

磁盘随机读写操作的响应延迟，指的是执行一次读写操作耗费多少时间。磁盘随机读写操作的响应延迟，也对数据库性能影响很大。假设底层磁盘支持每秒执行200个随机读写操作，但是每个读写操作耗费10ms完成和耗费1ms完成，差别是很大的。所以响应延迟，决定了MySQL执行单个增删改查SQL语句的性能。

所以对于核心业务的数据库的生产环境机器规划，一般推荐用SSD固态硬盘，而不是机械硬盘。因为固态硬盘的随机读写并发能力和响应延迟都比机械硬盘好得多，可以大幅提升数据库的QPS和性能。

(2)磁盘顺序写操作

已经知道，当Buffer Pool的缓存页更新了数据后，必须要写一条redo日志，这个redo日志就是用了顺序写。

所谓磁盘顺序写，就是指在一个磁盘日志文件里，一直在末尾追加日志。磁盘顺序写的性能其实是很高的，几乎可以跟内存随机读写的性能差不多。尤其是在数据库里其实也用到了OS Cache机制：比如将redo log顺序写入磁盘前，会先进入OS Cache操作系统内存缓存里。

对于磁盘顺序写来说，主要关注的是磁盘每秒读写数据量的吞吐量指标。

比如每秒可以写入磁盘100MB和每秒可以写入磁盘200MB数据，这对数据库的并发能力影响也是很大的。因为每一次更新数据，都必然涉及多个磁盘随机读取数据页的操作，也会涉及一条redo日志的磁盘顺序写操作。当然磁盘日志文件的顺序读写操作的响应延迟，也决定了数据库的性能。写redo日志文件越快，SQL语句性能就越高。

所以磁盘读写的IOPS指标，即每秒可以执行多少个随机读写操作，以及每秒读写磁盘数据量的吞吐量指标，即每秒可写入多少redo日志，整体决定了数据库的并发能力。而磁盘随机读写操作的响应延迟和顺序读写操作的响应延迟，整体决定了数据库执行SQL语句的性能。

2.Linux存储系统软件层原理及IO调度优化原理

Linux的存储系统分为：VFS层、文件系统层、Page Cache缓存层、通用Block层、IO调度层、Block设备驱动层、Block设备层。

步骤一：

当MySQL发起一次数据页的随机读写或redo日志文件的顺序读写时，会把磁盘IO请求交给Linux的VFS层。VFS层的作用，就是根据用户要对哪个目录中的文件执行的磁盘IO操作，把IO请求交给具体的文件系统。比如在Linux中，有的目录里的文件是由NFS文件系统管理的，有的目录里的文件是由Ext3文件系统管理的。这时VFS层需要根据用户对哪个目录下的文件发起的读写IO请求，把请求转交给对应的文件系统。

步骤二：

接着文件系统会先在Page Cache这个基于内存的页缓存里，查找是否有用户需要的数据。如果有就直接基于内存页缓存来执行读写，如果没有就继续往下执行。即把请求交给通用Block层，把用户对文件的IO请求转换为Block IO请求。

步骤三：

IO请求转换为Block IO请求后，会把这个Block IO请求交给IO调度层。在IO调度层这一层里默认使用CFQ公平调度算法。

这个算法的意思是，假如数据库收到了多条SQL语句同时在执行IO操作，有一条SQL语句"update where id = 1"只需更新磁盘上的一个数据即可，有另外一条SQL语句"select *"可能需要IO读取磁盘上的大量数据。那么根据CFQ公平调度算法，会导致数据库先执行第二条SQL读取大量数据的IO操作，可能耗时较久。而第一个仅更新少量数据的SQL的IO操作，就一直在等待，得不到执行。

所以生产环境一般建议调整IO调度层的调度算法为deadline IO调度算法。该算法的核心思想是，任何一个IO操作都不能一直不停的等待，在指定的时间范围内，都必须让IO操作去执行。

基于deadline IO调度算法，上面第一条SQL语句的更新少量数据的IO操作可能在等待一会后，就可以得到执行机会了，这也是一个生产环境的IO调度优化经验。

步骤四：

当IO完成调度后，就会决定哪个IO请求先执行，哪个IO请求后执行。此时可以执行的IO请求就会交给Block设备驱动层。这一层又会经过驱动把IO请求发送给真正的存储硬件，即Block设备层。

步骤五：

最后硬件设备完成IO读写操作后，会把结果沿着进来的层次依次返回。最终MySQL就可以得到本次IO读写操作的结果。这也就是MySQL和Linux存储系统交互的原理。

3.数据库服务器使用的RAID存储架构介绍

一般很多数据库部署在机器上时，存储都是搭建RAID存储架构。RAID就是一个磁盘冗余阵列；

磁盘冗余阵列的解释是：假设服务器里的磁盘就一块，一旦磁盘容量不够，可以多加几块磁盘。但机器里有多块磁盘后，怎么进行管理，及怎么在多块磁盘上存放数据？所以针对该问题，在存储层面会往机器加多块磁盘，然后引入RAID技术。因此可以理解为RAID就是用来管理机器里多块磁盘的一种磁盘阵列技术。有了RAID之后，服务器在往磁盘里读写数据时，RAID就会告诉服务器应该往哪块磁盘上读写数据。

有了RAID这种多磁盘阵列技术后，就可以在一台服务器里加多块磁盘，扩大服务器的磁盘存储空间了。当需要往磁盘里写数据时，通过RAID技术可以帮助选择一块磁盘写入。当需要从磁盘读取数据时，通过RAID技术也知道从哪块磁盘去读取。此外，RAID技术很重要的一个作用就是它可以实现数据冗余机制。

所谓的数据冗余机制：就是如果往一块磁盘写入一批数据，但这块磁盘坏了，则数据就会丢失。而RAID磁盘冗余阵列技术，可以往两块磁盘上都写入同样的一份数据。这样可以让两块磁盘上的数据一样，作为冗余备份。即便当一块磁盘坏掉了，也可以从另外一块磁盘读取冗余数据出来。而这一切都是RAID技术自动帮忙管理的，所以RAID技术实际上就是管理多块磁盘的一种磁盘冗余阵列技术。

4.数据库Too many connections故障定位

数据库无法连接的问题，异常信息通常是：

ERROR 1040(HY000): Too many connections

这个异常说明数据库的连接池已经有太多的连接，不能再和客户端建立新的连接了。

通过数据库整体架构原理可知：数据库自己其实是有一个连接池的，每个Java系统服务实例也有一个连接池。Java系统的每个连接Socket都会对应数据库连接池里的一个连接Socket。当出现"Too many connections"时，说明它的连接池的连接已经满了，Java业务系统不能跟它建立更多的连接。

一.一个生产案例

数据库部署在64GB的大内存物理机上，机器配置各方面都很高。连接这台物理机的Java系统部署在2台机器上。Java系统设置的连接池最大是200，所以Java系统总共最多会和数据库建立400个连接。

但是这时数据库却报了Too many connections异常，说明当前数据库无法建立400个网络连接，这么高配置的数据库机器居然不能建立400个连接。

于是检查一下MySQL的配置文件my.cnf，里面有一个关键的参数max_connections。max_connections表示的是MySQL能建立的最多连接数，设置为800。

这就奇怪了，明明设置了MySQL最多可以建立800个连接，居然两台机器要建立400个连接都不行。

这时登录MySQL执行如下命令进行线上确认：

mysql> show varilables like 'max_connections';

显示的结果是：当前MySQL仅仅只是建立了214个连接而已。因此猜测，MySQL是根本不管my.cnf设置的max_connections，就直接强行把最大连接数设置为214了。于是可以去检查一下MySQL的启动日志，可以看到如下字样：

Could not increase number of max_open_files to more than mysqld (request:65535)
Changed limits: max_connections: 214 (requestd 2000)
Changed limits: table_open_cache: 400 (requestd 4096)

这表明MySQL发现自己无法设置max_connections为我们期望的800，只能强行限制为214了，而导致这种情况的原因就是，Linux操作系统把进程可以打开的文件句柄数限制为1024了，最终导致MySQL最大连接数是214。

二.问题的解决

使用"ulimit -HSn"更改最大文件句柄数：

$ ulimit -HSn 65535

然后使用如下命令进行检查修改是否生效：

$ cat /etc/security/limits.conf
$ cat /etc/rc.local

如果都修改好后，在my.cnf里也确保max_connections参数调整好了，就可以重启服务器重启MySQL了，这样Linux的最大文件句柄就会生效，也就是让MySQL的最大连接数生效。

为何Linux的最大文件句柄限制为1024时，MySQL的最大连接数是214？

因为这是MySQL源码写死的，源码中有一个计算公式，算下来就是214。

Linux的ulimit命令的作用是什么？Linux默认会限制每个进程对机器资源的使用：包括可打开的文件句柄数的限制、可打开的子进程数的限制、网络缓存的限制、最大可锁定的内存大小等。

Linux操作系统设计的初衷是：尽量避免某个进程突然耗尽机器上的所有资源，所以才会默认进行限制。

对于我们来说常见的一个问题就是，文件句柄的限制。如果Linux限制一个进程的文件句柄太少的话，那么就会导致这个进程没法创建大量的网络连接，无法正常工作。

MySQL运行时就是Linux上的一个进程，当它需要跟很多业务系统建立大量连接时，如果限制最大文件句柄数量，就不能建立太多连接。

因此如果在生产环境部署一个系统，比如数据库系统、消息中间件系统、存储系统、缓存系统后，都需要调整一下Linux的一些内核参数，而这个文件句柄的数量是一定要调整的，通常都得设置为65535。还有比如Kafka之类的消息中间件，在生产环境部署时，如果不优化Linux内核参数，可能会导致Kafka无法创建足够的线程运行。

我们平时可以用ulimit命令来设置每个进程被限制使用的资源量，用ulimit -a就可以看到进程被限制使用的各种资源的量。

"core file size"代表进程崩溃时的转储文件的大小限制；
"max locked memory"就是最大锁定内存大小；
"open files"就是最大可以打开的文件句柄数量；
"max user processes"就是最多可以拥有的子进程数量；