System Variables for MariaDB Xpand
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Features
Topics on this page:
Overview
MariaDB Xpand provides system variables for configuration and monitoring:
Global and session scope are supported
Session scope overrides the global value for the duration of a specific session
All system variables provide global scope
Only some system variables provide session scope
Compatibility
The detail provided here applies to:
MariaDB Xpand 5.3
MariaDB Xpand 6.0
MariaDB Xpand 6.1
Displaying Variable Values
To display values for global or session variables, use the following syntax:
SHOW [GLOBAL | SESSION] VARIABLES [LIKE pattern]
SHOW GLOBAL VARIABLES [LIKE pattern] [WITH DEFAULTS]
The WITH DEFAULTS
option shows whether the variable is DEFAULTED
, which specifies that the global variable will always be set to the default value recommended by Xpand. If Xpand determines that a variable's value should be set to a different default value, variables with DEFAULTED = 1
will automatically be modified as part of an upgrade.
Finding Non-Default Globals
The following query will list of variables whose values differ from the default, excluding variables whose values are not subject to defaults:
sql> SELECT name, value, default_value
FROM system.global_variables
JOIN system.global_variable_definitions USING (name)
WHERE value != default_value
AND name NOT IN
('cluster_id', 'cluster_name', 'clustrix_version', 'customer_name', 'format_version',
'global_variables_ignored_version', 'license', 'mysql_port', 'server_id',
'ssl_cert', 'ssl_key', 'view_strmaps_upgraded');
Setting Variable Values
To set a global or session variable to a specific value:
SET [GLOBAL | SESSION] variable_name = desired_value;
To modify a global variable to always use the Xpand-recommended default value (DEFAULTED = 1
):
sql> SET GLOBAL variable_name = DEFAULT;
To set a global variable to a default value with DEFAULTED = 0
:
SET GLOBAL variable_name = actual_default_value;
If a session system variable is modified, the value remains in effect within your session until the session ends or the variable is set to another value. No other sessions are affected.
If you change a global variable, the value is applied to new sessions, but not sessions that are already open (including the session where the SET GLOBAL
statement occurred).
For the majority of workloads, Xpand recommends retaining the default values for global variables.
Please contact MariaDB Support with specific questions about modifying any of the default values as the product does not warn of inadvisable settings.
Supported System Variables
Name | Description | Default Value | Session Variable |
---|---|---|---|
| Hold alter statement completion until all transactions prior to alter have committed. |
| Yes |
| Specifies the implicit locking strategy for |
| Yes |
| Each statement will be its own transaction, and automatically applied to the database. |
| Yes |
| If enabled, when a transaction is interrupted by a group change or encounters a retriable error, the database will automatically retry some in-process transactions. Only transactions that were submitted with autocommit = 1 or the first statement of an explicit transaction are retried. Stored procedure and function calls are never retried. If the retried statements are not executed successfully, the application will receive an error. |
| Yes |
| Amount in which auto increment values increase by, by default. |
| Yes |
| Value where auto increment values start at by default. |
| Yes |
| The number of tables that can be backed up simultaneously. |
| |
| The maximum number of slices restored concurrently on each node. |
| |
| Compression level from 1 (fastest) to 9 (best compression) |
| |
| Send a warning alert if bigc doesn't move for this long (0 = disabled) |
| |
| If bigc is pinned, send a warning alert this often (0 = disabled) |
| |
| Always NONE. Xpand masters do not support generating event checksums. |
| |
| Force all binlogs to log in this format, unless set to 'DEFAULT'. Valid values : statement, row. |
| Yes |
| Dummy variable for compatibility. |
| |
|
| Yes | |
|
| Yes | |
| Dummy variable for compatibility. Must be utf8. |
| Yes |
|
| Yes | |
| The default character set for databases when they are created |
| Yes |
| 64-bit cluster id | <auto populated> | |
| Name of the cluster | <auto populated> | |
| Xpand software version | <auto populated> | |
|
| Yes | |
| The collation used by the default database. This value cannot be modified. |
| Yes |
| The default collation for databases when they are created |
| Yes |
| Number of values per frame. Available in Xpand 6.0 and later. |
| Yes |
| Number of values to add to a columnar extent before creating a new one. Available in Xpand 6.0 and later. |
| Yes |
Force consistent ordering. See Guide. |
| Yes | |
| Used for costing columnar reads. |
| Yes |
| This should be set on cluster formation. Used in Alerts / Warnings. |
| |
| Database almost full message interval in seconds. |
| |
| Fail system queries when space usage surpasses this percentage. |
| |
| Warn about system queries when space usage surpasses this percentage. |
| |
| Fail user queries when space usage surpasses this percentage. |
| |
| Warn about user queries when space usage surpasses this percentage. |
| |
| Attempt to get and log conflicting transaction session and statement information on distributed deadlock detection. |
| |
| Automatically resize all (online) devices in the cluster to match the largest device |
| |
| Maximum number of bytes allowed to be used for temporary containers. |
| |
| Enable rule dist_ |
| |
| Enable rule groupby_ |
| |
| Enable rule scalar_ |
| |
| Enable rule scalar_ |
| |
|
|
| |
| Limit of nested or cyclic foreign key cascading |
| |
| Enable/Disable foreign key checks. |
| Yes |
| A version string indicating the version of the software when the cluster was initially created. | <auto populated> | |
| A read-only variable indicating whether COMPRESS() and UNCOMPRESS() are supported |
| |
| Automatically trigger a group change when a back end TCP connection is dropped |
| |
| Always OFF. Xpand masters do not support generating GTID events. |
| |
| Dummy variable for compatibility. (Xpand does not support replication with Global Transaction Identifiers.) | ||
| Enable the Completely Fair Scheduler. |
| |
| Rows to process before rescheduling. |
| |
| The default number of slices used when a table or index is created. Set this to 0 to automatically equal the number of the nodes in the cluster. |
| Yes |
| Are COMPRESS() and UNCOMPRESS() supported? |
| |
| Dummy variable for compatibility. |
| |
| Dummy variable for compatibility. | Yes | |
| Maximum allowed age for idle transactions. Specify 0 for no timeout. |
| Yes |
| Dummy variable for compatibility. |
| |
| Dummy variable for compatibility. Available in Xpand 6.0 and later. |
| |
| Dummy variable for compatibility. |
| Yes |
| If internode latency exceeds this, send a warning to clustrix.log. Setting to 0 turns warnings off. |
| |
| Milliseconds a node will wait to hear from another node before forcing a group change. Setting to 0 uses the system default. |
| |
| JDBC compliant truncation check. Removed in Xpand 6.1. |
| Yes |
| If the number of tables exceeds this value, swap filtering tables only |
| Yes |
| Dummy variable for compatibility. |
| |
| The relation to which we last inserted an auto_ |
| Yes |
| Dummy variable for compatibility. |
| Yes |
| The license string for the cluster | ||
| The maximum number of locks the lock manager will hold on each node in the cluster. |
| |
| The maximum number of locks a single transaction can hold on each node in the cluster. |
| |
| Acquire a read lock on the source data when using INSERT INTO...SELECT FROM statements. This is necessary for correct statement based replication. |
| |
| Milliseconds a query waits for a lock before timing out. |
| |
| Dummy variable for compatibility. |
| |
| Table names are stored in the case specified in the CREATE TABLE statement and name comparisons are not case sensitive. The value of this variable does NOT correspond to MySQL. |
| |
| Dummy variable for compatibility. |
| |
| Binlog used in SHOW MASTER STATUS when used without specifying a binlog. | Yes | |
| Maximum allowed query size |
| |
| The maximum number of connections allowed per node |
| |
| Number of nodes or zones that can fail simultaneously without losing data or the ability to resolve transactions |
| |
| number of old log files the memlog should keep |
| |
| maximum number of bytes to log in a single log message |
| |
| Maximum amount of memory usable by in-memory tables. |
| |
| Cease Sierra planner optimization attempts once we have used this many total MiB and attempt to continue with the best plan found so far. |
| |
| Halt the Sierra planner and return an error to the user if this many MiB have been consumed during the initial parse phase. This variable is independent of the other max_ |
| |
| Stop the sierra planner once the planner has been working this long on a plan. Specify 0 to disable this check. |
| |
| Halt the Sierra planner and return an error to the user once this many total MiB have been used. This value should always be at least 50MiB greater than max_ |
| |
| Halt the Sierra planner and return an error to the user if the working set memory exceeds this many MiB limit. This variable is independent of the other max_ |
| |
| The maximum allowed number of slices for a representation. |
| |
| The maximum allowed number of tables. Do not change. |
| |
| memlog will start a new file after this many hours (0 disables time-based rollover) |
| |
| memlog will start a new file if the current one exceeds this size (mb) |
| |
| Fail system writes when memory usage for in-memory tables surpasses this percentage. |
| |
| Warn about system writes when memory usage for in-memory tables surpasses this percentage. |
| |
| Fail user writes when memory usage for in-memory tables surpasses this percentage. |
| |
| Warn about user writes when memory usage for in-memory tables surpasses this percentage. |
| |
| Replicate databases not specified in mysql_ |
| |
| Replicate tables not specified in mysql_ |
| |
|
| Yes | |
| Default port for mysql access to Xpand. |
| |
| Maximum size of relay log in bytes a slave process is allowed to create. |
| |
|
| ||
| The reported MySQL server version |
| |
| Dummy variable for compatibility. |
| |
| Number of seconds to wait to finish receiving a message before closing the connection. |
| |
| Timeout in seconds if no data is received from a client to close the connection. |
| Yes |
| How many batches unused slave_ |
| Yes |
| Dummy variable for compatibility. |
| |
| Default port for mysql access to Xpand. |
| |
| Subnets using the proxy protocol | ||
| Dummy variable for compatibility. |
| |
| Dummy variable for compatibility. |
| Yes |
| Enable query fanout. This takes precedence over all other fanout variables. |
| Yes |
| Enable fanout for INSERT, UPDATE, and DELETE queries. Order of writes is not guaranteed. |
| Yes |
| Enable fanout for INSERT INTO ... SELECT FROM ... queries. This takes precedence over query_ |
| Yes |
| Sets the minimum number of rows for fanout, which is determined by the estimate in the query plan. If a query plan estimates that fewer rows will be read than the minimum, then fanout will not occur. |
| Yes |
| Enable/Disable read only mode. |
| |
| Maximum number of simultaneous rebalancer operations. |
| |
| Rebalance mode. |
| |
| Maximum number of operations that rebalancer_ |
| |
| Minimum coefficient of overall write load variation that will trigger rebalance activity. |
| |
| Queued replicas count as healthy for this many seconds, to give missing nodes the chance to come back online before rebalancer_ |
| |
| Default size at which the rebalancer splits slices. |
| |
| Maximum number of simultaneous rebalancer operations targeting one device. |
| |
| Server ID for Xpand as Replication Master. |
| |
| Session ID for client connections. |
| Yes |
| Log BAD queries to the query.log. |
| Yes |
| Log DDL statements to query.log. |
| |
| Log ERROR statements to query.log. |
| |
| Log SLOW statements to query.log. |
| |
| Query duration threshold in milliseconds before logging this query. |
| Yes |
| Log users and LOGIN/LOGOUT to user.log. |
| |
| RSA private key file. Available in Xpand 6.0 and later. | ||
| RSA public key file. Available in Xpand 6.0 and later. | ||
| Enable skiplist containers for aggregates and sorting. |
| Yes |
| Enable usage of new-style static PDs |
| |
| Enable the predictive governor. |
| |
| Number of rows returned by a query before the predictive governor prevents execution. |
| |
| Maximum allowed packet size in bytes for the slave. |
| |
| Maximum size of slave slice buffer, in bytes. |
| |
| If true, and <col> is an auto_ |
| Yes |
| Log statements to binary logs. This variable can be set to FALSE on a per-session basis. |
| Yes |
| Do not log to the specified comma-separated binlogs. Available in Xpand 6.0 and later. | Yes | |
| Xpand provides limited support for SQL_ |
| Yes |
| Dummy variable for compatibility |
| Yes |
|
| Yes | |
| Dummy variable for compatibility. |
| Yes |
|
| Yes | |
| SSL public key certificate file |
| |
| SSL is enabled |
| |
| SSL private key file |
| |
| Dummy variable for compatibility. |
| |
| Time Zone. Must be set in Olson time zone format. See Guide. |
| |
| Milliseconds between runs of periodic task "rebalancer_ |
| |
| Milliseconds between runs of periodic task "rebalancer_ |
| |
| Milliseconds between runs of periodic task "rebalancer_ |
| |
| Milliseconds between runs of periodic task "rebalancer_ |
| |
| Milliseconds between runs of periodic task "rebalancer_ |
| |
| Milliseconds between runs of periodic task "rebalancer_ |
| |
| Kill the oldest transaction when available disk space goes below this percentage and available undo space goes below trxshoot_ |
| |
| Kill the oldest transaction when available undo space goes below this percentage and available disk space goes below trxshoot_ |
| |
| Maximum allowed age for transactions. Specify 0 for no timeout. |
| Yes |
| Controls when the client is notified of successful commit. 'RELAXED' is lower latency, but may result in 'committed' transactions being lost. Removed in Xpand 6.1. |
| Yes |
|
| Yes | |
| Concatenation of mysql_ | <auto populated> | |
| Timeout in seconds after data is sent to a client to close the connection. |
| Yes |
| Maximum allowed age for write transactions. Specify 0 for no timeout. |
| Yes |