patternsqlModerate
MySQL InnoDB page_cleaner settings might not be optimal
Viewed 0 times
mightsettingsoptimalinnodbpage_cleanermysqlnot
Problem
Seeing this note in mysqld.log:
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:
[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 | 1Solution
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:
You can only set
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.