MariaDB 10.11 is now Generally Available (GA), or stable if you prefer. It is a Long Term Support (LTS) version, so it is recommended for production use.
As I do for every new stable version, here I’m writing my review of MariaDB 10.11.
As mentioned, MariaDB, 10.11 which is now GA, is a production-ready LTS version. The previous LTS is MariaDB 10.6. All versions released between these two are Short Term Support (STS) versions. This means that their End Of Life is set one year after their first GA release. So, while some users prefer to wait some time before upgrading to the latest LTS, you should make sure that you are using a supported version.
For supported systems, see MariaDB Deprecation Policy.
For plugins maturity level, you can query the
MariaDB 10.11 can be used as a replica of older MariaDB versions. This is the rule that MariaDB and MySQL versions generally follow, though it’s always possible that the use of some removed or modified features crashes replication or produces inconsistencies.
For this review, I used the following sources of information:
- MariaDB KnowledgeBaase: Changes and Improvements in MariaDB 10.11
- MariaDB KnowledgeBase: Release Notes – MariaDB 10.11 Series
- MariaDB JIRA
- MariaDB Foundation blog
Features from STS versions
Since MariaDB 10.6 LTS (see my review), four Short Term Support versions were released. The changes they introduced had been tested and discussed by the community in advance, before being included into the next LTS version, MariaDB 10.11. Let’s see what these changes are.
Here are the changes that seem the most important to me:
- InnoDB Redo Log format was modified to reduce write amplification.
innodb_change_bufferingis now deprecated and ignored.
innodb_log_file_sizeis now dynamic.
innodb_buffer_pool_chunk_sizeis now allocated dynamically.
- Fast bulk
- Better concurrency on FreeBSD and DragonflyBSD.
FULLTEXTsearches can now find words containing apostrophes, like
- Other storage engines:
- Various SPIDER clean-ups and refactoring.
- SPIDER connecton strings don’t need to be written in the
- CONNECT now supports
- High availability:
- Lag-free replication of
- Replicas use GTID by default when it’s enabled on master.
- Demote a master safely and easily with
CHANGE MASTER TO MASTER_DEMOTE_TO_REPLICA = 1.
- Galera writes the
wsrep_status.jsonfile to make SST troubleshooting easier.
- Sequences also work with Galera now, if they’re declared with
- Lag-free replication of
- Descending indexes.
- More accurate JSON histograms for better optimisation of joins that don’t use indexes when engine-independent statistics are enabled.
- Faster unicode collations, particularly for ASCII characters.
- Hashicorp Vault plugin.
- OpenSSL 3.0.
mariadb --sslclient option is enabled by default. But no error is shown if a TLS connection can’t be established.
- However, MariaDB 10.10 won’t start if SSL is not configured correctly.
password_reuse_checkplugin can be used for preventing users from reusing the same password in a certain period of time.
- Compression capabilities are now added to MariaDB and its storage engines by specific plugins. Currently, the supported compression plugins are: bzip2, lzma, lz4, lzo, snappy.
This is just a quick summary. For more information, here are the links to version reviews written by me. You can find further information there.
- I didn’t review MariaDB 10.7, but I mentioned some of its features in my MariaDB Server Fest 2022 talk:
Back to the future: recent MariaDB features to learn for a happy life
- A summary of MariaDB 10.8: key performance improvements
- A summary of MariaDB 10.9: Vault integration,
- A summary of MariaDB 10.10:
MariaDB 10.11 also comes with several new features that weren’t included in any past version.
The following is a selection of the most important features. The choice was made based on my opinion, internal discussions at Vettabase, and customer needs.
Some highlights for lazy readers:
innodb_undo_tablespacesis now a dynamic variable;
innodb_read_io_threadsare now dynamic;
replicate_rewrite_dbis now a variable;
- mariadb-dump now fully supports temporal tables;
- Permissions can be assigned to
In version 10.11 the
PUBLIC pseudo-role was added,
SUPER does not include
READ_ONLY ADMIN, and passwordless login is supported on Windows.
The PUBLIC pseudo-role is now supported. The following SQL statements were implemented:
GRANT TO PUBLICallows to grant some privileges to all users.
REVOKE FROM PUBLICstatement causes permissions to be revoked from
PUBLIC. The users and roles that were explicitly granted those permissions will still have them.
SHOW GRANTS FOR PUBLICshows the
GRANTstatement that can be used to restore the PUBLIC permissions on a new instance, or in case they are somehow lost.
A typical use case for
PUBLIC would be to let all users
SELECT from a certain table. One still needs to authenticate into MariaDB to acquire public privileges.
For more details, see MDEV-5215.
We can set
read_only=1 to make a MariaDB instance read-only. This is is normally done with replicas, to make sure that no one writes data on replicas by accident. So, even if we have a proxy with read-write split that is not configured properly, we can be sure that no data change will happen on replicas.
However, a DBA might need to change data on read-only servers for a good reason. For example, there may be inconsistencies between the master and replicas; such inconsistencies need to be fixed manually.
But which permissions allow to write data on a read-only instance?
- MariaDB versions prior to 5.2 required the
SUPERprivilege to write into non-temporary tables when
- In the 5.2 version, a
READ_ONLY ADMINpermission was introduced. To write data into a read-only instance required
- Starting from 10.11,
SUPERdoes not allow to write into read only-instances. To do that,
READ_ONLY ADMINis now necessary.
Why was thing change made? I can think of the following use cases:
- Disallow everyone from writing into a read-only MariaDB server to avoid inconsistencies. I don’t believe this is wise, because a DBA might need to repair inconsistencies that were generated in other ways. Inconsistencies might occur because of binary log
MIXEDformat, or replication filters.
- DBAs want to be able to modify data on a read-only server, but not by mistake (i.e., not because they connected to a replica instead of the master by mistake). So they could only assign
READ_ONLY ADMINto a different user, or a role that is assigned to DBAs but not enabled by default.
- Tools or persons you don’t completely trust might have the
READ_ONLY ADMINprivilege but not
SUPER, or the other way around.
For more details, see MDEV-29596.
Windows: Passwordless local authentication
On Linux systems, the
unix_socket plugin allows to bind MariaDB users to one system user. Typically this is done to allow passwordless authentication for root: the user connects to the system (perhaps via
sudo) and then to MariaDB. This eliminates the need for double authentication (system and MariaDB) which previously led most users to write the root password in
unix_socket, as the name suggests, can’t work on Windows. But starting from version 10.11, we can use the GSSAPI authentication plugin to allow local authentication in a similar manner. The main difference here is that the client needs to support this authentication type. I suppose that the
mariadb command-line client (which comes with MariaDB itself) supports this feature.
Normally I don’t cover Windows in my MariaDB reviews, but I believe that some Windows users will be happy about this change.
InnoDB is the default storage engine in MariaDB. If you don’t know which storage engine you use or don’t even know what storage engines are, you use InnoDB. This is normally a great choice, because InnoDB performance and reliability are excellent. Other storage engines should be used in corner cases, or to leverage particular features.
Version 10.11 introduces some changes to InnoDB that make it easier to tune its configuration for performance.
innodb_undo_tablespaces determines the number of InnoDB undo logs. Before 10.11, the default value was 0, which meant that the undo log was written into the system tablespace. Starting from 10.11, the default value is 3. This means that you have three undo logs, and each is written in a file located in the data directory (or
innodb_undo_directory). More importantly, before 10.11 the
innodb_undo_tablespaces value could not be changed after the database was created, so in most cases one wouldn’t try to find the optimal value.
For more details, see MDEV-19229.
Background IO threads
The innodb_write_io_threads and innodb_read_io_threads variables represent the number of InnoDB background I/O threads. The former is useful to update the data files asynchronously (only transaction logs are synchronous), and the latter lost most of its importance when InnoDB read ahead feature was disabled and partially removed in 10.6.
The variables are dynamic starting from 10.11, so they can be changed without restarting MariaDB. Previously, to find the optimal values for your case, you had to run several tests, and before every test you had to restart MariaDB and warm up InnoDB buffer pool.
For more details, see MDEV-11026.
Removed buffer pool throttling
The best documentation for this change is the discussion in the task MDEV-25417.
To understand this change, you need to know that, by default, InnoDB dumps part of the buffer pool to disk on shutdown, and loads it on start.
Also, InnoDB throttles storage input and output based on
innodb_io_capacity_max, but that only matters when transaction checkpointing is struggling to catch up). The MariaDB team has learned that some users increase
innodb_io_capacity at startup to speed up the buffer pool loading. This is a bit dangerous, because there is a risk that users fail to set it to a reasonable value after the loading, so InnoDB could perform too many I/O operations.
Initially they considered adding a new variable that only controls I/O throttling during InnoDB buffer pool loading. Yet, after some tests, it turned out that throttling the buffer pool loading was not useful. So eventually they decided to remove the buffer pool loading throttling feature.
This change was applied to previous versions as well.
Every version comes with some performance optimisation. Let’s see what improved in 10.11.
Some improvements were made to the query optimiser:
DELETEswith semijoins are now properly optimised. We’re talking about statements in this form:
DELETE FROM t1 WHERE t1.x = (SELECT x FROM t2 WHERE t1.id = t2.id);
Previously, the general recommendation was to use a multi-table syntax with
DELETErather than using subqueries. See MDEV-7487.
EXPLAIN FORMAT=JSONnow returns the time spend by the optimiser to determine an execution strategy. MDEV-28926.
information_schema database contains some of the informational system tables.
- Previously, queries against
PARAMETERSwere run with a full table scan even if the WHERE clause was specific and allowed to make an efficient use of an index. This behaviour has changed. See MDEV-20609.
- Previously, queries against
PARAMETERSloaded all examined procedures code. This was especially slow in the case of full table scan. Now the procedures code is not loaded if the query only returns procedures or parameters names. See MDEV-29104.
MariaDB implemented temporal tables in version 10.3, in 2018. Since then, pretty much every major version came with some improvements to temporal tables. 10.11 has improvements for a type of temporal tables, called system-versioned tables. These tables use a row versioning controlled automatically by MariaDB. This preserves past data, allowing us to run queries on the table as it appears at a certain point in time, or to query how the data evolved over time.
The problem with system-versioned tables was that the history could not be modified until now. So it was impossible to take a dump and restore it.
Version 10.11 solves this problem in this way:
- mariadb-dump now dumps system-versioned tables history, see MDEV-16029.
system_versioning_insert_historyvariable was added. It is set to OFF by default. But it’s dynamic and, if enabled, it allows to insert past versions of the rows, with specified timestamps. Without this option, we could now take a dump but we couldn’t restore it. See MDEV-16546.
Also, in this version a bug has been fixed which caused fulltext indexes corruption in system-versioned tables. See MDEV-25004.
Tidying up and usability enhancements
With every new version, MariaDB takes some steps to improve the usability for the existing features. In 10.11, they made some enhancements to the existing server variables.
Slow log settings
One problem with variable names is that they are not always grouped in a clean way. Recently some steps have been made to solve this problem.
Starting from version 10.11, we can see all variables that affect the slow query log by running
SHOW VARIABLES LIKE 'slow_log%'; or, if we use a GUI that orders variables alphabetically, we can look for the ones that have a
slow_log_ prefix. This means that the prefix has been added to the following variables:
The replicate_rewrite_db variable
replicate_rewrite_db instructs a replica to change a database name. This is often done to replicate a test database . But previously it was only a startup option that one could specify on startup:
This implies that we have to modify the service configuration… which is not a good idea. Services should only be modified to change the start/stop/restart logics.
Starting from 10.11, the corresponding variable exists, so we can specify it in the configuration file:
replicate_rewrite_db = 'vettabase->vettabase_alpha'
MariaDB 10.11 is a new LTS release suitable for production use.
I listed MariaDB 10.11 most useful features (in my opinion). This includes features that were introduced in recent STS versions.
If you want to take advantage of the recent and old MariaDB performance optimisations, consider a MariaDB Health Check service from Vettabase.