MariaDB 10.11 LTS: New types and functions, more dynamic InnoDB configuration

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.

Maturity level

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 information_schema.ALL_PLUGINS table.

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.

References

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:

• SQL:
  • INET4 data type.
  • UUUID data type.
  • SFORMAT() function, for modern, complex string interpolation.
  • NATURAL_SORT_KEY() function, to order complex strings (version numbers, file names…) as a human being would do.
  • RANDOM_BYTES() function.
• InnoDB:
  • InnoDB Redo Log format was modified to reduce write amplification.
  • innodb_change_buffering is now deprecated and ignored.
  • innodb_log_file_size is now dynamic.
  • innodb_buffer_pool_chunk_size is now allocated dynamically.
  • Fast bulk INSERTs.
  • Better concurrency on FreeBSD and DragonflyBSD.
  • FULLTEXT searches can now find words containing apostrophes, like O'Brien.
• Other storage engines:
  • Various SPIDER clean-ups and refactoring.
  • SPIDER connecton strings don’t need to be written in the COMMENT clause anymore.
  • CONNECT now supports INSERT IGNORE.
• High availability:
  • Lag-free replication of ALTER TABLE.
  • 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.json file to make SST troubleshooting easier.
  • Sequences also work with Galera now, if they’re declared with NOCACHE and ENGINE=InnoDB.
• Performance:
  • 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.
• Security:
  • Hashicorp Vault plugin.
  • OpenSSL 3.0.
  • mariadb --ssl client 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.
  • The password_reuse_check plugin 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, innodb_log_file_size and more
• A summary of MariaDB 10.10: INET4 type, RANDOM_BYTES() and more

New features

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_tablespaces is now a dynamic variable;
• innodb_write_io_threads and innodb_read_io_threads are now dynamic;
• replicate_rewrite_db is now a variable;
• mariadb-dump now fully supports temporal tables;
• Permissions can be assigned to PUBLIC;

Security

In version 10.11 the PUBLIC pseudo-role was added, SUPER does not include READ_ONLY ADMIN, and passwordless login is supported on Windows.

PUBLIC

The PUBLIC pseudo-role is now supported. The following SQL statements were implemented:

• GRANT TO PUBLIC allows to grant some privileges to all users.
• REVOKE FROM PUBLIC statement causes permissions to be revoked from PUBLIC. The users and roles that were explicitly granted those permissions will still have them.
• SHOW GRANTS FOR PUBLIC shows the GRANT statement 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.

READ_ONLY ADMIN

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 SUPER privilege to write into non-temporary tables when read_only=1.
• In the 5.2 version, a READ_ONLY ADMIN permission was introduced. To write data into a read-only instance required SUPER or READ_ONLY ADMIN privileges.
• Starting from 10.11, SUPER does not allow to write into read only-instances. To do that, READ_ONLY ADMIN is 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 STATEMENT format, MIXED format, 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 ADMIN to 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 ADMIN privilege 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 /root/.my.cnf.

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

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.

Undo tablespaces

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 (or 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.

Performance optimisations

Every version comes with some performance optimisation. Let’s see what improved in 10.11.

Optimiser

Some improvements were made to the query optimiser:

• UPDATEs and DELETEs with 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 UPDATE and DELETE rather than using subqueries. See MDEV-7487.
• EXPLAIN FORMAT=JSON now returns the time spend by the optimiser to determine an execution strategy. MDEV-28926.

information_schema

The information_schema database contains some of the informational system tables.

• Previously, queries against ROUTINES or PARAMETERS were 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 ROUTINES or PARAMETERS loaded 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.

Temporal tables

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.
• The system_versioning_insert_history variable 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.

Incompatibilities 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:

• min_examined_row_limit
• slow_query_log
• slow_query_log_file
• long_query_time

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:

mysqld --replicate-rewrite-db=vettabase->vettabase_alpha

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'

SHOW COLLATIONS

SHOW COLLATION behaviour changed, causing possible connectors incompatibilities.

Since version 10.10, a collation can be used with multiple character sets. This is the case with UCA collations. But because of this change, collation names don’t have to include the name of a character set and are not unique.

In the output of the SHOW COLLATION command, multi-charset collations have some columns set to NULL, because those values don’t depend on the collation alone, but also on the character set it’s used with.

Another non-obvious consequence is that SHOW COLLATION LIKE 'utf8mb4%' shows information about all collations that refer to the utf8mb4 character set, even if their names might not start with utf8mb4.

This changed behaviour seems to be a problem for connectors, see MDEV-31608.

To use SHOW COLLATION with the same behaviour, use: SET sql_mode = 'NO_NULL_COLLATION_IDS'.

Conclusions

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.

Federico Razzoli