Archive Manager performance issues with InnoDB: page_cleaner messages in MYSQL.OUT file.
Article ID: 214304
Updated On:
Products
Issue/Introduction
We have noticed some performance issues with Archive Manager using Innodb. We have checked the $SPECROOT/mysql/bin/MYSQL.OUT file ($SPECROOT/mysql/data/<hostname>.err for Windows) and we can see the following messages:
2021-04-26T08:37:06.853871Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4894ms. The settings might not be optimal. (flushed=1828 and evicted=0, during the time.)
2021-04-26T08:37:20.298203Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6428ms. The settings might not be optimal. (flushed=1521 and evicted=0, during the time.)
2021-04-26T08:38:01.320036Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5830ms. The settings might not be optimal. (flushed=1654 and evicted=0, during the time.)
2021-04-26T08:46:30.730594Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4939ms. The settings might not be optimal. (flushed=1176 and evicted=0, during the time.)
2021-04-27T00:29:05.259747Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12370ms. The settings might not be optimal. (flushed=17 and evicted=0, during the time.)
2021-04-27T00:32:46.297579Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10001ms. The settings might not be optimal. (flushed=38 and evicted=0, during the time.)
**** Please Note that the same issue can be seen on the SRM server under the mysql logs and the same solution applies. *****
Environment
Release : Spectrum 10.x, Spectrum 20.x
Component : Spectrum Archive Manager
Cause
Once per second, the page cleaner scans the buffer pool for dirty pages to flush from the buffer pool to disk. The warning above shows that it has lots of dirty pages to flush, and it takes up to 12 seconds to flush a batch of them to disk, when it should complete that work in under 1 second. In other words, it's trying to do too much work at once.
Resolution
1. Change the default value of 1000 to 256 in the SPECROOT/mysql/my-spectrum.cnf by adding the following line:
innodb_lru_scan_depth = 256
3. Stop Archive Manager
4. Restart MySQL
5. Start Archive Manager
The value can be changed dynamically without restarting the MySQL, e.g.
mysql> SET GLOBAL innodb_lru_scan_depth=256;
But to set it permanently you have to add in the cnf file.
You can also get the current value from the MySQL by running this query:
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lru_scan_depth';
Additional Information
As mentioned by MySQL Docs
A setting smaller than the default is generally suitable for most workloads. A value that is much higher than necessary may impact performance. Only consider increasing the value if you have spare I/O capacity under a typical workload. Conversely, if a write-intensive workload saturates your I/O capacity, decrease the value, especially in the case of a large buffer pool.
Feedback
