Database stalls on innodb_buffer_pool_wait_free I have 4 identical MariaDB 10.0.33 databases that are regularly experiencing database stalls in different situations. I'm trying to understand how to tune the innodb parameters to prevent these stalls. I am aware I can/should add more ram/more disks to better support this workload. However, I really want to understand the implications innodb_lru_scan_depth has on the innodb_buffer_pool_pages_free metric, and what is causing frequent stalls as measured by the innodb_buffer_pool_wait_free metric.

First, some config:


Raid10 x4 SSD, 64gb ram, zero swap
innodb_buffer_pool_size = 48G (yes i know this can be increased a little)
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_log_file_size = 4G
innodb_log_buffer_size = 8M
transaction-isolation = READ-COMMITTED
innodb_flush_neighbors = 0
innodb_io_capacity = 2000 # default 200
innodb_io_capacity_max = 4000 # default 2000
innodb_max_dirty_pages_pct_lwm = 50
innodb_max_dirty_pages_pct = 75
innodb_flushing_avg_loops=90
innodb_lru_scan_depth = 16384 (this is the most important)


Second, some stats:


Disk reads are fairly stable throughout the day at ~3k - 5k, peaks to 10k
Disk writes are fairly stable throughout the day at ~1k peaks to 2k
Disk read latency less than ~0.5ms on average, some spikes to 1ms
Disk write latency less than 3ms on average, some spikes to 10ms
Disk utilization averaging ~80% some spikes to 95-100%
Between 1% and 2% buffer pool cache miss ratio
Consistent 1k innodb pool pages flushed per sec
Consistent 13-18% nnodb pool bytes dirty
Consistent 350k - 420k pages dirty
Frequent drops of innodb_buffer_pool_pages_free from 130k down to 0k
Frequent spikes of innodb_buffer_pool_wait_free to as high as 25k


Increasing the innodb_lru_scan_depth from the default of 1024 improved the situation. innodb_buffer_pool_pages_free immediately grows every time I increase it.  In the attached dashboard, I changed from a value of 16384 to 32768 innodb_lru_scan_depth at 11:40 on two of the four servers, and the pages free jumped up to ~260k from ~130k. There seems to be strong correlation between a lack of free pages, lru depth, and wait free.

My primary question is, what is the relationship between this LRU parameter and the database stalls I am experiencing?

Second question, other than adding more ram/storage bandwidth, from the database perspective, what can I tune to throttle expensive workloads that may be running in parallel, and saturating my disks? There may be a correlation 
with the innodb_buffer_pool_pages_old metric increasing when I'm seeing periods of database stalls.  This seems to indicate something is doing a large full table scan, and making the buffer pool less optimal.
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.