如何解决 mysql 警告：“InnoDB：page_cleaner：1000ms 预期循环花了 XXX ms。设置可能不是最佳的”？

How to solve mysql warning: "InnoDB: page_cleaner: 1000ms intended loop took XXX ms. The settings might not be optimal "?

提问by satch_boogie

I ran a mysql import mysql dummyctrad < dumpfile.sql on server and its taking too long to complete. The dump file is about 5G. The server is a Centos 6, memory=16G and 8core processors, mysql v 5.7 x64-

我在服务器上运行了一个 mysql import mysql dummyctrad < dumpfile.sql 并且它需要很长时间才能完成。转储文件大约5G。服务器是 Centos 6，内存=16G 和 8core 处理器，mysql v 5.7 x64-

Are these normal messages/status "waiting for table flush" and the message InnoDB: page_cleaner: 1000ms intended loop took 4013ms. The settings might not be optimal

这些正常的消息/状态“正在等待表刷新”和消息 InnoDB: page_cleaner: 1000ms intended loop took 4013ms. The settings might not be optimal

mysql log contents

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)

Update-1

更新 1

mysqlforum,innodb_lru_scan_depth

mysqlforum, innodb_lru_scan_depth

changing innodb_lru_scan_depth value to 256 have improved the insert queries execution time + no warning message in log, the default was innodb_lru_scan_depth=1024;

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

SET GLOBAL innodb_lru_scan_depth=256;

SET GLOBAL innodb_lru_scan_depth=256;

回答by Bill Karwin

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

InnoDB: page_cleaner: 1000ms 预期循环用了 4013ms。设置可能不是最佳的。（在此期间，flushed=1438 和 evicted=0。）

The problem is typical of a MySQL instance where you have a high rate of changes to the database. By running your 5GB import, you're creating dirty pages rapidly. As dirty pages are created, the page cleaner thread is responsible for copying dirty pages from memory to disk.

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

In your case, I assume you don't do 5GB imports all the time. So this is an exceptionally high rate of data load, and it's temporary. You can probably disregard the warnings, because InnoDB will gradually catch up.

在您的情况下，我假设您不会一直进行 5GB 导入。所以这是一个异常高的数据加载率，而且是暂时的。您可能可以忽略警告，因为 InnoDB 会逐渐赶上。

---

---

Here's a detailed explanation of the internals leading to this warning.

这是导致此警告的内部结构的详细说明。

Once per second, the page cleaner scans the buffer pool for dirty pages to flush from the buffer pool to disk. The warning you saw shows that it has lots of dirty pages to flush, and it takes over 4 seconds to flush a batch of them to disk, when it should complete that work in under 1 second. In other words, it's biting off more than it can chew.

每秒一次，页面清理器扫描缓冲池中的脏页以从缓冲池刷新到磁盘。您看到的警告表明它有很多脏页要刷新，将一批脏页刷新到磁盘需要 4 秒多的时间，而当它应该在 1 秒内完成该工作时。换句话说，它咬掉的比它咀嚼的多。

You adjusted this by reducing innodb_lru_scan_depthfrom 1024 to 256. This reduces how far into the buffer pool the page cleaner thread searches for dirty pages during its once-per-second cycle. You're asking it to take smaller bites.

您通过将其innodb_lru_scan_depth从 1024减少到 256进行了调整。这减少了页面清理器线程在其每秒一次的循环中搜索脏页面的缓冲池中的距离。你要求它吃小口。

Note that if you have many buffer pool instances, it'll cause flushing to do more work. It bites off innodb_lru_scan_depthamount of work for each buffer pool instance. So you might have inadvertently caused this bottleneck by increasing the number of buffer pools without decreasing the scan depth.

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

The documentation for innodb_lru_scan_depthsays "A setting smaller than the default is generally suitable for most workloads." It sounds like they gave this option a value that's too high by default.

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

You can place a limit on the IOPS used by background flushing, with the innodb_io_capacityand innodb_io_capacity_maxoptions. The first option is a soft limit on the I/O throughput InnoDB will request. But this limit is flexible; if flushing is falling behind the rate of new dirty page creation, InnoDB will dynamically increase flushing rate beyond this limit. The second option defines a stricter limit on how far InnoDB might increase the flushing rate.

您可以使用innodb_io_capacity和innodb_io_capacity_max选项限制后台刷新使用的 IOPS 。第一个选项是对 InnoDB 将请求的 I/O 吞吐量的软限制。但是这个限制是灵活的；如果刷新速度落后于新脏页创建的速度，InnoDB 将动态增加刷新速度超过此限制。第二个选项定义了 InnoDB 可能增加刷新率的更严格限制。

If the rate of flushing can keep up with the average rate of creating new dirty pages, then you'll be okay. But if you consistently create dirty pages faster than they can be flushed, eventually your buffer pool will fill up with dirty pages, until the dirty pages exceeds innodb_max_dirty_page_pctof the buffer pool. At this point, the flushing rate will automatically increase, and may again cause the page_cleaner to send warnings.

如果刷新速度可以跟上创建新脏页的平均速度，那么您就可以了。但是，如果您始终以比刷新的速度更快地创建脏页，最终您的缓冲池将被脏页填满，直到脏页超出innodb_max_dirty_page_pct缓冲池。此时，刷新率会自动增加，并可能再次导致page_cleaner发送警告。

Another solution would be to put MySQL on a server with faster disks. You need an I/O system that can handle the throughput demanded by your page flushing.

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

If you see this warning all the time under average traffic, you might be trying to do too many write queries on this MySQL server. It might be time to scale out, and split the writes over multiple MySQL instances, each with their own disk system.

如果您在平均流量下一直看到此警告，则您可能试图在此 MySQL 服务器上执行过多的写入查询。可能是向外扩展并将写入拆分到多个 MySQL 实例的时候了，每个实例都有自己的磁盘系统。

