MySQL InnoDB Buffer Pool空间不足导致查询变慢

问题现象

前端调用后台服务查询接口响应很慢，查看 MySQL 的日志，有如下日志：

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (13430 search iterations)! 13430 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 2845050 OS file reads, 85165 OS file writes, 9180 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

问题原因

MySQL InnoDB 存储引擎的 Buffer Pool （缓冲池）没有空闲块了，需要增大 Buffer Pool 的大小。

那 Buffer Pool 是什么？有什么作用？

对于使用MySQL InnoDB存储引擎的表来说，表数据是以页的形式存储在磁盘上，磁盘的访问速度相对于CPU来说很慢，为了调节磁盘和CPU的访问速度， InnoDB 存储引擎在处理客户端的请求时，如果需要访问某个页的数据，就会把完整的页的数据全部加载到内存中，然后通过内存进行读写访问，在读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省下磁盘 I/O 的开销了。

MySQL 服务器启动时向操作系统申请用来缓存表数据的内存，就叫 Buffer Pool（缓冲池）。

通过配置项 innodb_buffer_pool_size 配置 Buffer Pool 的大小，默认是 128M 。

Buffer Pool 的内存被划分为若干个页面，每个页面对应一个控制块。

Buffer Pool 相关的链表

• free 链表：存储空闲的缓冲页信息。

• flush 链表：存储脏页信息。脏页是Buffer Pool 中被修改的缓冲页，和磁盘上的数据不一致。

• LRU （Least Recently Used）链表：管理当缓冲池不够用时，需要将哪些缓冲页从内存中移除。

Buffer Pool 实例及 chunk

为了提高Buffer Pool 的并发处理能力，可以通过配置项 innodb_buffer_pool_instances 配置多个 Buffer Pool 实例，每个实例是独立的（独立地申请内存空间，独立地管理各种链表）。

每个 Buffer Pool 实例以 chunk 为单位向操作系统申请内存空间，chunk 的大小默认值是 128MB，可以通过配置项 innodb_buffer_pool_chunk_size 修改。

查看 Buffer Pool的信息

• 查看 Buffer Pool 大小：show variables like 'innodb_buffer_pool_size';

• 查看 Buffer Pool 实例个数：show variables like 'innodb_buffer_pool_instances';

• 查看 chunk 大小：show variables like 'innodb_buffer_pool_chunk_size';

• 查看 Buffer Pool 状态：show engine innodb status\G

  mysql> show engine innodb status\G
  ...
  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  Total large memory allocated 2198863872
  Dictionary memory allocated 1889983
  Buffer pool size   131072
  Free buffers       8685
  Database pages     121738
  Old database pages 44777
  Modified db pages  25340
  Pending reads      0
  Pending writes: LRU 0, flush list 1, single page 0
  Pages made young 57648, not young 454686
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 49231, created 86169, written 1538233
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 121738, unzip_LRU len: 0
  I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  ----------------------
  INDIVIDUAL BUFFER POOL INFO
  ----------------------
  ---BUFFER POOL 0
  Buffer pool size   16384
  Free buffers       1077
  Database pages     15216
  Old database pages 5596
  Modified db pages  3136
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 6486, not young 52789
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 5894, created 11149, written 196015
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 15216, unzip_LRU len: 0
  I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 1
  Buffer pool size   16384
  Free buffers       1088
  Database pages     15215
  Old database pages 5596
  Modified db pages  3136
  Pending reads      0
  Pending writes: LRU 0, flush list 1, single page 0
  Pages made young 6933, not young 5973
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 6033, created 10759, written 184737
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 15215, unzip_LRU len: 0
  I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 2
  Buffer pool size   16384
  Free buffers       1077
  Database pages     15228
  Old database pages 5601
  Modified db pages  3132
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 7429, not young 81168
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 6002, created 10761, written 209768
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 15228, unzip_LRU len: 0
  I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 3
  Buffer pool size   16384
  Free buffers       1093
  Database pages     15212
  Old database pages 5595
  Modified db pages  3136
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 7077, not young 65129
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 6317, created 10330, written 208418
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 15212, unzip_LRU len: 0
  I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 4
  Buffer pool size   16384
  Free buffers       1080
  Database pages     15223
  Old database pages 5599
  Modified db pages  3200
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 7127, not young 72730
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 6272, created 10750, written 187345
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 15223, unzip_LRU len: 0
  I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 5
  Buffer pool size   16384
  Free buffers       1094
  Database pages     15215
  Old database pages 5598
  Modified db pages  3200
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 7117, not young 14164
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 6171, created 10595, written 174556
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 15215, unzip_LRU len: 0
  I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 6
  Buffer pool size   16384
  Free buffers       1091
  Database pages     15209
  Old database pages 5594
  Modified db pages  3200
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 7012, not young 97165
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 6141, created 10860, written 179494
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  No buffer pool page gets since the last printout
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 15209, unzip_LRU len: 0
  I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  ---BUFFER POOL 7
  Buffer pool size   16384
  Free buffers       1085
  Database pages     15220
  Old database pages 5598
  Modified db pages  3200
  Pending reads      0
  Pending writes: LRU 0, flush list 0, single page 0
  Pages made young 8467, not young 65568
  0.00 youngs/s, 0.00 non-youngs/s
  Pages read 6401, created 10965, written 197900
  0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  LRU len: 15220, unzip_LRU len: 0
  I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  ...
  

  其中一些字段的含义如下：

  • Total large memory allocated：代表 Buffer Pool 向操作系统申请的连续内存空间大小，包括全部控制块、缓冲页、以及碎片。
  • Buffer pool size：代表该 Buffer Pool 可以容纳多少缓冲页，单位是页。
  • Free buffers：代表当前 Buffer Pool 还有多少空闲缓冲页，也就是 free 链表中还有多少个节点。
  • Database pages：代表 LRU 链表中页的数量。
  • Modified db pages：代表脏页数量，也就是 flush 链表中节点的数量。
  • Buffer pool hit rate：表示在过去某段时间内，平均访问 1000 次页面时，该页面有多少次已经被缓存到 Buffer Pool中。

参考资料

• 《MySQL 是怎样运行的 —— 从根上理解 MySQL》 第 17章 小孩子 1949 著