【MySQL】十八、MySQL数据库生产环境应用经验分享（二）

本文主要介绍MySQL数据库的在生产环境的使用经验分享

4 在数据库的压测过程中，如何360度无死角观察机器性能？

压测的时候，我们需要不停的增加线程的数量去让数据库承载更高的QPS，一直到最后看看数据库到底最高可以承载多高的QPS。在这个过程中，必须要关注机器的CPU、内存、磁盘和网络的负载情况，在硬件负载情况比较正常的范围内，哪怕负载相对高一些，也还是可以继续增加线程数量和提高数据库的QPS的。

当你不停的增加线程数量，发现在数据库抗下一个QPS的数值的同时，机器的CPU、内存、网络和磁盘的负载已经比较高了，到了一个有一定风险的临界值了，此时就不能再继续增加线程数量和提高数据库抗下的QPS了。

4.1 压测时如何观察机器的CPU负载情况？

先来看一个最常用的监测linux机器性能的命令，就是top命令，可以在控制台展示如下信息

top - 15:52:00 up 42:35, 1 user, load average: 0.15, 0.05,0.01

这行信息是最直观可以看到机器的cpu负载情况的，首先15:52:00指的是当前时间，up 42.35指的是机器已经运行了多少时间，1 user就是当前机器有1个用户在使用。

最重要的是load average: 0.15, 0.05,0.01这行信息，他说的是CPU在1分钟、5分钟、15分钟内的负载情况

CPU负载，假设机器是一个4核的CPU，此时如果CPU负载是0.15，这就说明，4核CPU连一个核都没用满，4核CPU基本都很空闲，没人使用。

如果CPU负载是1，那说明4核CPU中有一个核已经被使用的比较繁忙了，另外3个核还是比较空闲一些，要是CPU负载是1.5，说明有一个核被使用繁忙，另外一个核也在使用，但是没那么繁忙，还有2个核可能还是空闲的。

如果CPU负载是4，那说明4核CPU都被跑满了，如果你的CPU负载是6，那说明4核CPU被繁忙的使用还不够处理当前的任务，很多进程可能一直在等待CPU去执行自己的任务。

如果在压测过程中，发现4核CPU的load average已经基本达到了3.5、4了，那么说明几个CPU基本都跑满了，在满负荷运转，那么此时就不要再继续提高线程的数量和增加数据库的QPS了，否则CPU负载太高是不合理的。

4.2 压测时如何观察机器的内存负载情况？

在执行top命令后，中间我们跳过几行内容，可以看到如下一行内容：

Mem:33554432k total, 20971520k used, 12268339 free, 307200k buffers

这里说的就是当前机器的内存使用情况，这个其实很简单，明显可以看出来就是总内存大概有32GB，已经使用了20GB左右的内存，还有10多G的内存是空闲的，然后有大概300MB左右的内存用作OS内核的缓冲区了。

对于内存而言，同样是要在压测得到过程中紧密的观察，一般来说，如果内存的使用率在80%以内，基本都还能接受，在正常范围内，但是如果你的机器的内存使用率到了70% ~ 80%了，就说明有点危险了，此时就不要继续增加压测的线程数量和QPS了。

4.3 压测时如何观察机器的磁盘IO情况？

使用dstat 命令查看磁盘IO的情况

使用dstat -d命令，会看到如下信息：

-dsk/total -
read writ
103k 211k
0 11k

在上面可以清晰看到，存储的IO吞吐量是每秒读取103kb的数据，每秒写入211kb的数据，像这个存储IO吞吐量基本上都不算多的，因为普通的机械硬盘都可以做到每秒钟上百MB的读写数据量。

使用命令：dstat -r，可以看到如下信息

--io/total-
read writ
0.25 31.9
0 253
0 39.0

这个信息的意思是读IOPS和写IOPS分别是多少，也就是说随机磁盘读每秒钟多少次，随机磁盘写入每秒钟执行多少次，大概就是这个意思，一般来说，随机磁盘读写每秒在两三百次都是可以承受的。

所以我们需要在压测的时候密切观察机器的磁盘IO情况，如果磁盘IO吞吐量已经太高了，都达到极限的每秒上百MB了，或者随机磁盘读写每秒都到极限的两三百次了，此时就不要继续增加线程数量了，否则磁盘IO负载就太高了。

