[MDEV-13670] [Note] InnoDB: page_cleaner: 1000ms intended loop took XXXXms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.) Created: 2017-08-29  Updated: 2020-10-15  Resolved: 2018-06-19

Status: Closed
Project: MariaDB Server
Component/s: Server, Storage Engine - InnoDB
Affects Version/s: 10.2.8
Fix Version/s: 10.2.12, 10.3.3

Type: Bug Priority: Minor
Reporter: Stuart Slater Assignee: Marko Mäkelä
Resolution: Fixed Votes: 1
Labels: upstream
Environment:

Windows 2008 R1 32bit

Issue Links:
Duplicate
is duplicated by MDEV-13949 Improve help when reporting page clea... Closed
Relates
relates to MDEV-16526 Overhaul the InnoDB page flushing Closed
relates to MDEV-23399 10.5 performance regression with IO-b... Closed
relates to MDEV-14550 Error log flood : "InnoDB: page_clea... Closed

 Description   

Hi,
I migrated from MySQL 5.6.36 to MariaDB 10.2.8 32bit on Windows 2008 R1.
I have started to see the following in our production environment.

2017-08-29 10:45:24 4432 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4197ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

I have not made any changes to the configuration file (my.ini); I was wondering do I need to

the settings are:-

MariaDB [(none)]> show global variables like 'innodb_buffer%';
 
+-------------------------------------+----------------+
 
| Variable_name                       | Value          |
 
+-------------------------------------+----------------+
 
| innodb_buffer_pool_chunk_size       | 134217728      |
 
| innodb_buffer_pool_dump_at_shutdown | ON             |
 
| innodb_buffer_pool_dump_now         | OFF            |
 
| innodb_buffer_pool_dump_pct         | 25             |
 
| innodb_buffer_pool_filename         | ib_buffer_pool |
 
| innodb_buffer_pool_instances        | 1              |
 
| innodb_buffer_pool_load_abort       | OFF            |
 
| innodb_buffer_pool_load_at_startup  | ON             |
 
| innodb_buffer_pool_load_now         | OFF            |
 
| innodb_buffer_pool_populate         | OFF            |
 
| innodb_buffer_pool_size             | 268435456      |
 
+-------------------------------------+----------------+
 
11 rows in set (0.00 sec)

Do I need to increase innodb_buffer_pool_instances, or anything else to stop this latency which I never saw logged with MySQL 5.6.36?

many thanks for your advice and guidance.

 Comments   
Comment by Marko Mäkelä [ 2017-08-29 ]

This change originated from MySQL 5.7, which MariaDB’s InnoDB implementation is based on.
One of the first reports of this is Bug #75846 page_cleaner thread configuration warning spams error log on laptops,
filed one month after the message was introduced in MySQL 5.7.6.

The suggestion to omit this message when both reported counts are 0 might make sense.
The reporter also mentioned a related request: Bug #71662 InnoDB background threads should be configurable to reduce power consumption.

Comment by Elena Stepanova [ 2017-09-19 ]

Setting to Confirmed based on the comment above.

Comment by Michael Widenius [ 2017-12-05 ]

Marko wants to at some point understand exactly why we get this error and what we can do to
avoid warnings that the user can't do anything about.

In the mean time, we will change so that this warning is only given if log_warnings is 3 or above.

Comment by Michael Widenius [ 2017-12-07 ]

Marko has now committed in 10.2 tree a fix that we don't write the page_cleaner information if log_warnings is 2 or less

Comment by Marko Mäkelä [ 2018-06-19 ]

The message was hidden at the default log_warnings level (2 or less). The actual problem will be addressed in MDEV-16526.

Comment by Marko Mäkelä [ 2020-09-15 ]

Until MDEV-23399, the ‘flush_list flushing batch’ would actually wait for buffer pool page latches, and therefore a single batch can take an arbitrarily long time.

Generated at Thu Feb 08 08:07:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.