Skip to end of metadata
Go to start of metadata

This section describes the ClustrixDB global and session variables. All variables are global. Some variables may also be set at the session level. Session variables override the global setting for the duration of a specific session and are noted below.

For the majority of workloads, Clustrix recommends retaining the default values for global variables.

Please contact Clustrix Support with specific questions about modifying any of the default values as the product does not warn of inadvisable settings.

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 Clustrix. If Clustrix 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 Clustrix-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). 

Global Variables


NameDescriptionDefault ValueSession Variable
autocommitEach statement will be its own transaction, and automatically applied to the database.true

(tick)

autoretry
true

(tick)

auto_increment_incrementAmount in which auto increment values increase by, by default.1

(tick)

auto_increment_offsetValue where auto increment values start at by default.1

(tick)

backup_backup_concurrencyControls parallelism within a single backup transaction.1
backup_restore_concurrencyNumber of slices to be restored concurrently on each node. Specify 0 for automatic selection.0
backup_write_compression_levelCompression level from 1 (fastest) to 9 (best compression)6
binlog_checksumAlways NONE. Clustrix masters do not support generating event checksums.NONE
binlog_formatForce all binlogs to log in this format, unless set to 'DEFAULT'.DEFAULT

(tick)

character_set_client
latin1

(tick)

character_set_connection
latin1

(tick)

character_set_databaseDummy variable for compatibility. Must be utf8.utf8

(tick)

character_set_results
latin1

(tick)

character_set_serverThe default character set for databases when they are createdutf8

(tick)

cluster_id64-bit cluster id<auto populated>
cluster_nameName of the cluster.

clustrix_versionClustrix software version<auto populated>
collation_connection
latin1_swedish_ci

(tick)

collation_databaseThe default collation use by the default database. This value cannot be modified.utf8_general_ci

(tick)

collation_serverThe default collation for databases when they are createdutf8_general_ci

(tick)

consistent_orderForce consistent ordering. See Guide.false

(tick)

customer_nameThis should be set on cluster formation. Used in Alerts / Warnings.Unknown
databasefull_message_interval_sDatabase guard rail message interval in seconds.120
databasefull_system_error_percentageFail system queries when space usage surpasses this percentage.97
databasefull_system_warn_percentageWarn about system queries when space usage surpasses this percentage.95
databasefull_user_error_percentageFail user queries when space usage surpasses this percentage.90
databasefull_user_warn_percentageWarn about user queries when space usage surpasses this percentage.80
debug_deadlocksAttempt to get and log conflicting transaction session and statement information on distributed deadlock detection.false
device_auto_resize_to_largestAutomatically resize all (online) devices in the cluster to match the largest devicetrue
device_temporary_space_limit_bytesMaximum amount of bytes allowed to be used for temporary containers.5368709120
foreign_key_cascade_limitLimit of nested or cyclic foreign key cascading3
foreign_key_checksEnable/Disable foreign key checks.true

(tick)

format_versionA version string indicating the version of the software when the cluster was initially created.<auto populated>
gtid_modeAlways OFF. Clustrix masters do not support generating GTID events.OFF
gtid_purgedDummy variable for compatibility. (Clustrix does not support replication with Global Transaction Identifiers.)

gtm_schedule_tilEnable the TIL schedulertrue
gtm_schedule_til_batch_rowsRows to run before rescheduling100
hash_dist_min_slicesThe 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

(tick)

have_query_cacheDummy variable for compatibility.NO
hostname

(tick)

httpmon_port
3581
idle_trx_timeout_sMaximum allowed age for idle transactions. Specify 0 for no timeout.120

(tick)

innodb_flush_log_at_trx_commitDummy variable for compatibility.1
interactive_timeoutDummy variable for compatibility.28800

(tick)

internode_latency_warn_usIf internode latency exceeds this, send a warning to clustrix.log. Setting to 0 turns warnings off.0
jdbcCompliantTruncationJDBC compliant truncation checkfalse

(tick)

languageDummy variable for compatibility./usr/local/mysql/share/mysql/english
last_auto_increment_relationThe relation to which we last inserted an auto_increment value0

(tick)

lc_time_namesDummy variable for compatibility.en_US

(tick)

licenseThe license string for the cluster

lockman_max_locksThe maximum number of locks the lock manager will hold on each node in the cluster.5000000
lockman_max_transaction_locksThe maximum number of locks a single transaction can hold on each node in the cluster.1000000
lock_dest_on_insert_selectAcquire an exclusive lock on the destination table when using INSERT INTO...SELECT FROM statements.false
lock_on_insert_selectAcquire a read lock on the source data when using INSERT INTO...SELECT FROM statements. This is necessary for correct statement based replication.true
lock_wait_timeout_msMilliseconds a query waits for a lock before timing out.300000
log_binDummy variable for compatibility.ON
lower_case_table_namesTable 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
master_status_binlogBinlog used in SHOW MASTER STATUS when used without specifying a binlog.

(tick)

