A summary of MariaDB 10.6

Last updated on 21 Luglio 2021

MariaDB 10.6 went Generally Available (production ready)!

The first GA version is 10.6.3, released on 6th July 2021. I encourage you to take a look at the 10.6 official summary, but I’ve made a quick test and I want to put my own thoughts and comments here.

Blixy acquired superpowers by upgrading our MariaDB version to 10.6!

What’s new

To me, 10.6 is about refining some areas that needed improvements, though there are some useful new features.

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 have been added. The information_schema now includes the lists of keywords and SQL functions.

Imported from MySQL, after removing or adapting some views.

SKIP LOCKED

You can SELECT or UPDATE rows that are not locked, skipping the ones that are locked. 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()

Transforms a JSON document (or part of it) into a table that can be used as 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.

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 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 INSERT s (MDEV-515).
  • Writes to the temporary tablespace are normally avoided. Implicit and explicit temporary tables are faster (MDEV-12227).

Galera

Some improvements to Galera were made, too. 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 ).

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. And it helps non-expert users to avoid features that nowadays it makes no sense to use, for various reasons.

Various cleanups:

  • The TokuDB engine, not supported anymore by Percona, was removed.
  • The Cassandra engine, that was never widely used and was still based on the Thrift protocol, was removed.

InnoDB cleanups:

Is it stable? Do I need to upgrade?

Short answer: yes, with caution. And perhaps with our help.

Usually we don’t recommend to upgrade MariaDB or MySQL as soon as they are declared stable (well, in the case of MySQL there are more factors to consider, but they don’t apply to MariaDB). MariaDB 10.5 added big internal changes to InnoDB just one version before going GA, which really conflicts with our understanding of stability.

However, MariaDB 10.6 is mostly a hardening version. This doesn’t make it bug free, obviously. But suppose, for example, that there are severe bugs in DDL crash-safeness: chances are that, in most cases, DDL statements will still be safe in 10.6. Also, increased observability and performance optimisations are worth an upgrade. That said, depending on how prudent you want to be, you may still prefer to wait for a couple of maintenance releases.

Note that upgrades always need to be tested. Check that all your procedures for backup, restore, scale out, etc, work properly with 10.6. If your operations are fully automated, testing will be relatively easy.

Upgrades should be made gradually. Ideally, start by creating an unused replica, just to see if it crashes and if it can keep up with the master. Then start to send read queries to it, but initially only do this in testing.

Before sending reads to a 10.6 replica, you need to test that queries work, that they return correct results, and that they’re not slow. Be particularly careful with new features, like SKIP LOCKED and JSON_TABLE() . There are ways to do this, but it’s not possible to cover this topic here.

Contattaci to discuss how we can help you to plan, test, and finally perform and upgrade to MariaDB 10.6.

Federico Razzoli

A proposito di Federico Razzoli

Federico is Vettabase Ltd director, and he's an expert database consultant specialised in the MariaDB and MySQL ecosystems.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

*