如何解决mysql警告：“InnoDB：page_cleaner：1000ms意图循环花了XXX毫秒.设置可能不是最佳的“？

我运行了一个mysql import mysql dummyctrad<服务器上的dumpfile.sql,它需要很长时间才能完成.转储文件大约是5G.服务器是Centos 6,内存= 16G和8核处理器,mysql v 5.7 x64-
这些正常的消息/状态“等待表刷新”和消息InnoDB：page_cleaner：1000ms意图循环花了4013ms.设置可能不是最佳的
mysql日志内容

2016-12-13T10:51:39.909382Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4013ms. The settings might not be optimal. (flushed=1438 and evicted=0, during the time.)
2016-12-13T10:53:01.170388Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4055ms. The settings might not be optimal. (flushed=1412 and evicted=0, during the time.)
2016-12-13T11:07:11.728812Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4008ms. The settings might not be optimal. (flushed=1414 and evicted=0, during the time.)
2016-12-13T11:39:54.257618Z 3274915 [Note] Aborted connection 3274915 to db: 'dummyctrad' user: 'root' host: 'localhost' (Got an error writing communication packets)


PROCESSLIST：

mysql> show processlist \G;
*************************** 1. row ***************************
Id: 3273081
User: root
Host: localhost
db: dummyctrad
Command: Field List
Time: 7580
State: Waiting for table flush
Info:
*************************** 2. row ***************************
Id: 3274915
User: root
Host: localhost
db: dummyctrad
Command: Query
Time: 2
State: update
Info: INSERT INTO `radacct` VALUES (351318325,'kxid ge:7186','abcxyz5976c','user100
*************************** 3. row ***************************
Id: 3291591
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 4. row ***************************
Id: 3291657
User: remoteuser
Host: portal.example.com:32800
db: ctradius
Command: Sleep
Time: 2
State:
Info: NULL
4 rows in set (0.00 sec)


更新1

mysqlforum,innodb_lru_scan_depth

将innodb_lru_scan_depth值更改为256改进了插入查询执行时间日志中没有警告消息,默认为innodb_lru_scan_depth = 1024;

SET GLOBAL innodb_lru_scan_depth = 256;

解决方法:

InnoDB: page_cleaner: 1000ms intended loop took 4013ms. The settings might not be optimal. (flushed=1438 and evicted=0, during the time.)

问题是典型的MySQL实例,您对数据库的更改率很高.通过运行5GB导入,您可以快速创建脏页.在创建脏页时,页清理程序线程负责将脏页从内存复制到磁盘.

在你的情况下,我假设你不是一直做5GB的进口.所以这是一个异常高的数据加载速率,这是暂时的.您可以忽略警告,因为InnoDB会逐渐赶上.

以下是导致此警告的内部结构的详细说明.

每秒一次,页面清理程序扫描缓冲池以查找脏页以从缓冲池刷新到磁盘.您看到的警告显示它有大量要刷新的脏页,将一批它们刷新到磁盘需要4秒以上,而它应该在1秒内完成该工作.换句话说,它咬得比它能咀嚼的多.

您通过将innodb_lru_scan_depth从1024减少到256来调整此值.这减少了页面清除程序线程在每秒一次循环期间搜索脏页的缓冲池的距离.你要求它采取较小的咬伤.

请注意,如果您有许多缓冲池实例,则会导致刷新以执行更多工作.它会扼杀每个缓冲池实例的innodb_lru_scan_depth工作量.因此,您可能会在不降低扫描深度的情况下通过增加缓冲池的数量而无意中导致此瓶颈.

innodb_lru_scan_depth的文档说“小于默认值的设置通常适用于大多数工作负载.”听起来他们给这个选项一个默认值太高的值.

您可以使用innodb_io_capacity和innodb_io_capacity_max选项对背景刷新使用的IOPS进行限制.第一个选项是InnoDB将要求的I / O吞吐量的软限制.但这个限制是灵活的;如果刷新落后于新脏页面创建的速度,InnoDB将动态增加超出此限制的刷新率.第二个选项定义了InnoDB可以提高冲洗率的更严格限制.

如果刷新率可以跟上创建新脏页的平均速度,那么你就可以了.但是如果你一直创建脏页的速度比刷新的速度快,那么最终你的缓冲池将填满脏页,直到脏页超过缓冲池的innodb_max_dirty_page_pct.此时,刷新率将自动增加,并可能再次导致page_cleaner发送警告.

另一个解决方案是将MySQL放在具有更快磁盘的服务器上.您需要一个可以处理页面刷新所需吞吐量的I / O系统.

如果您在平均流量下始终看到此警告,则可能是在此MySQL服务器上尝试执行过多的写入查询.可能是时候向外扩展,并将写入分割为多个MySQL实例,每个实例都有自己的磁盘系统.

阅读有关页面清洁的更多信息：

> Introducing page_cleaner thread in InnoDB(存档副本)
> MySQL-5.7 improves DML oriented workloads