InnoDB Difficult to find free blocks in the buffer pool

场景

相关信息

16G 8C服务器下搭建mysql5.7。
大数据量下聚合查询几个小时未跑通，导致mysql崩溃，无法连接，无法停止。

排查过程

查看日志

[root@test log]# tail -f 100 /var/log/mysqld.log
2020-09-08T09:00:46.639970Z 244 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (1186065 search iterations)! 1186065 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. 99134549 OS file reads, 3547773 OS file writes, 687230 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.
2020-09-08T09:00:46.641107Z 248 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (1166575 search iterations)! 1166575 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. 99134549 OS file reads, 3547773 OS file writes, 687230 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

执行top找到pid，kill进程。

修改my.cnf，修改/添加以下参数：

• innodb_buffer_pool_size = 12G # 缓冲池大小，一般为总内存的80%
• innodb_buffer_pool_instances = 6 # 缓冲池实例，范围1~64

配置完后保存，重启mysql服务器即可。

---

此外，在执行sql时日志中监控到了如下警告:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 5623ms. 
The settings might not be optimal. (flushed=1899 and evicted=0, during the time.)

解决方法：

SET GLOBAL innodb_lru_scan_depth=256;