MariaDB 11.4 LTS review: cost-based optimiser, autoconfigured SSL and more

MariaDB released a new Long Term Support version as Generally Available: 11.4. So I feel it’s time to resume my ongoing project of reviewing MariaDB new versions.

I didn’t review recent MariaDB versions, but this doesn’t mean I wanted to cease my reviews. My commitment is to review Long Term Support versions. Short Term Support versions are not recommended for production, because support only lasts for one year. I might still review them to show and comment new interesting features, but I don’t guarantee this.

I talked about some of the features listed in this page in my talk at MariaDB Server Fest 2022: Recent MariaDB features for a happy life.

Support and timeline

MariaDB 11.4 is the latest Long Term Support version (LTS). The first Generally Available release is 11.4.2, released on 29 May 2024.

This branch will be supported til 29 May 2029, unless the support is extended. The previous LTS branch was 10.11 (see my 10.11 review), which will be supported til 16 February 2028.

The 11.4 branch includes the enhancements introduced by recent Short Term Support versions: 11.0, 11.1, 11.2, 11.3, as well as news enhancements not previously published.

Enhancements since the 10.11 LTS version

I’m going to list the enhancements that seem to me most interesting, based on our customers needs, our internal discussions at Vettabase, and my personal opinions. The enhancement are grouped and sorted logically. This logical order doesn’t match which versions introduced the various changes.

My highlights:

• Cost-based optimiser;
• Online, engine-independent ALTER TABLE;
• Unused InnoDB system tablespace disk space is claimed on startup;
• Unused InnoDB temporary tablespaces disk space can be claimed at runtime;
• An SSL certificate is created by default, and verified by the clients by default;
• mariadb-dump support for parallel backups.
• Some impressive cleanup, that consisted in removing problematic features:
  • Galera causal reads;
  • InnoDB Change Buffer;
  • Ability to keep all tables in the system tablespace;
  • InnoDB defragmentation.

High availability

The START REPLICA UNTIL statement now supports the SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS parameters, to only execute transactions before or after a certain GTID. They also support the INCLUSIVE and EXCLUSIVE options, to indicate whether the specified GTID should be executed or not.

Improvements to the binary log:

• It’s now possible to limit the binary log by settinf max_binlog_total_size. This is a Percona contribution. With slave_connections_needed_for_purge we can set a minimum number of replicas that are required to be connected for the master to purge the binary log.
• The binary log has a global mutex. Previously, an event checksum was calculated while the mutex was held. Now this calculation is made before acquiring the mutex, to improve concurrency. See MDEV-31273.
• binlog_row_image can now be set to FULL_NODUP. This is similar to FULL, which includes in the binary log the old and new versions of a row. But FULL_NODUP includes the unmodified values only once, resulting in a smaller binary log and less IO. Note that the FULL_NODUP mode is supported by MariaDB flashback feature. However, tools that consume the binary log might not support it. This is an Alibaba contribution.
• Some server system variables related to binary log filters were added: binlog_do_db, binlog_ignore_db, binlog_row_event_max_size. Previously, these filter could only be set with MariaDB startup options.

InnoDB

A historical problem with InnoDB is that even if system tablespace data is deleted, disk space was never claimed. So, if you have a crazy long transaction that lasts for hours, you might see your ibdata* files growing remarkably and you will never be able to shrink them. Fortunately, unused space is now freed on MariaDB startup.

The temporary tablespaces have been separated from the system tablespace in MariaDB 10.4. Their unused disk space is freed:

• On MariaDB startup;
• When SET innodb_truncate_temporary_tablespace_now=1 is run.

Other InnoDB tablespaces shrink when the user runs ALTER TABLE FORCE.

ALTER TABLE IMPORT is now a bit easier to use. Running CREATE TABLE and ALTER TABLE DISCARD TABLESPACE statements is no more necessary when moving a tablespace. See MDEV-26137.

ColumnStore

MariaDB 11.4 is the first production-ready version that is compatible with ColumnStore 12.04. This ColumnStore version comes with extensive architectural changes that we are trying to document in our MariaDB ColumnStore Unofficial Documentation Project.

ColumnStore needs to be installed as a separate package.

Migrations

Most ALTER TABLE operations now are run by copying the table and without locks, no matter which storage engine is used. It is still possible to specify a different ALGORITHM to avoid copying the table. This can be useful if the table is big and read-only, or if there is not enough space on disk.

ALTER TABLE … EXCHANGE PARTITION and ALTER TABLE … CONVERT TABLE … TO now support the WITH VALIDATION (default behaviour) and WITHOUT VALIDATION clauses. See MDEV-22164.

Stored programs (procedures, functions, triggers, events) typically access some tables. MariaDB has to validate them under some circumstances, to check if the tables have been modified after the last execution. The 11.4 version solves some problems with stored program validation by using the same logic that is used for prepared statements. See MDEV-5816.

Observability

Improvements to the information_schema.PROCESSLIST view:

• Columns are now bigger, so that less SQL queries will be truncated. This was a common problem with a simple (partial) solution.
• A SENT_ROWS column has been added. This is useful to identify queries that cause a lot of work just to retrieve a few rows (though this can be normal for some GROUP BY queries).

Temporal tables can be of type system-versioned (rows are automatically versioned by MariaDB), application-time periods (each row refers to time periods indicated by the user) or bitemporal (both). For a better understanding, see my webinar MariaDB Temporal Tables: A Demonstration. Previously, metadata about application-time periods were not available in the information_schema. Now time periods metadata are exposed in the following places:

• In the new PERIODS view;
• In the new KEY_PERIOD_USAGE view;
• The COLUMNS view now has additional columns: IS_SYSTEM_TIME_PERIOD_START and IS_SYSTEM_TIME_PERIOD_END.

