MYSQL实战优化——buffer pool生产经验

数据库并发性能优化

1、buffer pool在访问的时候需要加锁吗
buffer pool本质就是一大块内存数据结构，由一大堆的缓存页和描述数据块组成，然后加上了各种链表（free、flush、lru）来辅助它的运行。

假设MySQL同时接收到了多个请求，它自然会用多个线程来处理这多个请求，每个线程会负责处理一个请求。然后这多个线程应该会同时去访问buffer pool，就是同时去操作里面的缓存页，同时操作一个free链表、flush链表、lru链表。

那么我们想一下，现在多个线程来并发的访问这个buffer pool，此时它们都是在访问内存里的一些共享的数据结构，比如说缓存页、各种链表之类的，那么此时是不是必然要进行加锁？对，多线程并发访问一个buffer pool必然要加锁，让一个线程先完成一系列的操作，接着下一个线程再执行一系列的操作。

$\color{red}{多线程并发访问加锁，数据库的性能还好吗？}$
其实性能也不差，因为大部分情况下，每个线程都是查询或者更新缓存页里的数据，这个操作是发送在内存里的，基本都是微秒级的，会很快，包括更新free、flush、lru这些链表，因为都是基于链表进行一些指针操作，性能也是极高的。所以即使加锁，数据库的性能也还可以的。

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

2、多个buffer pool优化并发能力
对于上述问题，我们可以给MySQL设置多个buffer pool来优化它的并发能力。一般来说，MySQL默认的规则是，如果你给buffer pool分配的内存小于1GB，那么最多就只会给你一个buffer pool。但是如果你的机器内存很大，那么你必然会给buffer pool分配较大的内存，比如给它个8GB内存，那么此时你可以同时设置多个buffer pool，比如说下面的MySQL服务器端的配置：

innodb_buffer_pool_size = 8589934592
innodb_buffer_pool_instances = 4

我们给buffer pool设置了8GB的总内存，然后设置了4个buffer pool，那么每个buffer pool的大小就是2GB。这个时候，假设多个线程并发过来访问，那么就可以把压力分散开来了。有的线程访问这个buffer pool，有的线程访问那个buffer pool。这个时候并发访问的性能就会得到成倍的提升。

数据库运行期间的buffer pool动态调整

MySQL设计了一个chunk机制，也就是说buffer pool是由很多chunk组成的，它的大小是innodb_buffer_pool_chunk_size参数控制的，默认值就是128MB。每个buffer pool里的每个chunk就是一系列的描述数据块和缓存页，每个buffer pool里的多个chunk共享一套free、flush、lru这些链表，此时的话，看起来可能大致如下图所示：

chunk_1.jpg

上面的图里可以清晰的看到，每个buffer pool里已经有了很多chunk，每个chunk就是一系列的描述数据块和缓存页，这样的话，就是把buffer pool按照chunk为单位，拆分为了一系列的小数据块，但是每个buffer pool是共用一套free、flush、lru的链表。

那么现在有了chunk机制，就可以支持动态调整buffer pool大小了。比如buffer pool现在总大小是8GB，现在要动态调整到16GB，那么此时只要申请一系列的128MB大小的chunk就可以了，只要每个chunk是连续的128MB内存，然后把这些申请到的chunk内存分配给buffer pool就行了。我们了解这个chunk机制并不是要在数据库运行的时候动态调整buffer pool的大小，而是要了解buffer pool的数据结构，知道它可以在运行期间支持动态调整就行了。

show engine InnoDB status
当你启动数据库之后，你随时可以通过上述命令去查看当前innodb里的一些具体情况，执行show engine InnoDB status就可以了，此时你可能会看到如下一系列东西：

 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total large memory allocated 137428992    -- buffer pool最终的大小是多少、单位byte
 Dictionary memory allocated 3351525       -- 分配给innodb 数据字典的内存大小、单位byte
 Buffer pool size   8192                   -- buffer pool一共能容纳多少个缓存页
 Free buffers       1024                   -- free链表中一共有多少个空闲的缓存页
 Database pages     7045                   -- lru链表中一共有多少个缓存页
 Old database pages 2580                   -- lru链表冷数据区域里的缓存页数
 Modified db pages  0                      -- flush链表中的缓存页数量
 Pending reads      0                      -- 等待从磁盘上加载进缓存页的数量
 Pending writes: LRU 0, flush list 0, single page 0  -- 即将从lru链表、flush链表刷入磁盘的数量
 Pages made young 5477, not young 109336  -- lru冷数据区域里被访问过后转移到热数据区域的缓存页数量，以及lru冷数据区域里1s内被访问了没进入热数据区域的缓存页数量
 0.00 youngs/s, 0.00 non-youngs/s  --每秒从冷数据区域进入热数据区域的缓存页数量，以及每秒在冷数据区域里被访问了但是不能进入热数据区域的缓存页数量
 Pages read 7576, created 1078, written 418265  -- 已经读取、创建和写入了多少个缓存页，
 0.00 reads/s, 0.00 creates/s, 0.00 writes/s   -- 每秒读取、创建和写入的缓存页
 Buffer pool hit rate 1000 / 1000,  -- 每1000次访问，有多少次直接命中了buffer pool里的缓存
 young-making rate 0 / 1000 not 0 / 1000  -- 每1000次访问，有多次访问让缓存页从冷数据区域移动到了热数据区域，以及没移动的缓存页数量
 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
 LRU len: 7045, unzip_LRU len: 0  -- lru链表里的缓存页数量
 I/O sum[88]:cur[0], unzip sum[0]:cur[0]   -- sum：最近50s读取磁盘页的总数，cur：现在正在读取磁盘页的数量
 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 0 read views open inside InnoDB
 Process ID=21826, Main thread ID=140480130983680 , state=sleeping
 Number of rows inserted 31036, updated 66782, deleted 23020, read 658152114
 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 871.69 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================

$\color{green}{本文结束}$