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;
评论区