System Variables for MariaDB Xpand

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

alter_hold_until_checkpoint

Hold alter statement completion until all transactions prior to alter have committed.

false

‌Yes

alter_lock

Specifies the implicit locking strategy for ALTER TABLE when the LOCK clause is not specified. Changing this variable can result in unexpected table locks. Choices: NONE, SHARED, AUTO

NONE

‌Yes

autocommit

Each statement will be its own transaction, and automatically applied to the database.

true

‌Yes

autoretry

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.

true

‌Yes

auto_increment_increment

Amount in which auto increment values increase by, by default.

1

‌Yes

auto_increment_offset

Value where auto increment values start at by default.

1

‌Yes

backup_backup_concurrency

The number of tables that can be backed up simultaneously.

1

backup_restore_concurrency

The maximum number of slices restored concurrently on each node.

16

backup_write_compression_level

Compression level from 1 (fastest) to 9 (best compression)

6

bigc_pin_message_interval_s

Send a warning alert if bigc doesn't move for this long (0 = disabled)

21600

bigc_pin_warn_threshold_s

If bigc is pinned, send a warning alert this often (0 = disabled)

0

binlog_checksum

Always NONE. Xpand masters do not support generating event checksums.

NONE

binlog_format

Force all binlogs to log in this format, unless set to 'DEFAULT'. Valid values : statement, row.

DEFAULT

‌Yes

binlog_row_image

Dummy variable for compatibility.

FULL

character_set_client

latin1

‌Yes

character_set_connection

latin1

‌Yes

character_set_database

Dummy variable for compatibility. Must be utf8.

utf8

‌Yes

character_set_results

latin1

‌Yes

character_set_server

The default character set for databases when they are created

utf8

‌Yes

cluster_id

64-bit cluster id

<auto populated>

cluster_name

Name of the cluster

<auto populated>

clustrix_version

Xpand software version

<auto populated>

collation_connection

latin1_swedish_ci

‌Yes

collation_database

The collation used by the default database. This value cannot be modified.

utf8_general_ci

‌Yes

collation_server

The default collation for databases when they are created

utf8_general_ci

‌Yes

columnar_frame_size

Number of values per frame. Available in Xpand 6.0 and later.

64

‌Yes

columnar_rows_per_extent

Number of values to add to a columnar extent before creating a new one. Available in Xpand 6.0 and later.

2097152

‌Yes

consistent_order

Force consistent ordering. See Guide.

false

‌Yes

cost_read_column

Used for costing columnar reads.

  • 0.9 (Xpand 6.0 and later)

  • 0.01 (Xpand 5.3)

‌Yes

customer_name

This should be set on cluster formation. Used in Alerts / Warnings.

<auto populated>

databasefull_message_interval_s

Database almost full message interval in seconds.

120

databasefull_system_error_percentage

Fail system queries when space usage surpasses this percentage.

97

databasefull_system_warn_percentage

Warn about system queries when space usage surpasses this percentage.

95

databasefull_user_error_percentage

Fail user queries when space usage surpasses this percentage.

90

databasefull_user_warn_percentage

Warn about user queries when space usage surpasses this percentage.

80

debug_deadlocks

Attempt to get and log conflicting transaction session and statement information on distributed deadlock detection.

false

device_auto_resize_to_largest

Automatically resize all (online) devices in the cluster to match the largest device

true

device_temporary_space_limit_bytes

Maximum number of bytes allowed to be used for temporary containers.

5368709120

enable_rule_dist_groupby_stream_aggregate_impl

Enable rule dist_groupby_stream_aggregate_impl to fire

  • true (Xpand 6.1)

  • false (Xpand 6.0 and earlier)

enable_rule_groupby_stream_aggregate_impl

Enable rule groupby_stream_aggregate_impl to fire

  • true (Xpand 6.1)

  • false (Xpand 6.0 and earlier)

enable_rule_scalar_stream_aggregate_impl

Enable rule scalar_stream_aggregate_impl to fire

  • true (Xpand 6.1)

  • false (Xpand 6.0 and earlier)

enable_rule_scalar_stream_aggregate_over_partial

