MariaDB has broken the concept of dynamically configurable buffer pools!

Problem description

MySQL introduced the dynamically configurable InnoDB buffer pool with 5.7.5 in September 2014 (here and here):

The innodb_buffer_pool_size configuration option can be set dynamically using a SET statement, allowing you to resize the buffer pool without restarting the server. For example:

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

MariaDB 10.2.2 adopted this feature in September 2016 (source):

InnoDB was merged from MySQL-5.7.14 (XtraDB is disabled in MariaDB-10.2.2 pending a similar merge)

The problematic thing is, on the one hand, that this feature now no longer works as it did before and no longer works as expected. On the other hand, they changed the behaviour in spring 2025 within a major release series (LTS), which in my opinion is an absolute no-go (source):

From MariaDB 10.11.12 / 11.4.6 / 11.8.2, there are significant changes to the InnoDB buffer pool behavior.

And what is more, the description of this is quite poor (source):

• decreasing innodb_buffer_pool_size at runtime does not release memory (MDEV-32339)
• reorganise innodb buffer pool (and remove buffer pool chunks) (MDEV-29445)
• The Linux memory pressure interface, which could previously not be disabled and could cause performance anomalies, was rewritten and is disabled by default. (MDEV-34863)
• Server crashes when resizing default innodb buffer pool after setting innodb-buffer-pool-chunk-size to 1M (MDEV-34677)

In the corresponding worklog (MDEV-36197) MarkoM also describes a different behaviour:

innodb_buffer_pool_size_auto_max (my proposal for this task) would set the maximum for the automation (default: 0 to disable the logic).

which would have made much more sense in my opinion.

How did it work before?

How did it work in the past with MariaDB and still today with MySQL:

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool%size';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 2097152   |
| innodb_buffer_pool_size       | 134217728 |
+-------------------------------+-----------+

SQL> SET GLOBAL innodb_buffer_pool_size = @@innodb_buffer_pool_chunk_size * 128;

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool%size';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 2097152   |
| innodb_buffer_pool_size       | 268435456 |
+-------------------------------+-----------+

Thus everything OK. Works as expected and as usual.

What does MariaDB do today?

What happens today with MariaDB:

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool%size%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_buffer_pool_chunk_size    | 0         |
| innodb_buffer_pool_size          | 134217728 |
| innodb_buffer_pool_size_auto_min | 134217728 |
| innodb_buffer_pool_size_max      | 134217728 |
+----------------------------------+-----------+

SQL> SET GLOBAL innodb_buffer_pool_size = 256*1024*1024;
Query OK, 0 rows affected, 1 warning (0.000 sec)

SQL> show warnings;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '268435456' |
+---------+------+----------------------------------------------------------------+

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool%size%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_buffer_pool_chunk_size    | 0         |
| innodb_buffer_pool_size          | 134217728 |
| innodb_buffer_pool_size_auto_min | 134217728 |
| innodb_buffer_pool_size_max      | 134217728 |
+----------------------------------+-----------+

So nothing at all! Not even an error, just a warning. And if I do not look closely, I do not even realise that something did not work.

The MariaDB error log says:

[Note] InnoDB: Memory pressure event disregarded; innodb_buffer_pool_size=128m, innodb_buffer_pool_size_auto_min=128m

If you then do some searching, you find out that something has changed here: Buffer Pool Changes and try intuitively:

SQL> SET GLOBAL innodb_buffer_pool_size_max = 256*1024*1024;
ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size_max' is a read only variable

But that does not work either.

This means you have to restart the database! And possibly at the very moment when you do not actually want to restart the database and need this feature…

The documentation also states ((source):

Default Value: specified by the initial value of innodb_buffer_pool_size, rounded up to the block size of that variable. See the section about buffer pool changes in MariaDB 10.11.12, 11.4.6, and 11.8.2.

and (source):

If innodb_buffer_pool_size_max is 0 or not specified, it defaults to the innodb_buffer_pool_size value.

This means that I have to think again beforehand about how big I should make innodb_buffer_pool_size_max and can only correct it afterwards during operation, should I have forgotten or misjudged it.

In my opinion, this is a complete step backwards from an operational point of view. This is probably another implementation for some cloud-only as a service solution (enterprise?).

My suggestion is: Either, as suggested in the MDEV: 0 should switch off this feature and the behaviour should be as before or the default value should be set to 75% of the RAM size, as innodb_dedicated_server does with MySQL.

I had the audacity to open a bug here: New InnoDB Buffer Pool autosize feature not so optimal implemented.

FedericoR has thankfully recommended the following link: Issues with new buffer pool configuration in MariaDB Minors (10.11.12/13/14, 11.4.6/7/8, 11.8.2/3). I do not seem to be the only one who was annoyed by this change…

How does PostgreSQL do this?

PostgreSQL is currently not (yet) able to change shared_buffers dynamically. The default is usually 128M. The rule of thumb here, similar to MyISAM, is 25 - 40% of RAM. The lack of this feature is probably not as serious with PostgreSQL, however, as PostgreSQL relies heavily on the file system cache, similar to MyISAM.

Source: Resource Consumption

This page was translated using deepl.com.