2

I have noticed that when the page cleaner is triggered in my MySQL 8 database, it causes a temporary increase in the number of active connections. This can result in increased wait time for new connections to the database and affect overall performance.

Is this normal behavior for the page cleaner in MySQL 8? If so, why does it cause an increase in active connections? And, is there a way to minimize the impact of the page cleaner on active connections?

Any insight or guidance on this matter would be greatly appreciated.

1
  • 1
    Please list the specific metrics you observed, and there value before and after the change.
    – Rick James
    Feb 1, 2023 at 17:12

1 Answer 1

2

This depends on what the OS has configured

In the MySQL 8.0 Documentation on innodb_page_cleaners, it says in part

If your workload is write-IO bound when flushing dirty pages from buffer pool instances to data files, and if your system hardware has available capacity, increasing the number of page cleaner threads may help improve write-IO throughput.

Multithreaded page cleaner support extends to shutdown and recovery phases.

The setpriority() system call is used on Linux platforms where it is supported, and where the mysqld execution user is authorized to give page_cleaner threads priority over other MySQL and InnoDB threads to help page flushing keep pace with the current workload. setpriority() support is indicated by this InnoDB startup message:

[Note] InnoDB: If the mysqld execution user is authorized, page cleaner
thread priority can be changed. See the man page of setpriority().

If the Google CloudSQL Instance has it, pager cleaners can rise on demand based on I/O.

If your connections are rising due to increased writes, perhaps your instance class may be a bit too small to address I/O. Increasing threads on a small instance woudl exacerbate things. You may need to increase innodb_write_io_threads to get dirty pages committed to disk a little faster. Please get a bigger instance class (Open your wallet ...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.