4.4 压测时观察网卡的流量情况

使用dstat -n命令可以看到如下信息

-net/total-
recv send
16k 17k

这个说的是每秒钟网卡接收到流量有多少kb，每秒钟通过网卡发送出去的流量有多少kb，通常来说，如果你的机器使用的是千兆网卡，那么每秒钟网卡的总流量也就在100MB左右，甚至更低一些。

所以在压测的时候，也得观察网卡的流量情况，如果网卡传输流量已经达到了极限值，那么此时再怎么提高线程数量，数据库的QPS也上不去了，因为这台机器每秒钟无法通过网卡传输更多的数据了。

5 如何通过多个Buffer Pool来优化数据库的并发性能？

5.1 Buffer Pool在访问的时候需要加锁吗？

Buffer Pool本质就是一大块内存数据结构，由一大堆的缓存页和描述数据块组成的，然后加上各种链表（free、flush、LRU）来辅助他的运行。这个时候假设MySQL同时接收了多个请求，他自然会用多个线程来处理这些请求，每个线程会负责处理一个请求。

然后这多个线程应该会同时去访问Buffer Pool，也就是同时去操作里面的缓存页，同时操作一个free链表、flush链表、LRU链表，下图就是一个多线程并发访问Buffer Pool的示意图：

现在多个线程来并发的访问这个Buffer Pool了，此时他们都是在访问内存里的一些共享的数据结构，比如说缓存页、各种链表之类的，那么此时是不是必然要进行加锁？是的，多线程并发访问一个Buffer Pool，必然是要加锁的，然后让一个线程先完成一系列的操作，比如说加载数据页到缓存页，更新free链表，更新lru链表，然后释放锁，接着下一个线程再执行一系列的操作。

5.2 多线程并发访问加锁，数据库的性能还能好吗?

多线程并发访问一个Buffer Pool的时候必然加锁，然后多线程可能要串行着排队，一个一个的依次执行操作，此时数据库的性能还好吗？

其实即使就一个Buffer Pool，即使多个线程会加锁串行着排队执行，性能也差不到哪儿去，因为大部分情况下，每个线程都是查询或者更新缓存页里的数据，这个操作是发生在内存中的，基本都是微秒级的，很快很快，包括更新free、flush、lru这些链表，因为都是基于链表进行一些指针操作，性能也是极高的。

所以即使每个线程排队加锁，然后执行一系列操作，数据库的性能倒也是还可以的。

但是毕竟也是每个线程加锁然后一个一个操作，这也不是特别的好，特别是有时候你的线程拿到锁之后，他可能要从磁盘里读取数据页加载到缓存页里去，这还发生了一次磁盘IO呢，所以他要是进行磁盘IO的话，也许耗时就会多一些，后面排队等他的线程自然就多等一会儿了。

5.3 多个Buffer Pool优化并发能力

一般来说，MySQL默认的规则是，如果你给Buffer Pool分配的内存小于1GB，那么最多就只会给你一个Buffer Pool。

但是如果你的机器内存很大，那么你必然会给Buffer Pool分配较大的内存，比如给他个8G内存，那么此时是同时可以设置多个Buffer Pool的，比如说下面的MySQL服务器端的配置

[server]
innodb_buffer_pool_size = 8589934592
innodb_buffer_pool_instances = 4

我们给buffer pool设置了8GB的总内存，然后设置了他应该有4个Buffer Pool，此时就是说，每个buffer pool的大小就是2GB。

这个时候，MySQL在运行的时候就会有4个Buffer Pool了，每个Buffer Pool负责管理一部分的缓存页和描述数据块，有自己独立的free、flush、lru等链表。

这个时候，假设多个线程并发过来访问，那么不就可以把压力分散开来了吗，有的线程访问这个buffer pool，有的线程访问那个buffer pool。如下图：

这样的话，一旦你有了多个buffer pool之后，多线程并发访问的性能就会得到成倍的提升，因为多线程可以在不同的buffer pool中加锁和执行自己的操作，大家可以并发来执行了。