Schedule Meeting

a

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

by | Sep 6, 2022 | MariaDB, MariaDB Features

Need Help?  Click Here for Expert Support

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.

[showlayout id=266444]

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

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

Validating rows with CHECK constraints in MariaDB

Validating rows with CHECK constraints in MariaDB

Relational databases provide several ways to validate data. CHECK constraints are a powerful tool for in-database data validation. Their impact on performance is minimal, if any. In this article we'll discuss MariaDB support for CHECK constraints. Note that the CHECK...

MariaDB ColumnStore SQL limitations

MariaDB ColumnStore SQL limitations

MariaDB ColumnStore is an extremely fast and scalable solution for analytics built on MariaDB, which Vettabase supports. MariaDB ColumnStore inherits the MariaDB SQL dialect, and many MariaDB features. However, numerous MariaDB features are not available for the...

Installing MariaDB ColumnStore on Ubuntu, for production

Installing MariaDB ColumnStore on Ubuntu, for production

Let's see how to install MariaDB ColumnStore (single node) on Ubuntu. Let's also prepare the system to run ColumnStore with good performance, as we should always do in production. The following steps should work on any modern Ubuntu version up to and including 24.04...

Services

Need Help?  Click Here for Expert Support

2 Comments

  1. Marko Mäkelä

    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.

    Reply
    • Federico Razzoli

      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.

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *