HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

MySQL InnoDB page_cleaner settings might not be optimal

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
mightsettingsoptimalinnodbpage_cleanermysqlnot

Problem

Seeing this note in mysqld.log:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.)


There seems to be mention of something like this here:
MySQL instance stalling "doing SYNC index"

My question is: what action should be taken, if any, when this note is seen in the logs?

MySQL and OS versions:

mysql-community-server-5.7.9-1.el7.x86_64

centos-release-7-1.1503.el7.centos.2.8.x86_64

Running SHOW VARIABLES LIKE 'innodb%'; as suggested shows:

innodb_page_cleaners | 1

Solution

The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances

Check innodb_buffer_pool_instances with:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_instances'


You can only set innodb_page_cleaners as high as innodb_buffer_pool_instances. If you want innodb_page_cleaners=4 then you also need innodb_buffer_pool_instances=4.

Code Snippets

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_instances'

Context

StackExchange Database Administrators Q#121236, answer score: 14

Revisions (0)

No revisions yet.