InnoDB: page_cleaner: 1000ms intended loop took

环境：windows+5.7.12

错误详情：

 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5258ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.)

此错误来自于..\storage\innobase\buf\buf0flu.cc,代码：

    if (curr_time > next_loop_time + 3000) {
                    if (warn_count == 0) {
                        ib::info() << "page_cleaner: 1000ms"
                            " intended loop took "
                            << 1000 + curr_time
                               - next_loop_time
                            << "ms. The settings might not"
                            " be optimal. (flushed="
                            << n_flushed_last
                            << " and evicted="
                            << n_evicted
                            << ", during the time.)";
                        if (warn_interval > 300) {
                            warn_interval = 600;
                        } else {
                            warn_interval *= 2;
                        }

可以看到实际经历的时间比原定的循环时间next_loop_time多4000多毫秒

另外 (flushed=200 and evicted=0, during the time.)对应n_flushed_last与n_evicted 变量，而这两个变量由n_flushed_list与n_flushed_lru赋值，函数pc_wait_finished(&n_flushed_lru, &n_flushed_list)调用

/**
Wait until all flush requests are finished.
@param n_flushed_lru    number of pages flushed from the end of the LRU list.
@param n_flushed_list    number of pages flushed from the end of the
            flush_list.
@return            true if all flush_list flushing batch were success. */

说明

n_flushed_lru  表示从lru 列表尾部刷新的页数

n_flushed_list  这个是从刷新列表中刷新的页数，也就是脏页数，也就是日志中flushed=200 的值

 

尝试解决：

1，出现这个问题有说调整innodb_page_cleaners为更大的值即可，最好保持跟innodb_buffer_pool_instances一致

2，调低innodb_lru_scan_depth=256，系统默认的1024大了点。

当然这里调整不一定消失，有些问题是越讨论越清晰在8.0版本已经去掉此提示，见Bug #76661 ：https://bugs.mysql.com/bug.php?id=76661

 

网上有人解释的有道理：

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

The InnoDB Buffer Pool 官网也涉及到一些：

https://dev.mysql.com/doc/refman/5.7/en/innodb-lru-background-flushing.html
---------------------
作者：朝闻道-夕死可矣
来源：CSDN
原文：https://blog.csdn.net/jc_benben/article/details/82251891
版权声明：本文为博主原创文章，转载请附上博文链接！