如何解决mysql警告:"InnoDB:page_cleaner:1000毫秒的预期循环用了XXX毫秒.设置可能不是最佳的? [英] How to solve mysql warning: "InnoDB: page_cleaner: 1000ms intended loop took XXX ms. The settings might not be optimal "?

问题描述

我运行了mysql导入mysql dummyctrad<服务器上的dumpfile.sql及其完成所需的时间过长.转储文件约为5G.该服务器是Centos 6，memory = 16G和8core处理器，mysql v 5.7 x64-

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-

这些正常消息/状态是否为等待表刷新"和消息InnoDB: page_cleaner: 1000ms intended loop took 4013ms. The settings might not be optimal

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

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值更改为256可改善插入查询的执行时间+日志中没有警告消息，默认值为innodb_lru_scan_depth = 1024;

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;

SET GLOBAL innodb_lru_scan_depth=256;

推荐答案

InnoDB:page_cleaner:1000毫秒的预期循环耗时4013毫秒.设置可能不是最佳的. (在这段时间内刷新了= 1438，退出了= 0).

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

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

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.

就您而言，我假设您并非一直都导入5GB.因此，这是一个非常高的数据加载速率，并且是暂时的.您可能会忽略警告，因为InnoDB会逐渐赶上.

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.

这是导致此警告的内部原因的详细说明.

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

每秒，页面清理程序将扫描缓冲池中的脏页，以将其从缓冲池刷新到磁盘.您看到的警告表明它有很多脏页要刷新，并且需要花费4秒钟以上的时间将一批脏页刷新到磁盘上，而这应该在不到1秒的时间内完成.换句话说，它所咬的东西超过了它的咀嚼能力.

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.

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

You adjusted this by reducing innodb_lru_scan_depth from 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工作量.因此，您可能无意中通过增加缓冲池的数量而不减小扫描深度而导致了该瓶颈.

Note that if you have many buffer pool instances, it'll cause flushing to do more work. It bites off innodb_lru_scan_depth amount 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_depth says "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_io_capacity和innodb_io_capacity_max选项来限制后台冲洗使用的IOPS.第一个选项是InnoDB将请求的I/O吞吐量的软限制.但是这个限制是灵活的.如果刷新低于新创建的脏页的速度，则InnoDB将动态增加刷新率，使其超过此限制.第二个选项定义了一个更严格的限制，即InnoDB可以提高刷新率的程度.

You can place a limit on the IOPS used by background flushing, with the innodb_io_capacity and innodb_io_capacity_max options. 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_max_dirty_page_pct为止.此时，刷新率将自动增加，并可能再次导致page_cleaner发送警告.

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_pct of the buffer pool. At this point, the flushing rate will automatically increase, and may again cause the page_cleaner to send warnings.

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

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服务器上执行太多写查询.可能是时候进行扩展，并在多个MySQL实例(每个实例都有自己的磁盘系统)上拆分写操作了.

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.

了解有关页面清洁器的更多信息:

Read more about the page cleaner:

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

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

这篇关于如何解决mysql警告:"InnoDB:page_cleaner:1000毫秒的预期循环用了XXX毫秒.设置可能不是最佳的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！