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

[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迭代,就会将警告信息写入日志。下面是对应的源码块:

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,避免全表/全索引扫描