Not the answer you need?
Register and ask your own question!

Persistent but intermittent page_cleaner warnings in log

jcrispjcrisp EntrantInactive User Role Beginner
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:

https://gist.github.com/jcrisp/c2465...ed913deb714e04

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

Comments

  • vadimtkvadimtk Contributor Percona Staff Role
    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
  • jcrispjcrisp Entrant Inactive User Role Beginner
    Thank you very much! I'll give these a go and keep you posted on the results. James
  • jcrispjcrisp Entrant Inactive User Role Beginner
    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
  • jcrispjcrisp Entrant Inactive User Role Beginner
    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
  • jcrispjcrisp Entrant Inactive User Role Beginner
    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.)
  • jcrispjcrisp Entrant Inactive User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.