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.
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.
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.
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.
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:
information_schema now includes tables that provide information about the supported SQL language, and can be useful for upgrading:
This feature was imported from MySQL, after removing or adapting some views.
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() 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.
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.
As mentioned, MariaDB 10.6 is mostly about improving existing features. Here is a selection of notable improvements.
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).
Like all versions, 10.6 comes with several InnoDB improvements. Some of them are:
- The first
INSERTinto an empty table is faster. This includes importing a dump that uses multi-row
- Writes to the temporary tablespace are normally avoided. Implicit and explicit temporary tables are faster (MDEV-12227).
- Read-ahead of type
linearwas removed. The
randomtype is still supported but disabled by default. It’s now controlled by
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).
wsrep_modevariable now includes flags that specify if controversial Galera compatibility features should be enabled (
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.
- 23 InnoDB variables were removed.
- Some innodb_checksum_algorithm were removed.
- Nowadays, InnoDB tables should be compressed using the page compression. The
COMPRESSEDrow format is an old method, and MariaDB had plans to remove it. For this reason, 10.6 went GA with
COMPRESSEDset 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.