What’s New in MariaDB Server 10.5?

MariaDB Server 10.5 is a fresh, new, and stable version from MariaDB that was released on June, 24th 2020. Let’s take a look at the features that it will bring us.

More Granular Privileges

With MariaDB 10.5 some changes regarding the privileges are coming. Mainly, SUPER privilege has been split into several new privileges that allows to set more granular control over what actions are allowed for given users and what are not. Below is the list of the new privileges that are available in MariaDB 10.5:

• BINLOG ADMIN
• BINLOG REPLAY
• CONNECTION ADMIN
• FEDERATED ADMIN
• READ_ONLY ADMIN
• REPLICATION MASTER ADMIN
• REPLICATION SLAVE ADMIN
• SET USER

InnoDB Performance Improvements

MariaDB 10.5 comes with a list of performance improvements for InnoDB. What is important to know is that MariaDB 10.5 has embedded InnoDB from MariaDB 10.4. There are going to be performance modifications and improvements but the core of InnoDB is the same as in MariaDB 10.4. This is very interesting to see how the path MariaDB has chosen will bring in terms of the performance. On one hand, sticking to the old version allows faster release cycles for MariaDB – porting totally new InnoDB to MariaDB would be quite a challenge and, let’s be honest, may not be really feasible to accomplish. Please keep in mind that MariaDB becomes more and more incompatible with the upstream. It’s been a while since the last build where you could just swap binaries and everything would work without any issues. 

MariaDB developed its set of features like encryption or compression, making those implementations not compatible. On the other hand, new InnoDB has shown significantly better performance than MariaDB 10.4. Lots of lines of code have been written (and lots of lines of code have been removed) to make it more scalable than the previous version. It will be very interesting to see if MariaDB 10.5 will be able to outperform its concurrents.

We will not be getting into details as this is what you can find on MariaDB website, we’d like to mention some of the changes. InnoDB redo logs have seen some work making them more efficient. InnoDB buffer pool has also been improved to the extent that the option to create multiple buffer pools has been removed as no longer needed – performance challenges it was aimed to fix had already been fixed in 10.5 thus making this option not necessary.

What is also important to keep in mind is that InnoDB in 10.5 will be, due to the changes, will be incompatible with InnoDB in 10.4. The upgrade will be one-way only, you should plan your upgrade process accordingly.

Full GTID Support for Galera Cluster

Galera Cluster will come in MariaDB 10.5 with full GTID support. This should make the mixing of Galera Cluster and asynchronous replication more seamless and less problematic.

More Metadata for Replication and Binary Logs

Talking about replication, MariaDB 10.5 has improved binary log metadata. It comes with more information about the data being replicated:

• Signedness of Numeric Columns
• Character Set of Character Columns and Binary Columns
• Column Name
• String Value of SET Columns
• String Value of ENUM Columns
• Primary Key
• Character Set of SET Columns and ENUM Columns
• Geometry Type

This should help to avoid replication issues if there are different schemas on master and on the slave.

Syntax

Several changes in SQL syntax have been introduced in MariaDB 10.5. INTERSECT allows us to write a query that will result in rows that are returned by two SELECT statements. In MariaDB 10.5 INTERSECT ALL has been added, which allows to return a result set with duplicate values. Similarly, EXCEPT has been enhanced to allow for EXCEPT ALL.

Couple of changes have been made into the ALTER syntax – you can now rename columns with ALTER TABLE … RENAME COLUMN. It is also possible to rename index using ALTER TABLE … RENAME KEY syntax. What’s quite important, both ALTER TABLE and RENAME TABLE received a support for IF EXISTS, it will definitely help in terms of replication handling.

Performance Schema Updates to Match MySQL 5.7 

Performance Schema tables have been updated so that they will be on par with Performance Schema from MySQL 5.7. This means changes in instrumentation related to memory, metadata locking, prepared statements, stored procedures, locking, transactions and user variables.

Binaries Named mariadb

Last but not least, binaries have been changed from ‘mysql’ to ‘mariadb’. The old naming convention, however, can still be used to keep the compatibility with existing scripts and tools.

On top of that, several other changes have been introduced. JSON array and object aggregation function, improved instrumentation for the connection pool, improvements in the query optimizer or migration to new version of library for regular expressions. Integration with S3 has also been introduced – you can read data from S3 buckets from within MariaDB 10.5.

We are looking forward to seeing how this new MariaDB version will look like in production environments. If you are interested in trying, migration instructions are available on MariaDB website