0

I have recently updated MariaDB from 10.4 to 10.6. One major change in the underlying InnoDB engine is the removal of buffer pool instances, so there is always only one.

I believe this affects some math done for flushing and free buffers.

What worries me is the value of Free buffers when checking SHOW ENGINE INNODB STATUS;

Which is in my case just 1 (see below).

Is this OK? Should I worry almost no free buffers?

On old MariaDB 10.4 with same load and same InnoDB config I have hundreds/thousands of free buffers (about 1024 per pool and with 64 pools thats a lot)

I am basing my ideas on this article from percona and I tried to adjust innodb_lru_scan_depth to various values. from the default 1024, all the way down to 128 or up to 4096. And this changes NOTHING for the Free buffers in InnoDB engine status.

Here are some variables I use:

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_lru_flush_size | 32    |
| innodb_lru_scan_depth | 1024  |
+-----------------------+-------+
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_adaptive_flushing       | ON        |
| innodb_adaptive_flushing_lwm   | 10.000000 |
| innodb_flush_log_at_timeout    | 1         |
| innodb_flush_log_at_trx_commit | 2         |
| innodb_flush_method            | O_DIRECT  |
| innodb_flush_neighbors         | 0         |
| innodb_flush_sync              | ON        |
| innodb_flushing_avg_loops      | 30        |
+--------------------------------+-----------+
+--------------------------------------+-----------+
| Variable_name                        | Value     |
+--------------------------------------+-----------+
| innodb_max_dirty_pages_pct           | 80.000000 |
| innodb_max_dirty_pages_pct_lwm       | 0.000000  |
| innodb_page_size                     | 16384     |
+--------------------------------------+-----------+
+------------------------------------------+--------+
| Variable_name                            | Value  |
+------------------------------------------+--------+
| innodb_io_capacity                       | 100    |
| innodb_io_capacity_max                   | 2000   |
| innodb_read_io_threads                   | 8      |
| innodb_version                           | 10.6.7 |
| innodb_write_io_threads                  | 8      |
+------------------------------------------+--------+

And here is InnoDB ENGINE STATUS

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 19584, seg size 19586, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 17113639169452
Log flushed up to   17113638981154
Pages flushed up to 17111149633411
Last checkpoint at  17111149633411
0 pending log flushes, 0 pending chkp writes
14645584 log i/o's done, 97.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 95026151424
Dictionary memory allocated 562154200
Buffer pool size   5710848
Free buffers       1
Database pages     5710847
Old database pages 2108085
Modified db pages  1003052
Percent of dirty pages(LRU & free pages): 17.564
Max dirty pages percent: 80.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 1978993, not young 8837190
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4210165, created 19119199, written 39595529
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5710847, unzip_LRU len: 0
I/O sum[145]:cur[34], unzip sum[0]:cur[0]
7
  • What makes you think that something is actually wrong / sub-optimal? It looks like an optimisation went into MariaDB that made it not waste memory by keeping as much free. If you are not seeing semphore wait timeout errors in your logs, the chances are that it works exactly as it should, and you end up with a slightly bigger effective buffer pool size than what you were having before. Feb 18 at 15:39
  • @GordanBobić Well having 0 free buffers just looks wrong to me, when on other instances there are thousands. I am basing my assumption that the value should be > 0 on this article, specifically the innodb_lru_scan_depth section. percona.com/blog/2020/05/14/…
    – michnovka
    Feb 19 at 17:25
  • I'd probably want to see evidence of a performance degradation directly caused by it before assuming it is abnormal. Historically, if this was becoming problem you would start getting semaphore wait timeout errors. The need to keep many free pages has long been something to be optimized out. Feb 19 at 18:58
  • There seem to be no evidence of performance degradation. No semaphore wait timeouts in logs. But check official documentation: dev.mysql.com/doc/refman/8.0/en/… , specifically the section which says: "When tuning innodb_lru_scan_depth, start with a low value and configure the setting upward with the goal of rarely seeing zero free pages." . So having this value at 0 does not seem preferable even according to docs.
    – michnovka
    Feb 19 at 19:23
  • 2
    the first rule of performance tuning is to not overtune. In virtually all cases you shouldn't need to touch more than maybe 5-6 settings away from defaults (and LRU settings are not among them). In the past 20 years of working with MySQL I think I had to touch innodb_lru_scan_depth once, and that was on a server that was running with absurdly underspecified RAM and screaming about semaphore wait timeouts just until more RAM arrived. You are getting hung up on things that aren't relevant. Feb 20 at 6:10

1 Answer 1

1

The innodb_lru_scan_depth affects the rate of flushing modified (or "dirty") pages in the buffer pool.

The number of free buffers measures only how many pages of the buffer pool are unoccupied. Zero free buffers indicates that the buffer pool is fully occupied, but it does not necessarily indicate that the pages are dirty. They might be mostly or entirely read-only copies of data on disk, loaded into the buffer pool to satisfy SELECT queries, and therefore they do not require flushing. So the innodb_lru_scan_depth would have no effect in that scenario.

The number of free buffers alone cannot be used to guide tuning the innodb_lru_scan_depth.


Update:

I thought of a way the flushing could affect the checkpoints. If your rate of writing transactions is very high, you could be filling the log file faster than the background InnoDB threads can flush dirty pages. In that case, you could increase the log file size to give it more flexibility, or you could tune the innodb_lru_scan_depth to try to flush pages faster. But there's a practical limit to the flushing rate on any storage.

Ultimately, you could be trying to write transactions faster than a single server can keep up with. In that case, you need to split the database writes over multiple servers. Many growing applications face this challenge. It's not easy.

2
  • This is a slave node which has no SELECT queries, but still your explanation makes sense. Still tuning this variable does not in any way affect the checkpoint age, which regardless of its value keeps growing at constant speed and then at one point flushes all data to disk. Shouldnt this variable at least affect the rate of growth of checkpoint age?
    – michnovka
    Feb 21 at 12:45
  • No, the checkpoint age is based on the rate of writes to the redo log. Flushing happens asynchronously to writes. The lru scan depth might affect the flushing, but not the writes to the redo log. Feb 21 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.