Security

New permission: SHOW CREATE ROUTINE. It allows a user to see a store procedure owned by another user.

New table in the sys schema: privileges_by_table_by_level. A privilege can be assigned at several levels: database, table or column. This table shows privileges on each table grouped by table and privilege level.

SSL is now enabled by default. If no certificate is provided, MariaDB will create a self-signed certificate.

The clients will require a certificate by default, and will verify certificates by default. This new client behaviour can be disabled. The same applies to replicas. The clients also have options to verify the servers using their fingerprints.

Optimiser

The optimiser used to have a pure rule based model. The MariaDB 11 major version introduces a cost model. This means that the optimiser is aware of the cost of various operations in terms of performance, and will try to choose the query plan with the lowest cost. In case the estimations prove wrong, the user can modify the system tables that indicate the cost of the operations.

This is a major change that can’t be discussed here. It will be the subject of a future article.

Descending indexes can now be used for MIN() and MAX(). See MDEV-27576.

Some functions can now use indexes. In the past, wrapping a column name in a function call presented the use of indexes. For example WHERE UPPER(col) = 123 or ORDER BY UNIX_TIMESTAMP(col). This is because the optimiser doesn’t know what functions or expressions return, it sees them as black boxes.

Now the following functions can use indexes:

• DATE(col);
• YEAR(col);
• UPPERCASE(col) and UCASE(col), if the column collation is case-insensitive (I.E., it ends with _cs or _bin).

LOWER() and LCASE() can’t use indexes yet. See MDEV-31955.

SQL

New JSON functions:

• JSON_SCHEMA_VALID() validates a JSON string against a JSON Schema Draft 2020-12. The JSON Schema used by MariaDB is only partly compatible with the one used by MySQL, which implements the 2013 version;
• JSON_OBJECT_FILTER_KEYS();
• JSON_OBJECT_TO_ARRAY();
• JSON_ARRAY_INTERSECT().

JSON_TABLE() is a function that converts a JSON document to a tabular resultset. Now allows to write the JSON object keys into a dedicated column (MDEV-30145).

CONV() converts numbers from one base to another, for example, from the decimal to the octal system. It can now output base 62 numbers.

The new FORMAT_PICO_TIME() function accepts a number of picoseconds, and outputs a human-readable string describing the time interval.

DATE_FORMAT() now supports new symbols:

• %Z for the timezone string abbreviation;
• %z for the timezone as offset from UTC.

The INET4 and INET6 types can now be compared, so you can look for an IPv4 in an INET6 column. More generally, an INET4 value can be converted to INET6.

Miscellaneous improvements

mariadb-dump now supports parallel backups, similarly to mydumper.

Partitioned tables now can have columns of the GEOMETRY type or one of its subtypes. See MDEV-19177.

MariaDB now supports redirection of a client or a proxy to another MariaDB instance. These are the use cases:

• If you don’t use any proxy, you can use this feature to redirect clients away from the current instance, to put it in maintenance mode or decommission it (eg, to scale down after a temporary traffic peak). But this feature can’t help you if the instance crashes, for example. So you should definitely use a proxy instead.
• If you use one or more proxies, each proxy instance will find out if an instance disappear, and it has ways to put instances in maintenance mode. If detecting dead instances takes time, you can use this feature to force proxies stop using the current instance immediately, then wait until running queries complete, and then shutdown the instance.

However, at the moment of this writing, this feature is only theoretically useful because it lacks client side support.

SPIDER now accepts dedicated table and partition options to specify how to connect to the remote table. Now we can avoid using the CONNECTION or COMMENT options. Comment parsing can be disable by setting spider_ignore_comments to 1.

MariaDB has an Oracle mode that allows to use several Oracle syntaxes, and it’s very useful to migrate applications from Oracle. Its most extensive effects consist in implementing PL/SQL, the Oracle language for stored procedures. Now it is possible to create packages of stored procedure even when we’re not using the Oracle mode. This means that, instead of statements like CREATE PROCEDURE, we can use statements like CREATE PACKAGE, which contain the procedures definition.

Removals, incompatibilities and usability enhancements

The following features were removed:

• innodb_file_per_table=0 – It is not possible to store user-created tables in the system tablespace. This was the old way InnoDB stored tables, before the innodb_file_per_table variable was introduced.
• InnoDB Change Buffer, disabled by default since MariaDB 10.6 and deprecated since MariaDB 10.11, has now been removed. This solves situations were many data have to be flushed altogether causing a stall, and makes it possible to fix some other bugs. See MDEV-29694 and a talk from Marko Makela, InnoDB main developer: InnoDB change buffer: Unsafe at any speed.
• innodb_flush_method  was replaced by these more friendly, boolean variables:
  • innodb_log_file_buffering;
  • innodb_data_file_buffering;
  • innodb_log_file_write_through;
  • innodb_data_file_write_through.
• InnoDB defragmentation was removed. Note that we can still rebuild an InnoDB table with ALTER TABLE FORCE, and the rebuilt table won’t be fragmented. See MDEV-30545.
• The tx_isolation variable was renamed to transaction_isolation.
• The old_alter_table variable has been removed. It was olready deprecated and replaced by alter_algorithm.
• The wsrep_causal_reads variable was already deprecated and has now been removed.
• Mariabackup:
  • The prefix of files created by Mariabackup changed from xtrabackup_ to mariadb_backup_ (MDEV-18931).
  • The --innobackupex option was deprecated.
• The mysqldump symlink was removed.

Contributions

MariaDB 11.4 includes a couple of important external contributions. As mentioned above:

• The FULL_NODUP option was contributed by Alibaba;
• max_binlog_total_size was contributed by Percona.

Federico Razzoli