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_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.