[Warning] InnoDB: Difficult to Find Free Blocks in the Buffer Pool

MySQL错误日志给出警告信息:

?

1 2 3

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (336 search iterations)! 0 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. 1621989850 OS file reads, 1914021664 OS file writes, 110701569 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.   2019-10-26T15:02:03.962059Z 4520929 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (337 search iterations)! 0 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. 1621989850 OS file reads, 1914021664 OS file writes, 110701569 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

当InnoDB中的线程不断地请求空闲块，但无法从buffer pool中获得空闲块的时候，就会出现上面的警告消息。实际中，并不是这么简单，可能还有其他事情发生，懂得如何构建buffer pool中的块，就可以轻松的明白。

 

buffer pool主要由三个列表组成：

1.free list：空闲列表，线程可以从磁盘读入数据到列表中的页

2.lru list：最近最少使用列表

3.flush list：脏的或被修改的页的列表

 

正如我们所知，每个请求都是有InnoDB中的线程来处理。如果所需的页在buffer pool中不可用，则必须从磁盘中读取它并将其放入其中，为此需要从空闲列表(free list)中分配一个空闲块。空闲块只能从空闲列表中获取，所以需要空闲块的线程会经历以下的迭代过程：

·从free list中寻找一个空闲的块，如果有可用就分配它。如果不是,继续。

·扫描lru列表，如果找到干净(clean)的块，就将块移动到free list中，并分配。如果没有，继续。

·从lru列表的尾部刷新脏页，将其移动到free list，并分配。

这里是简化的步骤，只是为了便于理解，实际并不是这么简单，但类似。在任何时刻，如果块被移动到free list，就可以被任何线程使用，就可能使得当前的线程无法获取到块。当前线程就会每间隔10ms执行一次请求。

 

那什么时候会将警告信息写入日志呢？

 当一个线程执行超过20迭代，就会将警告信息写入日志。下面是对应的源码块：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

if (n_iterations > 20 & & srv_buf_pool_old_size == srv_buf_pool_size) {< strong > <= == == == Here it is < / strong > ib::     warn(ER_IB_MSG_134) \     << "Difficult to find free blocks in the buffer pool" \        " (" \     << n_iterations << " search iterations)! " << flush_failures \     << " 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: "

　　

遇到这种警告，可能是由很多原因造成的，以下的一些清单可以用来确认原因。尽管它已经多次使IO子系统饱和，但它不可能对每个实例和工作负载都相同的。

·IO子系统慢，不能满足需要及时将页刷新到磁盘

·page cleaner线程数量不够

·buffer pool本身太小，从而导致free list和lru list都小

 

常用的解决方法有：

1.增加buffer pool

2.调优、升级IO子系统

3.优化sql，避免全表/全索引扫描