2020-07-09T00:07:27.834631+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4852ms. The settings might not be optimal. (flushed=173 and evicted=0, during the time.)
2020-07-09T00:07:58.133012+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4295ms. The settings might not be optimal. (flushed=136 and evicted=0, during the time.)
2020-07-09T03:31:25.182819+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6812ms. The settings might not be optimal. (flushed=21 and evicted=0, during the time.)
2020-07-09T05:25:45.498145+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5605ms. The settings might not be optimal. (flushed=40 and evicted=0, during the time.)
2020-07-09T05:26:06.408845+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9040ms. The settings might not be optimal. (flushed=78 and evicted=0, during the time.)
2020-07-09T05:26:41.457475+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5365ms. The settings might not be optimal. (flushed=13 and evicted=0, during the time.)
2020-07-09T05:27:45.798837+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5582ms. The settings might not be optimal. (flushed=32 and evicted=0, during the time.)
2020-07-09T05:28:14.271330+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5376ms. The settings might not be optimal. (flushed=55 and evicted=0, during the time.)
2020-07-09T06:00:24.665758+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 14593ms. The settings might not be optimal. (flushed=69 and evicted=0, during the time.)
2020-07-09T06:01:16.722771+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5048ms. The settings might not be optimal. (flushed=89 and evicted=0, during the time.)
2020-07-09T06:17:44.475699+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 11532ms. The settings might not be optimal. (flushed=19 and evicted=0, during the time.)
2020-07-09T06:19:23.584295+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5097ms. The settings might not be optimal. (flushed=34 and evicted=0, during the time.)
2020-07-09T08:14:22.624772+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4279ms. The settings might not be optimal. (flushed=69 and evicted=0, during the time.)
2020-07-09T18:11:16.618121+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 31470ms. The settings might not be optimal. (flushed=148 and evicted=0, during the time.)
2020-07-09T21:29:01.659984+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4118ms. The settings might not be optimal. (flushed=161 and evicted=0, during the time.)
2020-07-09T22:15:32.269919+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6288ms. The settings might not be optimal. (flushed=27 and evicted=0, during the time.)
2020-07-09T23:45:10.615654+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9863ms. The settings might not be optimal. (flushed=130 and evicted=0, during the time.)
mysql版本:mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper
变量配置(使用默认配置):
innodb_io_capacity=200
innodb_lru_scan_depth=1024
innodb_max_dirty_pages_pct=75
innodb_max_dirty_pages_pct_lwm=0
innodb_buffer_pool_instances=8
innodb_page_cleaners=4
自从使用了5.7版本,mysql log就一直出现这个问题,也是很多人的噩梦,MySQL bug官网从2015年讨论到2019年还是一样,说是8.0版本已经取消这个提示了。 https://bugs.mysql.com/bug.php?id=76661
其实从日志出现的时间点看,大部分是在业务空闲时间点,对业务影响较小,不然是会出现SQL执行时间过长。
- 2020-7-10 无序列表项1 尝试修改
innodb_lru_scan_depth=128
发现出现的概率大概降到之前的1/3。 还在继续尝试调整innodb_page_cleaners
- 2020-7-16
innodb_lru_scan_depth=256
慢日志基本不出现,page_cleaner还是会出现。
Comments