Enable rule scalar_stream_aggregate_over_partial to fire

  • true (Xpand 6.1)

  • false (Xpand 6.0 and earlier)

expire_logs_days

Dummy variable for compatibility.

0

foreign_key_cascade_limit

Limit of nested or cyclic foreign key cascading

3

foreign_key_checks

Enable/Disable foreign key checks.

true

‌Yes

format_version

A version string indicating the version of the software when the cluster was initially created.

<auto populated>

have_compress

A read-only variable indicating whether COMPRESS() and UNCOMPRESS() are supported

YES

group_change_on_dead_connection

Automatically trigger a group change when a back end TCP connection is dropped

TRUE

gtid_mode

Always OFF. Xpand masters do not support generating GTID events.

OFF

gtid_purged

Dummy variable for compatibility. (Xpand does not support replication with Global Transaction Identifiers.)

gtm_schedule_til

Enable the Completely Fair Scheduler.

true

gtm_schedule_til_batch_rows

Rows to process before rescheduling.

100

hash_dist_min_slices

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.

0

‌Yes

have_compress

Are COMPRESS() and UNCOMPRESS() supported?

YES

have_query_cache

Dummy variable for compatibility.

NO

hostname

Dummy variable for compatibility.

‌Yes

idle_trx_timeout_s

Maximum allowed age for idle transactions. Specify 0 for no timeout.

120

‌Yes

innodb_flush_log_at_trx_commit

Dummy variable for compatibility.

1

innodb_strict_mode

Dummy variable for compatibility. Available in Xpand 6.0 and later.

true

interactive_timeout

Dummy variable for compatibility.

28800

‌Yes

internode_latency_warn_us

If internode latency exceeds this, send a warning to clustrix.log. Setting to 0 turns warnings off.

0

internode_timeout_ms

Milliseconds a node will wait to hear from another node before forcing a group change. Setting to 0 uses the system default.

  • 100 (Xpand 6.1)

  • 5000 (Xpand 6.0 and earlier)

jdbcCompliantTruncation

JDBC compliant truncation check. Removed in Xpand 6.1.

false

‌Yes

join_swap_threshold

If the number of tables exceeds this value, swap filtering tables only

20

‌Yes

language

Dummy variable for compatibility.

/usr/local/mysql/share/mysql/english

last_auto_increment_relation

The relation to which we last inserted an auto_increment value

0

‌Yes

lc_time_names

Dummy variable for compatibility.

en_US

‌Yes

license

The license string for the cluster

lockman_max_locks

The maximum number of locks the lock manager will hold on each node in the cluster.

5000000

lockman_max_transaction_locks

The maximum number of locks a single transaction can hold on each node in the cluster.

1000000

lock_on_insert_select

Acquire a read lock on the source data when using INSERT INTO...SELECT FROM statements. This is necessary for correct statement based replication.

false

lock_wait_timeout_ms

Milliseconds a query waits for a lock before timing out.

300000

log_bin

Dummy variable for compatibility.

ON

lower_case_table_names

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.

1

log_slave_updates

Dummy variable for compatibility.

TRUE

master_status_binlog

Binlog used in SHOW MASTER STATUS when used without specifying a binlog.

‌Yes

max_allowed_packet

Maximum allowed query size

16777216

max_connections

The maximum number of connections allowed per node

500

max_failures

Number of nodes or zones that can fail simultaneously without losing data or the ability to resolve transactions

1

max_memlog_keep_files

number of old log files the memlog should keep

30

max_memlog_message_bytes

maximum number of bytes to log in a single log message

1048576

max_memory_table_limit_mb

Maximum amount of memory usable by in-memory tables.

16

max_sierra_opt_mem_MiB

Cease Sierra planner optimization attempts once we have used this many total MiB and attempt to continue with the best plan found so far.

80

max_sierra_parse_mem_MiB

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_sierra_%_mem_MiB variables.

256

max_sierra_plan_s

Stop the sierra planner once the planner has been working this long on a plan. Specify 0 to disable this check.

10

max_sierra_tot_mem_MiB

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_sierra_opt_mem_MiB.

160

max_sierra_working_mem_MiB

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_sierra_%_mem_MiB variables.

512

max_slices

The maximum allowed number of slices for a representation.

2000

max_tables

The maximum allowed number of tables. Do not change.

2000

memlog_rollover_hours

memlog will start a new file after this many hours (0 disables time-based rollover)

0

memlog_rollover_size_MiB

memlog will start a new file if the current one exceeds this size (mb)

1024

memory_table_system_full_error_percentage

Fail system writes when memory usage for in-memory tables surpasses this percentage.

97

memory_table_system_full_warn_percentage

Warn about system writes when memory usage for in-memory tables surpasses this percentage.

95

memory_table_user_full_error_percentage

Fail user writes when memory usage for in-memory tables surpasses this percentage.

90

memory_table_user_full_warn_percentage

Warn about user writes when memory usage for in-memory tables surpasses this percentage.

80

mysql_default_db_replication_policy

Replicate databases not specified in mysql_slave_db_replication_policy.

true

mysql_default_table_replication_policy

Replicate tables not specified in mysql_table_replication_policy.

true

mysql_float_format

mysql51

‌Yes

mysql_port

Default port for mysql access to Xpand.

3306

mysql_relay_log_bytes

Maximum size of relay log in bytes a slave process is allowed to create.

  • 268435456 (Xpand 6.0 and later)

  • 67108864 (Xpand 5.3)

mysql_slave_batch_kb_limit

512

mysql_version

The reported MySQL server version

5.0.45

net_buffer_length

Dummy variable for compatibility.

16384

net_read_timeout

Number of seconds to wait to finish receiving a message before closing the connection.

30

net_write_timeout

Timeout in seconds if no data is received from a client to close the connection.

60

‌Yes

parallel_slave_unused_vrel_lifetime

How many batches unused slave_coord_relations will be kept for before eviction. Available in Xpand 6.0 and later.

100

‌Yes

performance_schema

Dummy variable for compatibility.

false

port

Default port for mysql access to Xpand.

3306

proxy_protocol_networks

Subnets using the proxy protocol

query_cache_size

Dummy variable for compatibility.

0

query_cache_type

Dummy variable for compatibility.

OFF

‌Yes

query_fanout

Enable query fanout. This takes precedence over all other fanout variables.

true

‌Yes

query_fanout_all_writes

Enable fanout for INSERT, UPDATE, and DELETE queries. Order of writes is not guaranteed.

false

‌Yes

query_fanout_insert_select

Enable fanout for INSERT INTO ... SELECT FROM ... queries. This takes precedence over query_fanout_all_writes.

true

‌Yes

query_fanout_min_rows

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.

5000

‌Yes

read_only

Enable/Disable read only mode.

false

rebalancer_global_task_limit

Maximum number of simultaneous rebalancer operations.

16

rebalancer_rebalance_mode

Rebalance mode. normal allows swapping slices between nodes for improved data distribution. noswap allows the rebalancer to move slices to even out distribution, but will not swap slices between nodes. The noswap option is available in Xpand 6.0.6 and later and Xpand 6.1.0 and later.

  • noswap (Xpand 6.1.1 and later)

  • normal (Xpand 6.1.0 and earlier)

rebalancer_rebalance_task_limit

Maximum number of operations that rebalancer_imbalanced and rebalancer_rebalance_distribution will each schedule at once.

2

rebalancer_rebalance_threshold

Minimum coefficient of overall write load variation that will trigger rebalance activity.

0.05

rebalancer_reprotect_queue_interval_s

Queued replicas count as healthy for this many seconds, to give missing nodes the chance to come back online before rebalancer_reprotect starts copying.

600

rebalancer_split_threshold_kb

Default size at which the rebalancer splits slices.

8388608

rebalancer_vdev_task_limit

Maximum number of simultaneous rebalancer operations targeting one device.

1

server_id

Server ID for Xpand as Replication Master.

1

session_id

Session ID for client connections.

0

‌Yes

session_log_bad_queries

Log BAD queries to the query.log.

false

‌Yes

session_log_ddl

