1598645 - Investigate: innodb_lru_scan_depth is set properly

Status: NEW

(Tree Management :: Treeherder: Infrastructure, task, P3)

Description

[Kyle Lahnakoski [:ekyle]]

MySQL has a deamon that writes dirty pages to disk. I suspect it's write speed is constrained by innodb_io_capacity. The amount of work it does per cycle is proportional to innodb_lru_scan_depth: The number of pages to inspect each cycle, writing dirty ones to disk. With the cache preemptively cleaned, future transactions are free to overwrite clean pages with data they need. If all the pages in the cache are dirty, then the LRU page is written to disk, and the new data is loaded in its place, which effectively doubles the number of operations, and slows down the transaction. This write-and-read is probably constrained by innodb_io_capacity_max.

innodb_lru_scan_depth was decreased during the slowdown event

https://github.com/mozilla-platform-ops/devservices-aws/commit/69ab6c7d210e845ee735347cfdcbb42e0f86f5ba#diff-aac6aeeaf82f5228af02042b7e7825d2R78

The read latency also doubled around the same time

https://bug1597136.bmoattachments.org/attachment.cgi?id=9110657

The innodb_lru_scan_depth may be too low. The documentation suggests adjusting the number "... with the goal of rarely seeing zero free pages"

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth