Announcing MariaDB Community Server 11.5 RC
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 sessionmax_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