Announcing MariaDB Community Server 11.5 RC

Posted on May 30, 2024 by
spacer

We are pleased to announce the release candidate (RC) of MariaDB Community Server 11.5. Our next release series includes plenty of changes which can be useful for DevOps and DBAs, and more.

Increasing Productivity for DevOps and DBAs

With MariaDB Server 11.5 DevOps and DBAs get new options to have more control over the disc storage used for temporary files. They also get more insights into server metrics and load via new or enhanced tables in the information schema.

Option To Limit Disk Space Used For Temporary Files And Tables

When internal in-memory temporary tables are reaching a memory limit, they need to be stored on disk in directories defined via the tmpdir system variable.

Two new system variables have been added to define the maximum storage to be used for such temporary tables and other internally created temporary files:

  • max_tmp_session_space_usage – to limit the disc storage used per session
  • max_tmp_total_space_usage – to limit the total disc storage used by the MariaDB Server instance

Any query which does result in exceeding the limit of temporary storage will return with an error.

Two new status variables can be used to monitor the currently used storage:

  • tmp_session_space_used
  • Tmp_total_space_used

The space used per session is also shown in the view process_list in the information schema.

New Information Schema Table For Password Related Data

A new information Schema view, USERS, has been added, which DBAs can use to get insights about password related information for a user. This information can be used:

  • by an application to inform a user about a password about to expire or an account which is at risk of being blocked due to the number of wrong passwords entered
  • by DBAs to query users which have been blocked because of too many invalid passwords entered

The new view includes the fields:

  • USER – A string including user name and host
  • PASSWORD_ERRORS – A counter with the current number of wrong passwords entered
    • Reset to 0 when a correct password has been entered
    • An account is blocked, if max_password_errors is reached
    • NULL for accounts with privilege CONNECTION ADMIN
  • PASSWORD_EXPIRATION_TIME – The date and time when the password expires or NULL, if the password never expires

New Information Schema Table SEQUENCES

The new information schema table SEQUENCES includes details about created sequences, to allow DBAs to gather information about already created sequences via SQL.

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES\G
*************************** 1. row ***************************
       SEQUENCE_CATALOG: def
        SEQUENCE_SCHEMA: test
          SEQUENCE_NAME: s_name
              DATA_TYPE: tinyint
      NUMERIC_PRECISION: 8
NUMERIC_PRECISION_RADIX: 2
          NUMERIC_SCALE: 0
            START_VALUE: 100
          MINIMUM_VALUE: -127
          MAXIMUM_VALUE: 126
              INCREMENT: 10
           CYCLE_OPTION: 0

Enhancements to Statistics Monitoring with Plugin “Userstat”

The plugin “Userstat” adds tables to the Information Schema when loaded. Capturing statistics can be activated by:

SET GLOBAL userstat=1;

The plugin provides statistics which can help analyze details per table, index, user, and client. Some enhancements have been made to the collected statistics:

Remove Statistics Collection for System Tables

The collected statistics do not include statistics for system tables any more, like the performance and information schemas.

Easier Check Of Index Effectivity

The plugin “Userstat” now includes a counter QUERIES for the number of queries in table INDEX_STATISTICS, which is incremented for each query the index is part of. A relatively low number indicates that the index is not used very often and is thus a candidate  to be removed.

More Query Type Information In The Table TABLE_STATISTICS

New counters have been added to table TABLE_STATISTICS:

  • ROWS_INSERTED
  • ROWS_DELETED
  • ROWS_UPDATED
  • KEY_READ_HITS
  • KEY_READ_MISSES

These new fields enable getting a better understanding of the operations done on a table.

New Client And User Statistics

The tables CLIENT_STATISTICS and USER_STATISTICS, which are used to analyze per client (host) and per user load, now include the counters:

  • KEY_READ_HITS
  • KEY_READ_MISSES

 

Changes To Data Types And Character Sets

Extended TIMESTAMP Maximum Value

For 64 bit systems the maximum value for the TIMESTAMP data type has been extended from ‘2038-01-19 03:14:07 UTC’ to ‘2106-02-07 06:28:15 UTC’ without changing the storage format.

System versioned tables use the maximum value of TIMESTAMP for their row_end field to indicate a currently valid value. These values should be changed changed to the new maximum value:

  • ALTER TABLE can be used for this conversion
  • A new option –update-history for mariadb-dump allows converting row_end values to the new maximum while running a dump

Default Unicode Collation Changed To ‘uca1400_ai_ci’

The character sets utf8mb3, utf8mb4, ucs2, utf16 and utf32 now by default use the collation ‘uca1400_ai_ci’, which enables proper support for SMP characters including emojis.

 

New Tooling Options

MariaDB Dump – Dump Databases To Separate Directories

The new option --dir=<path> can be used with mariadb-dump to dump databases to their own sub-directory inside of the directory defined by --dir=<path>. Each sub-directory includes a .sql file with DDL statements and a .txt file with tab separated data.

The new option was added to allow dumping multiple databases in parallel. To do that use it with the options --all-databases or --databases, and --parallel.

 

Miscellaneous

New Information Provided By ANALYZE FORMAT=JSON

ANALYZE FORMAT=JSON for a table now shows new information:

  • r_index_rows – A counter for the number of enumerated index tuples before any checks are made
  • r_icp_filtered – The percentage of records left after a pushed index condition check

GTID Binlog Events Now Include Thread ID

The thread ID and the corresponding statement can now be retrieved from binary logs.

The output of mariadb-dump also includes the thread ID.

 

Resources

Download MariaDB Community Server 11.5 RC at mariadb.com/downloads