Persistent but intermittent page_cleaner warnings in log

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hi!

I have a new linode with 6 CPU cores. 16gig ram, SSD raid running Percona server 5.7.22-22 and Ubuntu 16.04. It is a slave to a Percona 5.6 server. Ever since building it, it has log warnings like the below, one or twice a day:

2018-07-28T12:42:37.554905Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9873ms. The settings might not be optimal. (flushed=78, during the time.)
2018-07-30T01:09:12.360683Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10967ms. The settings might not be optimal. (flushed=8, during the time.)
2018-07-30T11:18:56.042427Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 13259ms. The settings might not be optimal. (flushed=106, during the time.)
2018-07-30T22:29:27.797124Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5557ms. The settings might not be optimal. (flushed=46, during the time.)
2018-07-31T14:37:31.821713Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 30181ms. The settings might not be optimal. (flushed=5, during the time.)
2018-07-31T21:53:04.710618Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8186ms. The settings might not be optimal. (flushed=21, during the time.)
2018-08-02T00:23:22.393201Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4223ms. The settings might not be optimal. (flushed=132, during the time.)

I monitor the server with datadog and new relic, and all aspects of CPU and IO are quiet at these times. At other much busier times, there are no warnings.

I’ve scoured forums to try and find an answer and been slowly tweaking down the innodb_lru_scan_depth and up the innodb_log_file_size. This doesn’t seem to have made much difference. Current settings in my cnf file are:

innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_buffer_pool_size = 7G
innodb_page_cleaners = 6 # lower of CPU count or buffer pools if it cannot keep up
innodb_lru_scan_depth = 256

Here are the full InnoDB settings:

[URL=“page_cleaner: 1000ms intended loop took... · GitHub”]https://gist.github.com/jcrisp/c2465...ed913deb714e04[/URL]

Otherwise the database seems to working fine.

I’m hoping you might be able to give me some pointers to help resolve this, or let me know if it is not important?

Thanks very much!
James

2

Not sure what kind of SSD you have, but I would try still increase innodb_log_file_size to say 4G.
Also I would try innodb_io_capacity=2500 and innodb_io_capacity_max=4500

3

Thank you very much! I’ll give these a go and keep you posted on the results. James

4

Hi Vadimtk! I tried with the increased innodb_io_capacity and innodb_io_capacity_max and log size of 1G. I still got the warning again after 2 days (but maybe less frequent?). I’ve increased the innodb_log_file_size to the full 4G now, and await to see if I get the message again. Thanks James

5

Hi again!

No luck, I’ve got another warning despite innodb_log_file_size = 4G and innodb_io_capacity=2500 and innodb_io_capacity_max=4500.

2018-08-06T10:12:38.116672Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7950ms. The settings might not be optimal. (flushed=81, during the time.)

The setting changes don’t seem to have made much difference to the time it took to run. The machine is idle with low disk latency when the warning happens.

What else could we try?

Thanks
James

6

Yes still getting about one every day or two:
2018-08-09T13:25:19.265377+10:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 17914ms. The settings might not be optimal. (flushed=30, during the time.)

7

Hi Vadimtk! I think I managed to pin down a disk latency problem after all, with the VPS provider at the time of the latest page_clearner error. It was quite short, but the page cleaner is every second. I’ve switched to a new host machine with same provider, and hoping this will solve it. I wanted to check though, are those settings you recommended good ones to stay with? Or should I slowly try and decrease the innodb_log_file_size?
Thanks
James