Read more about the page cleaner:

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

• Introducing page_cleaner thread in InnoDB(archived copy)
• MySQL-5.7 improves DML oriented workloads

• 在 InnoDB 中引入 page_cleaner 线程（存档副本）
• MySQL-5.7 改进了面向 DML 的工作负载

回答by Vilq

The bottleneck is saving data to HDD. Whatever HDD you have: SSD, normal one, NVMe etc.

瓶颈是将数据保存到 HDD。无论您拥有何种硬盘：SSD、普通硬盘、NVMe 等。

Note, that this solution applies mostly to InnoDB

请注意，此解决方案主要适用于 InnoDB

I had the same problem, I've applied few solutions.

我有同样的问题，我已经应用了几个解决方案。

1st: checking what's wrong

第一：检查有什么问题

atop -dwill show you disk usage. If disk is 'busy', then try to stop all queries to database (but don't stop mysql server service!)

atop -d将显示您的磁盘使用情况。如果磁盘“忙”，则尝试停止对数据库的所有查询（但不要停止 mysql 服务器服务！）

To monitor how many queries you do have, use mytop, innotop or equivalent.

要监控您有多少查询，请使用 mytop、innotop 或等效项。

If you have 0 queries, but disk usage is STILL next to 100% from a few seconds / few minutes, then it means, that mysql server is trying to flush dirty pages / do some cleaning as mentioned before (great post of Bill Karwin).

如果您有 0 个查询，但磁盘使用率在几秒/几分钟后仍接近 100%，那么这意味着 mysql 服务器正在尝试刷新脏页/如前所述进行一些清理（Bill Karwin 的精彩帖子） .

THEN you can try to apply such solutions:

那么你可以尝试应用这样的解决方案：

2nd: harware optimisation

第二：硬件优化

If your array is not in RAID 1+0 consider to double speed of saving data using such kind of solution. Try to extend your HDD cotroller possibilities with writing data. Try to use SSD or faster HDD. Applying this soultion depends on your harware and budget possibilities and may vary.

如果您的阵列不在 RAID 1+0 中，请考虑使用此类解决方案将数据保存速度提高一倍。尝试通过写入数据来扩展您的 HDD 控制器的可能性。尝试使用 SSD 或更快的 HDD。应用此解决方案取决于您的硬件和预算可能性，并且可能会有所不同。

3nd: software tuning

第三：软件调优

If harware cotroller is working fine, but you want to extend speed of saving data you can set up in mysql config file:

如果硬件控制器工作正常，但你想提高保存数据的速度，你可以在 mysql 配置文件中设置：

3.1.

3.1.

innodb_flush_log_at_trx_commit = 2-> if you/re using innodb tables It works form my experisnce the best with one table per file: innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 2-> 如果您/正在使用 innodb 表它可以从我的经验中得到最好的，每个文件一个表： innodb_file_per_table = 1

3.2.

3.2.

continuing with InnoDB: innodb_flush_method = O_DIRECT innodb_doublewrite = 0 innodb_support_xa = 0 innodb_checksums = 0

继续 InnoDB： innodb_flush_method = O_DIRECT innodb_doublewrite = 0 innodb_support_xa = 0 innodb_checksums = 0

Lines above are in general reducing amount of data needed to be saved in HDD, so performance is greater.

上面几行通常减少了需要保存在 HDD 中的数据量，因此性能更高。

3.3

3.3

general_log = 0 slow_query_log = 0

general_log = 0 slow_query_log = 0

Lines above disable saving logs, of course it is yet another amount of data to be saved on HDD

上面几行禁用保存日志，当然这是另一个要保存在硬盘上的数据

3.4 check again what's happening usit e.g. tail -f /var/log/mysql/error.log

3.4 再次检查发生了什么，例如 tail -f /var/log/mysql/error.log

4th: general notes

第四：一般注意事项

General notes: This was tested under MySQL 5.6 AND 5.7.22 OS: Debian 9 RAID: 1 + 0 SSD drives Database: InnoDB tables innodb_buffer_pool_size = 120G innodb_buffer_pool_instances = 8 innodb_read_io_threads = 64 innodb_write_io_threads = 64Total amount of RAM in server: 200GB

一般说明：这是在 MySQL 5.6 和 5.7.22 下测试的操作系统：Debian 9 RAID：1 + 0 SSD 驱动器数据库：InnoDB 表 innodb_buffer_pool_size = 120G innodb_buffer_pool_instances = 8 innodb_read_io_threads = 64 innodb_write_io_threads = 64服务器中的 RAM 总量：200GB

After doing that you may observe higher CPU usage; that's normal, because writing data is more faster, so then CPU will work harder.

这样做之后，您可能会观察到更高的 CPU 使用率；这是正常的，因为写入数据更快，所以 CPU 会更努力地工作。

If you're doing that using my.cnf of course don't forget to restart MySQL server.

如果您使用 my.cnf 这样做，当然不要忘记重新启动 MySQL 服务器。

5th: supplement

第五：补充

Beeing intrigued I did this quirk with SET GLOBAL innodb_lru_scan_depth=256;mentioned above.

我很感兴趣，我做了SET GLOBAL innodb_lru_scan_depth=256;上面提到的这个怪癖 。

Working with big tables I've seen no change in performance.

使用大表时，我发现性能没有变化。

After corrections above I didn't get rid of warnings, however whole system is working significantly faster. Everything above is just an experimentation, but I have measured results, it helped me a little, so hopefully it may be useful for others.

经过上述更正后，我没有消除警告，但是整个系统的运行速度明显加快。上面的一切都只是一个实验，但我已经测量了结果，它对我有一点帮助，所以希望它对其他人有用。