CODEWITHSUNDEEP
mysqllogginginnodb
White-Gandalf
White-Gandalf

Reputation: 69

What to do with "InnoDB: page_cleaner: 1000ms intended loop took 0ms"?

Today i stumbled over these lines in my mysql-error-log:

2016-05-30T04:50:45.522853Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 1000ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2016-05-30T04:50:47.523024Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 0ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
...

...the last line repeating at an interval of about 0.5 ms for about 1000 times.

What is a mysql user expected to do when THIS message shows up?

A search in the internet for "1000ms intended loop took 0ms" yields zero results. Google advocates a search without quotation marks, but that yields results of the exact opposite statement - where the "intended loop" - whatever this might be - took exceptional big amounts of time (thousands to tenth of thousands milliseconds).

Well, to be precise: The database i am talking about has one single table in innodb format, the rest is in myisam - for good reason (by a factor of more than a thousand more reading than writing). What i mostly wonder about is: Why the hack does this server complain about anything around innodb at all? Because the only one table in innodb format isn't even used yet - except some rare experiments i do for myself. But the latter could impossibly be the reason for the log entries, because i began my working day more than an hour later.

So the question remains at: What may be the reason at all for a mysql server to complain about a not even used innodb database? With a multitude of a thousand error messages?

Upvotes: 2

Views: 11695

Answers (1)

Brian Fenech
Brian Fenech

Reputation: 31

We experienced the same problem across various clients and found out that the problem was due to setting the value of innodb_lru_scan_depth from the default of 1024 to as low as 128. Although lowering the value reduces the time taken to process a transaction especially in write bound workloads I believe that setting the value too low would make the buffer pool to not able to keep up in clearing some of its buffers and buffer pool dirty pages.

In our case we have seen a drastic improvement by increasing the value from 128 to 256 but the right value depends on the hardware and the type of load. The trick is to find the right value between increasing OLTP performance and letting MySQL keep the buffer pool clean as not to have the page_cleaner needing to do a lot of work as stated by the above message:

"InnoDB: page_cleaner: 1000ms intended loop took *****ms"

The value cane be changed dynamically without restarting MySQL e.g.

SET GLOBAL innodb_lru_scan_depth=256;

Upvotes: 3

Related Questions