mysql - 如何解决mysql警告: "InnoDB: page_cleaner: 1000ms intended loop took XXX ms. The settings might not be optimal "?

Question

我在服务器上运行了一个 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)

进程列表:

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)

Update-1

mysqlforum , innodb_lru_scan_depth

将 innodb_lru_scan_depth 值更改为 256 提高了插入查询的执行时间 + 日志中没有警告消息，默认值为 innodb_lru_scan_depth=1024；

SET GLOBAL innodb_lru_scan_depth=256;

Answer 1

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 会逐渐 catch 来。

---

以下是导致此警告的内部细节的详细说明。

页面清理器每秒扫描一次缓冲池中的脏页以从缓冲池刷新到磁盘。您看到的警告表明它有很多脏页要刷新，将一批脏页刷新到磁盘需要超过 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