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 😉
In the 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
Settings:
innodb_page_cleaners
is currently 4,innodb_buffer_pool_instances
is 25 !.innodb_buffer_pool_chunk_size
is 134217728innodb_buffer_pool_size
is 26843545600
Questions:
- Should I just set the
page cleaners
to 25 as suggested in the other answer. - Should I try 1) first, see if it makes a difference, then lower the
innodb_lru_scan_depth
if 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 cleaners
to the same as thepool 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?
Thanks!