I am having similar issues to this question but I’m trying to understand how to tune the database correctly (rather than just lowering settings and hoping 😉
error log I have these entries (often days or weeks apart)
2020-12-19T11:54:53.640527Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12204ms. The settings might not be optimal. (flushed=27 and evicted=0, during the time.) 2020-12-19T23:14:04.901370Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6788ms. The settings might not be optimal. (flushed=10 and evicted=0, during the time.)
And corresponding entries in the mysql-slow log! down to the second. What I find weird is that I thought the cleaner was supposed to be some kind of background process… why does it have any impact on the queries themselves?
mysqltuner is happy, the db has 32 gigs ram and some relevant settings are
innodb_page_cleanersis currently 4,
innodb_buffer_pool_instancesis 25 !.
- Should I just set the
page cleanersto 25 as suggested in the other answer.
- Should I try 1) first, see if it makes a difference, then lower the
innodb_lru_scan_depthif I don’t see an improvement?
- Why doesn’t MYSQL just default the page cleaners to be the same as the number of instances if it can have such a drastic affect? t suggests setting the
page cleanersto the same as the
pool instances. Why doesn’t MYSQL just default to that if it’s so sensible?
- Is it expected to see the cleaner have an impact in the slow-query log?