Database stalls on innodb_buffer_pool_wait_free
what is the relationship between this LRU parameter and the database stalls I am experiencing?


The relationship is quite straightforward. The background flush thread every second will scan innodb_lru_scan_depth * innodb_buffer_pool_instances LRU list entries in an attempt to find dirty pages that it can write out to disk. Flushed pages become free and add to innodb_buffer_pool_pages_free.

If the DML thrown at your database produces dirty pages faster than the flush thread can write them out, at some point innodb_buffer_pool_pages_free will go down to zero, and the next attempt to read a page to the buffer pool will cause another innodb_buffer_pool_wait_free while the server flushes dirty pages to disk synchronously.

Ideally you would want the background flushing thread to write dirty pages to disk at the same rate (on average) as they are dirtied by the DML activities. You do that by increasing innodb_lru_scan_depth, which increases the probability of dirty pages being written to disk before the server needs more free pages.

An alternative, as mentioned in another answer, is to enable adaptive flushing and set the appropriate innodb_max_dirty_pages_pct and/or innodb_max_dirty_pages_pct_lwm values.

It should be noted that overly aggressive background flushing could saturate the I/O bandwidth on its own, which would be just as bad as excessive innodb_buffer_pool_wait_free events. At some point you might find that you will have to allocate more memory for buffer pools to satisfy your workload demands, or live with occasional free page waits.