MySQL：如何配置buffer pool

如何通过多个buffer pool来优化数据库的并发性能

buffer pool在访问的时候需要加锁吗？

我们对MySQL执行CRUD操作时候的第一步，就是利用buffer pool里的缓存来更新或者查询。

buffer pool本质上就是一大块内存数据结构，由一大堆的缓存页和描述数据块组成的，然后加上了各种链表（free、flush、lur）来辅助它运行。

那么，假设MySQL同时接收到了多个请求，它自然会用多个线程来处理这多个请求，每个线程会负责处理一个请求。限制多个线程来并发访问这个buffer pool了，那么这个时候是不是必须要加锁呢？

对，多线程并发访问一个buffer pool，必然是要加锁的，然后让一个线程先完成一系列的操作，比如说加载数据页到缓存页，更新free链表，更新lur链表，然后释放锁，接着下一个线程再执行一系列的操作。

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

• 即使就一个buffer pool，即使多个线程会加锁串行着排队执行，此时性能也差不到哪里去。
• 因为大部分情况下，每个线程都是查询或者更新缓存页里的数据，这个操作是发生在内存里的，基本都是微秒级，很快很快，包括更新free、flush、lru这些链表，它因为都是基于链表进行的一些指针操作，性能也是极高的。
• 所以，即使每个线程排队加锁，然后执行一系列操作，数据库性能也是还可以的。
• 但是再怎么可以，毕竟也是每个线程加锁然后排队一个一个操作，也不是特别的号，特别是有时候线程拿到锁之后，它可能要从磁盘里读取数据页加载到缓存页里去，这时候发生了一次磁盘IO！耗时就更多一点了，后面排队线程就要多等一会儿了。

MySQL的生产优化经验：多个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中加锁和执行自己的操作，大家可以并发来执行了！

所以这个在实际生产环境中，设置多个buffer pool来优化高并发访问性能，是mysql一个很重要的优化技巧。

如何通过chunk来支持数据库运行期间的buffer pool动态调整？

buffer pool这种大块头，能在运行期间动态调整大小吗？

就当前来看，buffer pool在数据库运行期间是不能动态的调整自己的大小的。

为什么呢？因为动态调整buffer pool大小，比如buffer pool本来是8G，运行期间你给调整为16G了，此时要怎么实现呢？这个时候就需要向操作系统申请一块新的16GB的连续内存，然后把现在的buffer pool中的所有缓存页、描述数据块、各种链表，都拷贝到新的16GB的内存中去。这个过程是极为耗时，性能很低下，是不可以接受的。

如何基于chunk机制把buffer pool给拆小呢？

MySQL为优化上面问题，设计了一个chunk机制，也就是buffer pool是由很多chunk组成的，它的大小是由innodb_buffer_pool_chunk_size参数控制的，默认值就是128MB。

比如现在我们给buffer pool设置一个总大小是8GB，然后有4个buffer pool，那么每个buffer pool就是2GB，此时每个buffer pool是由一系列的128MB的chunk组成的，也就是说每个buffer pool会有16个chunk。

每个buffer pool里的每个chunke里就是一系列的描述数据块和缓存页，每个bufffer pool里的多个chunk共享一套free、flush、lru这些链表。如下图：

基于chunk机制是如何支持运行期间，动态调整buffer pool大小的？

比如当前buffer pool现在总大小是8GB，现在要动态加到16GB，那么此时只要申请一系列的128MB大小的chunk就可以了，只要每个chunk是连续的128MB内存就行了，然后把这些申请到的chunk内存分配给buffer pool就行了。

有个这个chunk机制，此时并不需要额外申请16GB的连续内存空间，然后还要把已有的数据进行拷贝。

这里并不是让大家在数据库运行的时候动态调整buffer pool大小，而是要了解数据库的buffer pool的真实的数据结构，是可以由多个buffer pool组成的，每个buffer pool是多个chunk组成的，然后运行期间可以支持动态调整大小。

在生产环境中，如何基于机器配置来合理设置Buffer Pool？

生产环境中应该给buffer pool设置多少内存？

问题：我们现在数据库部署在一台机器上，这台机器可能有个8G、16G、32G、64G、128G的内存大小，那么此时buffer pool应该设置多大呢？

建议：给buffer pool设置你的机器内存的50%~60%左右

比如你有32GB的机器，那么给buffer设置个20GB的内存，剩下的留给OS和其他人来用，这样比较合理一些。

假设你的机器是128GB的内存，那么buffer pool可以设置个80GB左右，大概就是这样的一个规则。

buffer pool总大小=(chunk大小 * buffer pool数量)的2倍数

接着确定了buffer pool的总大小之后，就得考虑一下设置多少个buffer pool，以及chunk的大小了
此时要记住，有一个很关键的公式就是：buffer pool总大小=(chunk大小 * buffer pool数量)的倍数

比如默认的chunk大小是128MB，那么此时如果你的机器的内存是32GB，你打算给buffer pool总大小在20GB左右，那么你得算一下，此时你的buffer pool的数量应该是多少个呢？

假设你的buffer pool的数量是16个，这是没问题的，那么此时chunk大小 * buffer pool的数量 = 16 * 128MB =
2048MB，然后buffer pool总大小如果是20GB，此时buffer pool总大小就是2048MB的10倍，这就符合规则了。

当然，此时你可以设置多一些buffer pool数量，比如设置32个buffer pool，那么此时buffer pool总大小（20GB）就是（chunk大小128MB * 32个buffer pool）的5倍，也是可以的。

那么此时你的buffer pool大小就是20GB，然后buffer pool数量是32个，每个buffer pool的大小是640MB，然后每
个buffer pool包含5个128MB的chunk，算下来就是这么一个结果了。

SHOW ENGINE INNODB STATUS

当你的数据库启动之后，你随时可以通过SHOW ENGINE INNODB STATUS命令，去查看当前innodb里的一些具体情况：

