Variables and Status Info

MariaDB system variables and status information are laws unto themselves. New variables are constantly being added; sometimes a few old ones drop away. The following is a brief overview of some system variables and status information not yet discussed that have changed with MariaDB 10.3.

The secure_timestamp variable influences how and whether a connection can change its local session timestamp. The following InnoDB system variables have been dropped and are therefore banned from appearing in the MariaDB configuration file. If you do not remove these variables from my.cnf, the MariaDB instance will not start after the upgrade:

• innodb_file_format
• innodb_file_format_check
• innodb_file_format_max
• innodb_large_prefix
• innodb_mtflush_threads
• innodb_use_mtflush

Feature status information shows which features MariaDB has used and how often. New variables are:

• Feature_system_versioning
• Feature_invisible_columns
• Feature_json
• Feature_custom_aggregate_functions

The table open cache (TOC) implements all status information already available in MySQL 5.7 to better monitor the behavior of the TOC:

• Table_open_cache_active_instances
• Table_open_cache_hits
• Table_open_cache_misses
• Table_open_cache_overflows

Finally, the innodb_buffer_pool_load_incomplete status information indicates whether loading of the InnoDB buffer pool has been completed or is still running after the restart of the instance.

Logging

MariaDB has two powerful logfiles: the general query log for all queries and the slow query log for slow queries. Both logfiles have new exclusion criteria that define what types of statements should not be logged. The log_disabled_statements variable with its possible values slave and sp (stored procedures) is responsible for the general query log, whereas the log_slow_disabled_statements variable with its possible values admin, call, slave, and sp is responsible for the slow query log.

Another way to set filters for the slow query log is to use the log_slow_filter variable. Queries that use priority queue optimization for file sort operations can now also be logged with the filsort_priority_queue variable. These queries are then also potential candidates for query tuning.

The information_schema database (also called I_S) has also improved slightly. Its tables have been optimized and are now expected to consume far less memory. System-Versioned Tables and Columns are now visible in the corresponding I_S.TABLES and I_S.COLUMNS tables, with the I_S.CHECK_CONSTRAINTS table already introduced with MariaDB 10.2, as well.

Replication

Some changes for semi-synchronous replication have appeared after a long wait. Alibaba has built in the Semisync plugin, so the functionality has now migrated directly into the server code. This step is intended to improve performance.

In asynchronous replication, two new variables have been added that the admin can use to control for which errors the system should try again to execute a command (slave_transaction_retry_errors) and how long it should wait before trying (slave_transaction_retry_interval). This change comes from the Spider developers camp.

Three status variables – Rpl_transactions_multi_engine, Transactions_gtid_foreign_engine, and Transactions_multi_engine – can be employed to determine whether transactions have been executed using different transactional storage engines (InnoDB, RocksDB, TokuDB) and whether it might make sense to use one table for each storage engine with the variable gtid_pos_auto_engines, which updates the GTIDs.

On the Galera cluster side, the plugin has been updated to the latest version. With the state snapshot transfer (SST) rsync method you can use stunnel, if available, to achieve data-in-transit encryption. The new variable wsrep_reject_queries makes it possible to reject requests with an error message while waiting for a node.