MySQL Bugs: #81899: InnoDB: page_cleaner: 1000ms intended loop took 27876ms. The settings mig

Bug #81899	InnoDB: page_cleaner: 1000ms intended loop took 27876ms. The settings mig
Submitted:	17 Jun 2016 6:54	Modified:	22 Jun 2016 18:41
Reporter:	igchang choi	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.7.11	OS:	CentOS (6.5)
Assigned to:		CPU Architecture:	Any
Tags:	flush page option

Description:
hello 

I have questions

The sql query is slowing down the message comes out
"InnoDB: page_cleaner: 1000ms intended loop took 27876ms. The settings might not be optimal. (Flushed = 6 and evicted = 0, during the time.)"

H / W spec
2core CPU / 4G RAM / 100G HDD (no SSD)

How can I fix this problem ? plz help me 

And the my.cnf

datadir = / var / lib / mysql
socket = / var / lib / mysql / mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0

log-error = / var / log / mysqld.log
pid-file = / var / run / mysqld / mysqld.pid

# Recommended in standard MySQL setup
sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 64M
#table_open_cache = 2048
table_open_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

default-storage-engine = InnoDB
# You can set .._ buffer_pool_size up to 50 - 80%
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 30
innodb_thread_concurrency = 16
innodb_page_cleaners = 4
innodb_buffer_pool_instances = 4
innodb_purge_threads = 8

innodb_write_io_threads = 8
innodb_read_io_threads = 8

#innodb_fast_shutdown = 0
transaction-isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_neighbors = 0 # off

flush = 1
flush_time = 10
binlog_order_commits = 0

explicit_defaults_for_timestamp = 1

character_set_server = utf8
skip-character-set-client-handshake
collation_server = utf8_general_ci

skip-name-resolve
skip-host-cache

slow_query_log = 1
slow_query_log_file = / var / lib / mysql / mysql-slow.log
long_query_time = 2

max_connections = 100
wait_timeout = 16
expire_logs_days = 3

sql_mode = "NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"

validate-password-mixed-case-count = 0

# Max_allowed_packet = 256M
# Max_allowed_packet = 1060M

[Mysqldump]
quick
max_allowed_packet = 60M

[Mysql]
no-auto-rehash

How to repeat:
hello 

I have questions

The sql query is slowing down the message comes out
"InnoDB: page_cleaner: 1000ms intended loop took 27876ms. The settings might not be optimal. (Flushed = 6 and evicted = 0, during the time.)"

H / W spec
2core CPU / 4G RAM / 100G HDD (no SSD)

How can I fix this problem ? plz help me 

And the my.cnf

datadir = / var / lib / mysql
socket = / var / lib / mysql / mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0

log-error = / var / log / mysqld.log
pid-file = / var / run / mysqld / mysqld.pid

# Recommended in standard MySQL setup
sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 64M
#table_open_cache = 2048
table_open_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

default-storage-engine = InnoDB
# You can set .._ buffer_pool_size up to 50 - 80%
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 30
innodb_thread_concurrency = 16
innodb_page_cleaners = 4
innodb_buffer_pool_instances = 4
innodb_purge_threads = 8

innodb_write_io_threads = 8
innodb_read_io_threads = 8

#innodb_fast_shutdown = 0
transaction-isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_neighbors = 0 # off

flush = 1
flush_time = 10
binlog_order_commits = 0

explicit_defaults_for_timestamp = 1

character_set_server = utf8
skip-character-set-client-handshake
collation_server = utf8_general_ci

skip-name-resolve
skip-host-cache

slow_query_log = 1
slow_query_log_file = / var / lib / mysql / mysql-slow.log
long_query_time = 2

max_connections = 100
wait_timeout = 16
expire_logs_days = 3

sql_mode = "NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"

validate-password-mixed-case-count = 0

# Max_allowed_packet = 256M
# Max_allowed_packet = 1060M

[Mysqldump]
quick
max_allowed_packet = 60M

[Mysql]
no-auto-rehash

Hi!

In order to get rid of that message you need to better tune InnoDB SE.

This is a forum for reporting bugs with repeatable test cases. What you are asking for is a free support. This is not a free support forum. You can get free support in the MySQL groups on Facebook, Twitter and some other social networks.

I can only tell you that you can set number of specialized threads in InnoDB.

Actually, it turns out this is a duplicate of the bug # 76661.

Have same issue

And I really thinks in is not an ask for free support as this in produces by fresh mysql installed from oracle repo and then it produces this error mysql do not process requests and goes timeout. 

I have to roll back to 5.6 due to this issue 

PS innodb_buffer_pool_instances is 1
innodb_page_cleaners is 1 

2016-10-21T18:45:38.712305Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4452ms. The settings might not be
 optimal. (flushed=0 and evicted=0, during the time.)
2016-10-21T18:45:52.569978Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4386ms. The settings might not be
 optimal. (flushed=0 and evicted=0, during the time.)
2016-10-21T18:47:52.403963Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7081ms. The settings might not be
 optimal. (flushed=0 and evicted=0, during the time.)

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;

Encountering the same issue on v5.7.18.
Tried setting innodb_buffer_pool_size to 80% of available RAM .. it didn't help. Then changed innodb_lru_scan_depth = 256. Didn't resolve this issue.