Total memory allocated xxxx;
Dictionary memory allocated xxx
Buffer pool size xxxx
Free buffers xxx
Database pages xxx
Old database pages xxxx
Modified db pages xx
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young xxxx, not young xxx
xx youngs/s, xx non-youngs/s
Pages read xxxx, created xxx, written xxx
xx reads/s, xx creates/s, 1xx writes/s
Buffer pool hit rate xxx / 1000, young-making rate xxx / 1000 not xx / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: xxxx, unzip_LRU len: xxx
I/O sum[xxx]:cur[xx], unzip sum[16xx:cur[0]

跟buffer pool相关的一些输出：

• Total memory allocated，这就是说buffer pool最终的总大小是多少
• Buffer pool size，这就是说buffer pool一共能容纳多少个缓存页
• Free buffers，这就是说free链表中一共有多少个空闲的缓存页是可用的
• Database pages和Old database pages，就是说lru链表中一共有多少个缓存页，以及冷数据区域里的缓存页数量
• Modified db pages，这就是flush链表中的缓存页数量
• Pending reads和Pending writes，等待从磁盘上加载进缓存页的数量，还有就是即将从lru链表中刷入磁盘的数量、即将从flush链表中刷入磁盘的数量
• Pages made young和not young，这就是说已经lru冷数据区域里访问之后转移到热数据区域的缓存页的数量，以及在lru冷数据区域里1s内被访问了没进入热数据区域的缓存页的数量
• youngs/s和not youngs/s，这就是说每秒从冷数据区域进入热数据区域的缓存页的数量，以及每秒在冷数据区域里被访问了但是不能进入热数据区域的缓存页的数量
• Pages read xxxx, created xxx, written xxx，xx reads/s, xx creates/s, 1xx writes/s，这里就是说已经读取、创建和写入了多少个缓存页，以及每秒钟读取、创建和写入的缓存页数量
• Buffer pool hit rate xxx / 1000，这就是说每1000次访问，有多少次是直接命中了buffer pool里的缓存的
• young-making rate xxx / 1000 not xx / 1000，每1000次访问，有多少次访问让缓存页从冷数据区域移动到了热数据区域，以及没移动的缓存页数量
• LRU len：这就是lru链表里的缓存页的数量
• I/O sum：最近50s读取磁盘页的总数
• I/O cur：现在正在读取磁盘页的数量

这里要尤为关注的是free、lru、flush几个链表的数量的情况，然后就是lru链表的冷热数据转移的情况，然后你的缓存页的读写情况，这些代表了你当前buffer pool的使用情况。
最关键的是两个东西，一个是你的buffer pool的千次访问缓存命中率，这个命中率越高，说明你大量的操作都是直接基于缓存来执行的，性能越高。
第二个是你的磁盘IO的情况，这个磁盘IO越多，说明你数据库性能越差。

总结

数据库在生产环境运行的时候，必须根据机器的内存设置合理的buffer pool的大小，然后设置buffer pool的数量，这样的话，可以尽可能的保证你的数据库的高性能和高并发能力。

然后在线上运行的时候，buffer pool是有多个的，每个buffer pool里有多个chunk共用一套链表数据结构，然后执行CRUD的时候，就会不停的加载磁盘上的数据页到缓存页里来，然后会查询和更新缓存页里的数据，同时维护一系列的链表结构。

然后后台线程定时根据lru链表和flush链表，去把一批缓存页刷入磁盘释放掉这些缓存页，同时更新free链表

如果执行CRUD的时候发现缓存页都满了，没法加载自己需要的数据页进缓存，此时就会把LRU链表冷数据区域的缓存页刷入磁盘，然后加载自己需要的数据页进来

问题

• 如何避免执行CRUD的时候，频繁的发现缓存页都用完了，完了还需要先把一个缓存页刷入磁盘腾出一个空闲缓存页，然后才能从磁盘读取一个自己需要的数据页到缓存页里来。
• 如果频繁这么搞，那么很多crud操作，每次都要执行两次磁盘IO，一次是缓存页刷入磁盘，一次是数据页从磁盘里读取出来，性能是很不高的。

如果要避免上面问题，本质就是要避免缓存页频繁的被使用完毕。而实际在使用缓存页的过程中，有一个后台线程就定时把LRU链表冷数据区域的一些缓存页刷入磁盘。

也就是说缓存页一边被使用，一边被后台线程定时释放掉一批。

所以如果缓存页使用的很快，然后后台线程释放缓存页的速度很慢，那么必然导致这个问题。

• 而缓存页被使用的速度是你没法控制的，这是由外部系统访问数据库的并发程序决定的，高并发访问数据库，缓存页必然就使用很快了。
• 后台线程定时释放一批缓存页，这个过程也很难优化。因为你要是释放的过于频繁了，那么后台线程执行磁盘IO过于频繁，也会影响数据库的性能

所以关键在于你的buffer pool有多大

• 如果你的数据库要抗高并发的访问，那么你的机器必然要配置很大的内存空间，起码是32GB以上的，甚至64GB或者128GB。此时你就可以给你的buffer pool设置很大的内存空间，比如20GB，48GB，甚至80GB。
• 这样的话，你会发现高并发场景下，数据库的buffer pool缓存页频繁的被使用，但是后台线程也在定时释放一些缓存页。那么综合下来，空闲的缓存页还是会以一定的速率逐步的减少。
• 因为buffer pool足够大，所以空闲缓存页很多。也就是说 ，即使你的空闲缓存页逐步的减少，也可能需要较长时间才会发现缓存页用完了，此时才会出现一次CRUD操作执行的时候，先刷缓存页到磁盘，再读取数据页到缓存页来，这种情况是不会出现太频繁的
• 而一旦你的数据库高峰过去，此时缓存页被使用的速率下降了很多很多，然后后台线程会定时基于flush链表和lru链表不停地释放缓存页，因此空闲缓存页的数量又会在数据库低峰的时候慢慢增加了
• 所以线上的MySQL在生产环境中，buffer pool的大小、buffer pool的数量，这都是要用心设置和优化的，因为多MySQL的性能和并发能力，都会有较大的影响。