Mariadb – Database stalls on innodb_buffer_pool_wait_free

mariadb

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.

mariadb last 3hour dashboard

Best Answer

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.

Related Question