Log DDL statements to query.log.

true

session_log_error_queries

Log ERROR statements to query.log.

true

session_log_slow_queries

Log SLOW statements to query.log.

true

session_log_slow_threshold_ms

Query duration threshold in milliseconds before logging this query.

10000

‌Yes

session_log_users

Log users and LOGIN/LOGOUT to user.log.

false

sha256_password_private_key_path

RSA private key file. Available in Xpand 6.0 and later.

sha256_password_public_key_path

RSA public key file. Available in Xpand 6.0 and later.

sigma_skiplist

Enable skiplist containers for aggregates and sorting.

  • true (Xpand 6.1)

  • false (Xpand 6.0 and earlier)

‌Yes

sierra_enable_static_pds

Enable usage of new-style static PDs

  • true (Xpand 6.0 and later)

  • false (Xpand 5.3)

sierra_predictive_governor

Enable the predictive governor.

true

sierra_predictive_governor_threshold

Number of rows returned by a query before the predictive governor prevents execution.

100000000000

slave_max_allowed_packet

Maximum allowed packet size in bytes for the slave.

16777216

slave_slice_max_bytes

Maximum size of slave slice buffer, in bytes.

  • 268435456 (Xpand 6.0 and later)

  • 67108864 (Xpand 5.3)

sql_auto_is_null

If true, and <col> is an auto_increment column, testing for <col> IS NULL will match the last row inserted

false

‌Yes

sql_log_bin

Log statements to binary logs. This variable can be set to FALSE on a per-session basis.

true

‌Yes

sql_log_skip_binlogs

Do not log to the specified comma-separated binlogs. Available in Xpand 6.0 and later.

‌Yes

sql_mode

Xpand provides limited support for SQL_MODE.

STRICT_TRANS_TABLES

‌Yes

sql_notes

Dummy variable for compatibility

false

‌Yes

sql_quote_show_create

true

‌Yes

sql_safe_updates

Dummy variable for compatibility.

false

‌Yes

sql_select_limit

18446744073709551615

‌Yes

ssl_cert

SSL public key certificate file

server-cert.pem

ssl_enabled

SSL is enabled

false

ssl_key

SSL private key file

server-key.pem

sync_binlog

Dummy variable for compatibility.

0

system_time_zone

Time Zone. Must be set in Olson time zone format. See Guide.

UTC

task_rebalancer_rebalance_distribution_interval_ms

Milliseconds between runs of periodic task "rebalancer_rebalance_distribution". Specify 0 to disable periodic task.

30000

task_rebalancer_rebalance_interval_ms

Milliseconds between runs of periodic task "rebalancer_rebalance". Specify 0 to disable periodic task.

30000

task_rebalancer_reprotect_interval_ms

Milliseconds between runs of periodic task "rebalancer_reprotect". Specify 0 to disable periodic task.

15000

task_rebalancer_split_interval_ms

Milliseconds between runs of periodic task "rebalancer_split". Specify 0 to disable periodic task.

30000

task_rebalancer_zone_balance_interval_ms

Milliseconds between runs of periodic task "rebalancer_zone_balance". Specify 0 to disable periodic task.

60000

task_rebalancer_zone_missing_interval_ms

Milliseconds between runs of periodic task "rebalancer_zone_missing". Specify 0 to disable periodic task.

300000

trxshoot_disk_min_pct

Kill the oldest transaction when available disk space goes below this percentage and available undo space goes below trxshoot_undo_min_pct.

5

trxshoot_undo_min_pct

Kill the oldest transaction when available undo space goes below this percentage and available disk space goes below trxshoot_disk_min_pct.

2

trx_timeout_s

Maximum allowed age for transactions. Specify 0 for no timeout.

0

‌Yes

tx_sync_commit

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.

STRICT

‌Yes

unique_checks

true

‌Yes

version

Concatenation of mysql_version and clustrix_version.

<auto populated>

wait_timeout

Timeout in seconds after data is sent to a client to close the connection.

28800

‌Yes

write_trx_timeout_s

Maximum allowed age for write transactions. Specify 0 for no timeout.

0

Yes