A summary of MariaDB 10.9: Vault integration, innodb_log_file_size and more

MariaDB 10.9 GA was released on the 22nd of August 2022. It is a short-term release, and we are not recommended to use it in production because it will only be supported for one year. The latest long-term release, at the time of this writing, is MariaDB 10.6.

A summary of MariaDB 10.9, by Federico Razzoli

General thoughts and highlights

As far as I can see, MariaDB Foundation and MariaDB Corporation haven’t done much to promote version 10.9 as of now. The reason is probably simple: while it surely brings some interesting improvements, there is nothing particularly appealing from a marketing perspective.

My personal highlights for this version are:

  • A nice feature for troubleshooting running queries and to monitor/troubleshoot a cluster.
  • A HashiСorp Vault plugin for key management.
  • Several small performance optimisations.
  • innodb_log_file_size is now dynamic.

HIgh Availability

MariaDB provides high availability with these built-in features:

  • Galera Cluster;
  • asynchronous replication;
  • a semi-synchronous replication plugin.

Starting from version 10.9, we have a wsrep_status.json file that makes checking the health status of a Galera node much easier for a script. Occasionally, it becomes easier for humans, too. Here is an example copied from the JIRA task:

{
"date": "2021-09-04 15:35:02.000",
"timestamp": 1630758902.00000000,
"errors": [
{
"timestamp": 1630758901.00000000,
"msg": "mysqld: Can't open shared library '/tmp/galera/0/mysql/lib64/mysql/plugin/audit_log.so' (errno: 0, cannot open shared object file: No such file or directory)"
},
{
"timestamp": 1630758901.00000000,
"msg": "Couldn't load plugins from 'audit_log.so'."
}
],
"warnings": [
{
"timestamp": 1630758902.00000000,
"msg": "/tmp/galera/0/mysql/sbin/mysqld: unknown option '--loose-skip_mysqlx'"
},
{
"timestamp": 1630758902.00000000,
"msg": "/tmp/galera/0/mysql/sbin/mysqld: unknown variable 'loose-log_error_verbosity=3'"
},
{
"timestamp": 1630758902.00000000,
"msg": "/tmp/galera/0/mysql/sbin/mysqld: unknown variable 'loose-audit_log_file=/tmp/galera/0/mysql/var/audit.log'"
},
{
"timestamp": 1630758902.00000000,
"msg": "'proxies_priv' entry '@% root@void' ignored in --skip-name-resolve mode."
}
],
"status": {
"state": "DISCONNECTED",
"comment": "Disconnected",
"progress": -1.00000
}
}

As you can see, we can easily check the status object. If something goes wrong, hopefully, we will see informative error and warning lists.

See MDEV-26971 for details.

Galera now supports OpenSSL 3.0. See MDEV-25949 to get more information on it.

The mariadb-binlog utility has new --do-domain-ids , --ignore-domain-ids and --ignore-server-ids options. mariadb-binlog allows us to read the binary log in an understandable way for both humans or programs and allows to filter events in various ways. These new variables are useful to filter events by their GTID.

See MDEV-20119 to learn more.

Storage engines

MariaDB delegates physical data reads and writes to special plugins called storage engines. The default storage engine is InnoDB, other engines are only useful in special cases.

InnoDB

innodb_log_file_size is now dynamic! You may think that this is not a huge improvement. But innodb_log_file_size is the second most important setting for InnoDB. When it’s undersized, writes can be super slow. Previously, changing it required shutting down MariaDB properly, manually deleting the old log files, and then restarting MariaDB. It was also a risky task, because if you delete the log files without shutting down MariaDB cleanly, your database will become unusable.

Being able to resize innodb_log_file_size without even restarting MariaDB is a historical change.

See MDEV-27812 to read more about it.

innodb_change_buffering is now deprecated and ignored.

FULLTEXT searches can now find words containing apostrophes, like O'Brien (MDEV-20797). This already worked with Aria and MyISAM tables.

Several performance optimisations: MDEV-27557MDEV-28185MDEV-27767MDEV-28313MDEV-28137MDEV-28465MDEV-26789.

FreeBSD and DragonflyBSD users may notice a concurrency improvement. On other systems, InnoDB has already used futexes or futex-like APIs. Starting from 10.9, InnoDB also uses futex-like system calls on FreeBSD and Dragonfly. This is described in MDEV-26476.

SPIDER

SPIDER reads and writes to remote MariaDB servers in a transparent way. Among other use cases, it is useful for data sharding.

The ongoing work to simplify and refactor the SPIDER code is progressing with this version. Some SPIDER settings were removed. SPIDER doesn’t have its own version number anymore ( spider_version ), it adopts MariaDB version numbers instead.

There was also an undocumented, difficult-to-use SPIDER high availability feature, which has now been removed.

CONNECT

The CONNECT engine allows to use external data sources as regular tables, including remote databases, data files in several formats, and more.

CONNECT now supports INSERT IGNORE . In certain circumstances, this makes it much easier to add multiple rows to a data file or a remote database.

EXPLAIN

SHOW EXPLAIN now reports ANALYZE of a running query, optionally with FORMAT=JSON (MDEV-27021).

EXPLAIN FOR CONNECTION is now a synonym for SHOW EXPLAIN , added for MySQL compatibility (MDEV-10000).

MariaDB Automation

HashiCorp Vault can be used with MariaDB in many ways. This requires some configuration work though. MariaDB 10.9 comes with a handy HashiСorp Key Management plugin which can be used to automate key rotation for Data At Rest encryption, also known as encryption of data that is written to the disk. See MDEV-19281 to learn more.

Other plugins are also available and can be helpful. However, it’s worth mentioning that Vault can be used with any technology (SSH keys, etc) and is independent from any cloud vendor. You can use it to avoid cloud vendor lock-ins. It can also be very important for multi-cloud or hybrid environments.

Conclusions

I have listed the most useful features of MariaDB 10.9, in my opinion.

Here I need to remind you again that MariaDB 10.9 is a short-term support release. It will be discontinued in one year since its first stable release, in August 2023. While it is helpful for testing new features, most users shouldn’t use it in production. The latest long-term support release is 10.6.

If you want to take advantage of the very recent and long-known MariaDB performance optimisations, consider the MariaDB Health Check service from Vettabase.

Federico Razzoli

A proposito di Federico Razzoli

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

2 Replies to “A summary of MariaDB 10.9: Vault integration, innodb_log_file_size and more”

  1. Federico, thank you for this update! A small correction: Starting with MySQL 5.6.8, InnoDB is able to resize the log on startup.

    It was never a good idea to manually delete any persistent InnoDB files, but I think that it was the only option to resize the log before MySQL 5.6.8. Starting with MDEV-27199 in MariaDB Server 10.8, InnoDB will no longer happily create the log file if it is missing.

    • Hi Marko, thanks for the information!
      But you mention MySQL 5.6.8, and MDEV-27812 mentions MariaDB Enterprise 10.5. So if we talk about MariaDB community specifically, am I correct that resizing innodb_log_file_size dynamically is only possible since 10.9?
      And I should point out that resizing the logs is not possible at all in 10.8.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.

*