14.BufferPool之参数配置与优化

---

highlight: arduino-light

innodbbufferpool_size

介绍：配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。海尔生产48G。默认是取内存的百分之60-百分之80

Buffer Pool本质其实就是数据库的一个内存组件，你可以理解为他就是一片内存数据结构，所以这个内存数据结构肯定是有一定的大小的，不可能是无限大的。

这个Buffer Pool默认情况下是128MB，还是有一点偏小了，我们实际生产环境下完全可以对Buffer Pool进行调整。

比如我们的数据库如果是16核32G的机器，那么你就可以给Buffer Pool分配个2GB的内存，使用下面的配置就可以了。

innodb_buffer_pool_size = 2147483648

innodb-buffer-pool-size 推荐设置到 70%-80% 的内存。

然后我去 MySQL 5.7 的官网上查证了下，有写道：On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size.

链接:https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

多实例BufferPool的概述

在Buffer Pool特别大并且多线程并发访问量特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度。所以，在Buffer Pool特别大时，可以把它们拆分成若干个小的Buffer Pool，每个Buffer Pool都称为一个实例。它们都是独立的(即独立地申请内存空间，独立地管理各种链表)。

缓存区本身是一块内存空间，在多线程并发访问缓存的情况下，为了保证缓存页数据的正确性，可能会对缓存区单实例锁互斥访问，如果缓存区非常大并且多线程并发访问非常高的情况下，单实例缓存区的可能会影响请求的处理速度。如下图所示，数据库缓存区大小为3G，并发访问QPS为3000，如果缓存区只有一个实例，那么这3000个请求可能需要竞争同一个互斥锁。

Mysql线程池接受到多个网络请求之后，给每个请求分配一个线程去执行，那么多个线程就会同时去操作Buffer Pool，Buffer Pool中有很多共享的数据结构，比如缓存页、各种链表之类的，所以需要要进行加锁。 让一个线程加锁，先完成一系列的操作，比如说加载数据页到缓存页，更新free链表，更新lru链表，然后释放锁，接着下一个线程再执行一系列的操作。

即使一个Buffer Pool，即使多个线程会加锁串行着排队执行，性能也差不到哪儿去，因为大部分情况下，每个线程都是查询或者更新缓存页里的数据，这个操作发生在内存里，微秒级的，包括更新free、flush、lru这些链表，也都是指针操作，性能也是很高的。但是有可能要从磁盘里读取数据页加载到缓存页里去，发生了一次磁盘IO，就会耗时多一些。

MySQL 5.5引入了缓存区实例作为减小内部锁争用来提高MySQL吞吐量的手段，用户可以通过设置innodb_buffer_pool_instances参数来指定InnoDB缓存区实例的数目，默认缓存区实例的数目为1。缓存区实例的大小均为`innodbbufferpoolsize/innodbbufferpoolinstances。如下图所示，数据库缓存区大小为3G，并发访问QPS为3000，如果缓存区有3个实例，理想情况下最多每1000个请求会竞争同一个互斥锁。

每个Instance之间都是独立的，支持多线程并发访问，且一个page只会被存放在一个固定的Instance中,主要是通过page_id获取Page对应的Buffer Instance。

mysql buf_pool_t *buf_pool = buf_pool_get(page_id);

其实最终还是分段锁的思想。

缓存区实例有以下特点： 1. 缓存区实例有自己的锁/信号量/物理块/freeList、LRU、flushList且缓存区实例之间没有锁竞争。 2. 所有缓存区实例的空间在数据库启动时分配，数据库关闭后释放。 3. 缓存页按照哈希函数随机分布到不同的缓存实例中。

innodbbufferpool_instances

可以通过设置innodbbufferpool_instances的值来修改Buffer Pool实例的个数。

每个Buffer Pool实例实际占用多少内存空间呢，通过如下公式计算：

```mysql

缓冲池实际占用内存空间 = 缓冲池大小 ÷ 缓冲池实例的个数

innodbbufferpoolsize ÷ innodbbufferpoolinstances ``` 由于管理Buffer Pool也是需要性能开销的，所以也并不是实例越多越好。

InnoDB规定，当innodbbufferpoolsize小于1GB时，设置多个实例是无效的，在这种情况下，即使你设置的innodbbufferpoolinstances不为1，那么InnoDB默认也会把它改为1。

配置BufferPool的注意事项

innodbbufferpoolsize必须是innodbbufferpoolchunksize * innodbbufferpoolinstances的倍数。否则服务器会自动把innodbbufferpoolsize的值调整为【innodbbufferpoolchunksize * innodbbufferpoolinstances】结果的整数倍。

``` 例如 innodbbufferpoolchunksize=128MB
innodbbufferpool_instances=16

那么 innodbbufferpoolchunksize * innodbbufferpool_instances=2GB

如果我们设置innodbbufferpool_size=9GB，则会被自动调整为10GB ```

在服务启动时，如果innodbbufferpoolchunksize * innodbbufferpoolinstances > innodbbufferpoolsize的值，那么innodbbufferpoolchunksize的值会被服务器自动的设置为innodbbufferpoolsize ÷ innodbbufferpoolinstances的值。

``` 例如 innodbbufferpoolchunksize=256MB innodbbufferpool_instances=16

那么 innodbbufferpoolchunksize * innodbbufferpool_instances=4GB

如果我们设置innodbbufferpoolsize=2GB，因为4GB > 2GB，则innodbbufferpoolchunk_size被修改为128MB。 ```

innodboldblocks_pct

参数：innodboldblocks_pct

介绍：老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。

画外音：如果把这个参数设为100，就退化为普通LRU了。

innodboldblocks_time

参数：innodboldblocks_time

介绍：老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。

总结

(1)缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；

(2)缓冲池通常以页(page)为单位缓存数据；

(3)缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；

(4)InnoDB对普通LRU进行了优化：

将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题

页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

mysql是使用LRU算法解决缓存污染

redis是使用LFU 淘汰策略解决缓存污染。

参考:https://zhuanlan.zhihu.com/p/408873086?utm_id=0