MariaDB 10.6: A Review

MariaDB 10.6 went Generally Available (production ready)!

The first GA version is 10.6.3, released on 6th July 2021. I’ve made a quick test and I want to put my own thoughts and comments here. For more details, take a look at the Changes and Improvements in MariaDB 10.6 in the MariaDB KnowledgeBase.

What’s new

To me, 10.6 is mostly about refining some areas that needed improvements, and increasing observability. There are also some useful new features, some of which were imported from MySQL.

New features

As mentioned, I don’t expect the list of new features to impress you: they’re not many and some of them are merged from MySQL. But they’re useful.

Ignored indexes

Useful to test what happens if you drop an index before actually dropping it. If something goes wrong, you can “instantly” make it visible again.

See my article MariaDB Ignored Indexes for a practical, complete example of how to use this feature.

Observability

The sys schema is now supported: “system” database with views and procedures that mainly aim to investigate performance problems. It’s not really a system database, because root can modify or drop it.

A couple of performance_schema tables related to replication were added. Namely:

• replication_applier_status
• replication_applier_status_by_worker
• replication_applier_status_by_coordinator
• replication_applier_configuration
• replication_connection_configuration

The information_schema now includes tables that provide information about the supported SQL language, and can be useful for upgrading:

• KEYWORDS
• SQL_FUNCTIONS

This feature was imported from MySQL, after removing or adapting some views.

SKIP LOCKED

With the SKIP LOCKED syntax, locking SELECTs can read and return the rows that are not locked, skipping the ones that are locked instead of waiting for the lock to be released. Locking SELECTs are queries that use the LOCK IN SHARE MODE or FOR UPDATE options. This addresses a typical need of applications that allow multiple users to book limited resources (flights, hotel rooms, etc).

Imported and adapted from MySQL.

JSON_TABLE()

JSON_TABLE() is a table function that transforms a JSON document (or part of it) into a table. The resulting table can be used just like a regular table, or a view, or a subquery.

Imported from MySQL.

OFFSET … FETCH … [WITH TIES]

This syntax is equivalent to LIMIT ... OFFSET, but the WITH TIES optional clause adds an extra functionality:

SELECT id, name, city
    FROM user
    ORDER BY city
    LIMIT 10;

SELECT id, name, city
    FROM user
    ORDER BY city
    OFFSET 0 ROWS
    FETCH FIRST 10 ROWS
        WITH TIES;

The first query orders the results by city and return the first 10 rows. If the last city is 'Edinbburgh', you won’t know how many users you have from Edinburgh.

The second query will return at least 10 rows. But if the last city is 'Edinburgh',it will also return all users from Edinburgh.

See also MariaDB: WITH TIES syntax.

Oracle compatibility

Part of the ongoing work to make MariaDB more compatible with Oracle. Some Oracle syntaxes and functions were added. This greatly eases the migration.

If you care about Oracle compatibility, you may want to know that:

• MariaDB has now an ORACLE SQL_MODE, which enables some Oracle features and makes MariaDB behaviour more similar to Oracle’s.
• The main feature enabled by the afore mentioned option is a PL/SQL parser, that allows us to run many Oracle stored procedures in MariaDB.

Improvements

As mentioned, MariaDB 10.6 is mostly about improving existing features. Here is a selection of notable improvements.

Atomic DDL

Most CREATE, ALTER, DROP and RENAME statements are now atomic and crash-safe. This means that, even if the server crashes during one of these operations, the change will be completely done or not done at all. You won’t have, for example, orphan files or unusable objects.

To make DDL crash-safe, MariaDB now logs DDL changes into a DDL log file. Keep this in mind if you have big tables and your disk space is limited.

See Which DDL Operations are Now Atomic. Note that DDL is not implemented yet for:

• Partitioned tables.
• Tables with Foreign Keys (MDEV-21053).
• CREATE OR REPLACE TABLE: a table may still be dropped and not re-created if a crash occurs(MDEV-25292).

InnoDB

Like all versions, 10.6 comes with several InnoDB improvements. Some of them are:

• The first INSERT into an empty table is faster. This includes importing a dump that uses multi-row INSERTs (MDEV-515).
• Writes to the temporary tablespace are normally avoided. Implicit and explicit temporary tables are faster (MDEV-12227).
• Read-ahead of type linear was removed. The random type is still supported but disabled by default. It’s now controlled by innodb_random_read_ahead (rather than innodb_read_ahead).

Galera

10.6 comes with some Galera improvements, as well. The following are the most relevant:

• If encrypted connections between nodes are disabled, you can now enable them without downtime (MDEV-22131).
• A wsrep_mode variable now includes flags that specify if controversial Galera compatibility features should be enabled (BINLOG_ROW_FORMAT_ONLY, DISALLOW_LOCAL_GTID, REQUIRED_PRIMARY_KEY, REPLICATE_ARIA, REPLICATE_MYISAM, STRICT_REPLICATION).
  • This obsoletes wsrep_replicate_myisam and wsrep_strict_ddl. They are still in version 10.6, but they were removed in version 10.7, see MDEV-24947 and MDEV-24843.

Cleanup

Cleaning up MariaDB is useful in many ways. It reduces the amount of code and use cases that the MariaDB team can maintain, helping them to develop a better product in the future.

Storage engine cleanups:

• The TokuDB engine, not supported anymore by Percona, was removed.
  TokuDB was designed to allow fast writes, thanks to fractal indexes. In this data structure, each node (except for leaves) has a buffer: when a buffer is full, data changes are pushed to the lower level. TokuDB was developed by TokuTak, which was acquired by Percona. But probably it never had the user traction that Percona hoped.
  Most TokuDB users might want to evaluate MyRocks.
• The Cassandra engine was removed.
  CassandraSE was never widely used. It used the Apache Thrift protocol to communicate with Cassandra, but nowadays it’s much better to use the CQL language instead (an SQL small subset with extensions to use Cassandra specific features). CassandraSE was not maintained, and didn’t compile for several years.
  If you’re looking for an alternative to let MariaDB “talk” to Cassandra, contact us.

InnoDB cleanups:

• 23 InnoDB variables were removed.
• Some innodb_checksum_algorithm were removed.
• Nowadays, InnoDB tables should be compressed using the page compression. The COMPRESSED row format is an old method, and MariaDB had plans to remove it. For this reason, 10.6 went GA with COMPRESSED set to read-only by default, as a first step before the complete removal of this feature. However, this change was reverted and the removal plan was cancelled based on users feedback, including my feedback. For more information see MDEV-23497, MDEV-22367, MDEV-27736 and the comments in these tasks.

Federico Razzoli