max_allowed_packetMaximum allowed query size16777216
max_connectionsThe maximum number of connections allowed per node250
max_failuresNumber of nodes that can fail simultaneously without losing the ability to resolve transactions1
max_memlog_keep_filesnumber of old log files the memlog should keep30
max_memlog_message_bytesmaximum number of bytes to log in a single log message1048576
max_memory_table_limit_mbmaximum memory usable for in memory tables in MB16
max_sierra_opt_mem_MiBCease 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_MiBHalt 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_sStop 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_MiBHalt 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_MiBHalt 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_slicesThe maximum allowed number of slices for a representation.2000
max_tablesThe maximum allowed number of tables. Do not change.2000
memlog_rollover_hoursmemlog will start a new file after this many hours (0 disables time-based rollover)0
memlog_rollover_size_MiBmemlog will start a new file if the current one exceeds this size (mb)1024
memory_table_system_full_error_percentageFail system queries when space usage surpasses this percentage.97
memory_table_system_full_warn_percentageWarn about system queries when space usage surpasses this percentage.95
memory_table_user_full_error_percentageFail user queries when space usage surpasses this percentage.90
memory_table_user_full_warn_percentageWarn about user queries when space usage surpasses this percentage.80
mysql_default_db_replication_policyReplicate databases not specified in mysql_slave_db_replication_policy.true
mysql_default_table_replication_policyReplicate tables not specified in mysql_table_replication_policy.true
mysql_float_format
mysql51

(tick)

mysql_portDefault port for mysql access to Clustrix.3306
mysql_relay_log_bytesMaximum size of relay log in bytes a slave process is allowed to create.52428800
mysql_slave_batch_kb_limit
512
mysql_versionThe reported MySQL server version5.0.45
net_buffer_lengthDummy variable for compatibility.16384
net_write_timeoutTimeout in seconds if no data is received from a client to close the connection.60

(tick)

portDefault port for mysql access to Clustrix.3306
qrc_enabled
false
query_cache_sizeDummy variable for compatibility.0
query_cache_typeDummy variable for compatibility.OFF

(tick)

query_fanout
true

(tick)

query_fanout_all_writes
false

(tick)

query_fanout_insert_select
true

(tick)

read_onlyEnable/Disable read only mode.false
rebalancer_global_task_limitMaximum number of simultaneous rebalancer operations.16
rebalancer_rebalance_task_limitMaximum number of operations that rebalancer_imbalanced and rebalancer_rebalance_distribution will each schedule at once.2
rebalancer_rebalance_thresholdMinimum coefficient of overall write load variation that will trigger rebalance activity.0.05
rebalancer_reprotect_queue_interval_sQueued 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_kbSize at which the rebalancer splits slices.1048576
rebalancer_vdev_task_limitMaximum number of simultaneous rebalancer operations targeting one device.1
server_idServer ID for Clustrix as Replication Master.1
session_idSession ID for client connections.0

(tick)

session_log_bad_queriesLog BAD queries to the query.log.false

(tick)

session_log_ddlLog DDL statements to query.log.true
session_log_error_queriesLog ERROR statements to query.log.true
session_log_slow_queriesLog SLOW statements to query.log.true
session_log_slow_threshold_msQuery duration threshold in milliseconds before logging this query.20000

(tick)

session_log_usersLog users and LOGIN/LOGOUT to user.log.false
sigma_skiplistUse skiplist-based sigmasfalse

(tick)

slave_max_allowed_packetMaximum allowed packet size in bytes for the slave.16777216
slcon_max_row_sizemaximum row size in skiplist containers (in bytes)32696
sql_auto_is_nullIf true, and <col> is an auto_increment column, testing for <col> IS NULL will match the last row insertedfalse

(tick)

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

(tick)

sql_mode

(tick)

sql_notesDummy variable for compatibilityfalse

(tick)

sql_quote_show_create
true

(tick)

sql_safe_updatesDummy variable for compatibility.false

(tick)

sql_select_limit
18446744073709551615

(tick)

ssl_certSSL public key certificate fileserver-cert.pem
ssl_enabledSSL is enabledfalse
ssl_keySSL private key fileserver-key.pem
sync_binlogDummy variable for compatibility.0
system_time_zoneTime Zone. Must be set in Olson time zone format. See Guide.UTC
task_rebalancer_rebalance_distribution_interval_msMilliseconds between runs of periodic task "rebalancer_rebalance_distribution". Specify 0 to disable periodic task.30000
task_rebalancer_rebalance_interval_msMilliseconds between runs of periodic task "rebalancer_rebalance". Specify 0 to disable periodic task.30000
task_rebalancer_reprotect_interval_msMilliseconds between runs of periodic task "rebalancer_reprotect". Specify 0 to disable periodic task.15000
task_rebalancer_split_interval_msMilliseconds between runs of periodic task "rebalancer_split". Specify 0 to disable periodic task.30000
task_rebalancer_zone_balance_interval_msMilliseconds between runs of periodic task "rebalancer_zone_balance". Specify 0 to disable periodic task.60000
task_rebalancer_zone_missing_interval_msMilliseconds between runs of periodic task "rebalancer_zone_missing". Specify 0 to disable periodic task.60000
trxshoot_disk_min_pct
5
trxshoot_undo_min_pct
2
trx_timeout_sMaximum allowed age for transactions. Specify 0 for no timeout.0

(tick)

tx_sync_commitControls when the client is notified of successful commit. 'RELAXED' is lower latency, but may result in 'committed' transactions being lost.STRICT

(tick)

unique_checks
true

(tick)

versionConcatenation of mysql_version and clustrix_version.<auto populated>
wait_timeoutTimeout in seconds after data is sent to a client to close the connection.28800

(tick)

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

(tick)

  • No labels