10.7 preview feature: InnoDB Bulk Insert

Everyone wants performance. Speeding up the insert into empty InnoDB tables and partitions is a common enough use case to optimize. Thirunarayanan Balathandayuthapani from the MariaDB Corporation wrote this improvement to the bulk inserting into InnoDB tables that is available as a preview for testing.

To achieve this, if the table is empty then InnoDB will pre-sort the records for each index, and build the indexes one page at a time. If the transaction does bulk insert operation then InnoDB should create buffer of size innodb_sort_buffer_size for each index.

If the buffer ran out of memory then InnoDB will create a temporary file for storing the data.

10.7 feature preview: Miscellaneous features

The “Misc features” preview includes all the other features that did not make it into a separate dedicated preview binary.


MariaDB Server 10.7 includes the JSON_EQUALS function, which compares inputs as JSON objects, regardless of whitespace, key order, or number format.

(more…)

10.7 preview feature: Password Reuse Check plugin

By default, MariaDB does not check if a user reuses a password. Some security policies require users to choose a new password each time, and the Password Reuse Check plugin, available in a MariaDB 10.7.0 preview, enables this functionality.

Old passwords are stored in the mysql.password_reuse_check_history table, and the period they are retained for is determined by the password-reuse-check-interval system variable, which specifies a number of days. By default this is zero, meaning unlimited retention.

The password can be used in conjunction with other password validation plugins, such as the Simple Password Check plugin or the CrackLib Password Check plugin.

10.7 preview feature: CONVERT PARTITION

If you are using table partitioning, you have likely heard of the ALTER TABLE … EXCHANGE PARTITION … WITH TABLE … command. It existed in MariaDB since forever. But if you check the manual (any manual) or search the web, you will see that almost the only use case of it is converting a partition to a standalone non-partitoned table, or converting a standalone non-partitoned table into a partition.

And the usage was designed back then to be anything but obvious. To convert a partition to a table you need first to create an empty table with the same structure as a partition, then you exchange it with a partition, and then you drop the empty partition.

10.7 preview feature: Python-like string formatting

Sometimes there is a need to combine data from different columns into one string. For example,

SELECT CONCAT(first_name, ‘ ‘, last_name) FROM employees;

This doesn’t look too bad, but can quickly get out of hand, if you need to do something more complex than that. For example, let’s say, we also need to mention the salary here:

SELECT CONCAT(first_name, ‘ ‘, last_name, ‘ -‘, CAST(FORMAT(salary, 0) AS VARCHAR(10)), ‘ ‘, currency) FROM employees;

This preview shows a new feature for MariaDB 10.7 that was developed as a part of Google Summer of Code (MDEV-25015) by Alan Cueva, together with his mentor Vicențiu Ciorbaru.

10.7 preview feature JSON Histograms

MariaDB has had support for histograms as part of Engine Independent Table Statistics since 10.0. As part of Google Summer of Code (MDEV-21130), Michael Okoko, together with his mentor Sergey Petrunia, have implemented a new format (using JSON) for histograms that significantly improves the accuracy and flexibility of histograms. For those just interested in the feature details, you can skip to the “New format”, however if one is unfamiliar with the purpose of histograms, read on.

Why statistics are needed

Histograms are important for queries where the WHERE clause uses columns that are not indexed.

10.7 preview feature: Compression Provider Plugins

MariaDB has been using a pluggable storage engine architecture for a long time and whilst this means great flexibility in choosing and managing the right storage engines for specific use cases, it also means they are easier to develop and therefore there’s an expectation that more engines will be created.

More storage engines means the MariaDB Server itself needs to be as flexible as possible to accommodate all sorts of functionalities that storage engines may need. One area where the MariaDB Server proved to be not that welcoming was in making available all the compression libraries needed by storage engines.

10.7 preview feature: Natural sort

Natural sort order is the ordering of strings in alphabetical order, while numbers are treated as numbers. This understanding of sorting is closer to human comprehension than to a machine. You can find an example of this feature in the Windows file manager. There the files are sorted in natural order. Try to create four folders “b1”, “a11”, “a2”, “a1”.

There are several programming languages which have natural sort. In PHP it is built-in function natsort, while as an third-party module in Python it is natsort, in Perl it is Sort::Naturally and in Matlab it is sort_nat.