The most noteworthy improvements in MariaDB 10.11

Improving on its predecessor, MariaDB 10.6, MariaDB 10.11 is the latest long-term stable series. It was released in February 2023 with ClusterControl support introduced in August of the same year. So what’s new? We’ll take a look at updates in 5 key areas: security, the InnoDB engine, temporal tables, general performance optimizations, and system variables.

3 key MariaDB 10.11 security updates

Addition of PUBLIC pseudo-role

The PUBLIC pseudo-role allows the following SQL statements to be implemented:

• GRANT TO PUBLIC – Grants some privileges to all users.
• REVOKE FROM PUBLIC – Revokes privileges from PUBLIC. However, roles and users that have explicitly been granted those privileges will retain them.
• SHOW GRANT FOR PUBLIC – Shows the GRANT statement that can be used to restore PUBLIC permissions.

A typical use case for PUBLIC would be an instance where you want all users to have UPDATE privileges on a certain table.

Read-only Admin

Usually in replicas, we set the read_only=1 to make MariaDB instances read-only to ensure that no accidental writes are done on replicas. However, there are instances where a DBA might need to change data on replicas due to inconsistencies with the primary. In previous MariaDB versions, the SUPER or READ-ONLY ADMIN privileges were required to write data into a replica. Now, to write on read-only instances, the READ-ONLY ADMIN privilege is necessary and the SUPER privilege is not allowed to.

Passwordless local authentication in Windows

MariaDB’s unix_socket plugin in Linux systems allows the binding of one system user to MariaDB users. This allows for passwordless authentication for a system user to the MariaDB instance, eliminating the need for double authentication (MariaDB and system). With MariaDB 10.11, the  GSSAPI authentication plugin is included in the server — this allows for the same passwordless local authentication in Windows.

InnoDB performance enhancements 

Some InnoDB performance enhancements were also introduced via easier configuration tuning.

Undo tablespaces

innodb_undo_tablespaces determines the number of InnoDB undo logs. In previous MariaDB versions, the default value was 0, which meant the undo log was written into the system tablespace. However, with MariaDB 10.11, the default value is 3. This means that there are three undo logs, and each is written in the defined innodb_undo_directory. The most significant aspect is that in previous versions the innodb_undo_tablespaces value could not be changed after database creation and one wouldn’t try to find the optimal value..

Background IO threads are now dynamic

The InnoDB background IO threads are represented by the innodb_write_io_threads and innodb_read_io_threads variables. In the previous versions, these variables were not dynamic and changes required a MariaDB restart. In MariaDB 10.11 these variables are now dynamic and do not require a restart.

Other InnoDB changes

• Fast bulk INSERTS.
• innodb_change_buffering is now deprecated and ignored.
• innodb_log_file_size is now dynamic.
• innodb_buffer_pool_chunk_size is now allocated dynamically.
• InnoDB Redo Log format was modified to reduce write amplification.
• FULLTEXT searches can now find words with apostrophes, like O’Connor.
• Better concurrency on FreeBSD and DragonflyBSD.

Temporal tables improvements

MariaDB introduced temporal tables in 2018. MariaDB 10.11 includes improvements on a temporal table called system-versioned tables. These tables use row versioning which is controlled by MariaDB automatically. These tables preserve past data that allows running queries on the tables to show how data has evolved or was at a certain point in time.

Previously, system-versioned tables’ history could not be modified until now thus it was impossible to take mariadb-dump and restore it. The following changes have been made:

• mariadb-dump now dumps system-versioned tables history.
• The system_versioning_insert_history variable was added. It is set to OFF by default. But it’s dynamic and, if enabled, allows the insertion of past versions rows, with specified timestamps. Without this option, we could take a dump but not restore it.

General performance optimizations

The query optimizer

• DELETEs and UPDATEs with semi-joins are now properly optimized. Previously, it was recommended to use multi-table syntax with DELETE and UPDATE. 
• EXPLAIN FORMAT=JSON now returns the time spent by the optimizer to determine an execution strategy.

Information_schema

These are some of the improvements done to the information_schema database which contains informational systems tables. In previous versions, queries executed on PARAMETERS and ROUTINES run with a full table scan, even if there was a WHERE clause used that was specific and efficiently used an index — this behavior has been modified.

Previously, queries executed on PARAMETERS and ROUTINES loaded all examined procedure codes, which translated to slowness, especially in the case of a full table scan. Now, if the query only returns procedures’ and parameters’ names, the procedure codes are not loaded.

Existing system variables enhancements

Slow log settings

One issue with variable names is that they are not always grouped cleanly. From MariaDB 10.11, we can see all variables that affect the slow query log by running SHOW VARIABLES LIKE ‘slow_log%’;. The slow_log prefix has been added to the following variables:

• min_examined_row_limit
• slow_query_log
• slow_query_log_file
• Long_query_time

The replicate_rewrite_db variable

To instruct a replica to change a database name we use replicate_rewrite_db. This is often done to replicate a test database. Previously, this was a startup option that could be specified on startup. This implies that the service configuration must be modified which is not a good idea. Services should only be modified to change the start/stop/restart logic. Now, the corresponding variable exists as a dynamic system variable.

Wrapping Up

The above are some of the more interesting features introduced in MariaDB 10.11. If you want to see the full list of improvements, you can go here. 

With ClusterControl, you can easily upgrade to MariaDB 10.11 stress-free. Go to our MariaDB on ClusterControl page to see all of the ops features available to you. Once familiar, see how ClusterControl gives you unprecedented control in administering MariaDB in any environment — try our free 30 day trial, no CC required. In the meantime, stay up to date with all the latest news and best practices for the most popular open-source databases by following us on Twitter and LinkedIn, or subscribing to our monthly newsletter below.