MySql 5.7 : "InnoDB: page_cleaner: 1000ms intended loop took 5585ms. The settings might not be optimal."

MySQL & MariaDB Other MySQL® Questions
1

Hi,
I’m running MySql 5.7.42 on Ubuntu 18.04 on VM with 12 Core, 72GB Ram and recently I faced these note:

2024-01-23T14:02:15.886327Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4304ms. The settings might not be optimal. (flushed=1459 and evicted=0, during the time.)
2024-01-23T14:16:02.717341Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5906ms. The settings might not be optimal. (flushed=10036 and evicted=0, during the time.)
2024-01-23T14:23:11.305065Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4544ms. The settings might not be optimal. (flushed=10037 and evicted=0, during the time.)
2024-01-23T14:32:50.739433Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5888ms. The settings might not be optimal. (flushed=10015 and evicted=0, during the time.)

as far as i know this could depends on I/O and could be fixed tuning some parameters but I cannot understand where to start.

My conf:

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
key_buffer_size		= 1400M
max_allowed_packet	= 64M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

log_error = /var/log/mysql/error.log
slow_query_log		= 1
slow_query_log_file	= /var/log/mysql/mysql-slow.log
long_query_time = 1
expire_logs_days	= 10
max_binlog_size   = 100M

innodb_buffer_pool_size = 48G 
innodb_log_file_size = 4000M
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_buffer_pool_instances=45
query_cache_limit       = 32M
query_cache_size        = 0
query_cache_type        = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_lru_scan_depth=256
tmp_table_size = 64M
max_heap_table_size = 64M
innodb_autoinc_lock_mode =2
sync_binlog=0 
bulk_insert_buffer_size=512M
join_buffer_size = 3M 
sort_buffer_size = 512K
table_open_cache=4000
innodb_open_files=4000
max_connections=151
wait_timeout=7200
interactive_timeout=7200

There’s too many dirty pages to flush so I’m thinking about these tuning options:

  • increase innodb_read_io_threads = 16, innodb_write_io_threads = 16 to 32?
  • increase innodb_log_buffer_size = 512M to 1GB ?
  • increase innodb_page_cleaners=4, innodb_purge_threads=4 to 8?
  • decrease nnodb_io_capacity = 10000, innodb_io_capacity_max = 20000 halving their value?

any advice will be greatly appreciated
Thanks

2

Hello @Domenico_Sgarbossa,

You should put innodb_io_capacity back to default of 200. Check out this blog post on the subject

innodb_buffer_pool_instances=45

That’s a crazy number of pool instances. You’re BP is only 48G. Reduce number of instances down to 8.

I see you are still on 5.7. I hope you are making efforts to upgrade to 8 ASAP since 5.7 is dead.

innodb_log_buffer_size = 512M

Also, crazy large. Do you frequently update large BLOB columns? If you don’t, set this back to the default 16MB.

innodb_(read|write)_io_threads

You only have 12 cores. Put these back to defaults.

Check your monitoring solution for CPU await and disk queuing. You might just be pushing your disks harder than they can handle. If that is the case, you might need to introduce innodb_max_purge_lag to create pauses in DMLs to ease the write activity.

3

Thanks @matthewb!
Obviously I’m planning to upgrade to Mysql 8 as soon as possible, unfortunately it takes a lot of work because the webapp is very large.
Meanwhile I faced this problem so I urgently need a band aid until I finish the app conversion.

My conf values mostly has been set accordly some well known tunign scripts (eg. mysqltuner.pl).
I’ve read many posts where expert dba’s blames those kind of scripts: to be honest in the last 4 years I haven’t faced no problems following their advices but I understand that probably I was only lucky.