The following variables are available when the Spider storage engine has been installed.
See Server System Variables for a complete list of system variables and instructions on setting them.
See also the Full list of MariaDB options, system and status variables.
spider_auto_increment_mode
-1
Uses the table parameter. 0
Normal Mode. Uses a counter that Spider gets from the remote backend server with an exclusive lock for the auto-increment value. This mode is slow. Use Quick Mode (2
), if you use Spider tables with the table partitioning feature and the auto-increment column is the first column of the index. 1
Quick Mode. Uses an internal Spider counter for the auto-increment value. This mode is fast, but it is possible for duplicates to occur when updating the same table from multiple Spider proxies. 2
Set Zero Mode. The auto-increment value is given by the remote backend. Sets the column to 0
, even if you set the value to the auto-increment column in your statement. If you use the table with the table partitioning feature, it sets to zero after choosing an inserted partition. 3
When the auto-increment column is set to NULL
, the value is given by the remote backend server. If you set the auto-increment column to 0
,the value is given by the local server. Set spider_reset_auto_incremnet
to 2
or 3
if you want to use an auto-increment column on the remote server. numeric
-1
0
-1
to 3
aim
spider_bgs_first_read
spider_split_read
and spider_semi_split_read
, the third read fetches the remaining data source in a single fetch. -1
Uses the table parameter. 0
Records are usually retrieved. 1
and greater: Number of records. numeric
-1
2
-1
to 9223372036854775807
bfr
spider_bgs_mode
-1
Uses the table parameter. 0
Disables background search. 1
Uses background search when searching without locks 2
Uses background search when searching without locks or with shared locks. 3
Uses background search regardless of locks. numeric
-1
0
-1
to 3
bmd
spider_bgs_second_read
spider_bgs_first_read
, the engine continues scanning a range adding a LIMIT
of spider_bgs_first_read
and spider_bgs_second_read
. -1
Uses the table parameter. 0
Records are usually retrieved. 1
and greater: Number of records. -1
100
-1
to 9223372036854775807
bsr
spider_bka_engine
spider_bka_mode
system variable is set to 1
. Defaults to the value of the table parameter, which is MEMORY by default. string
""
Memory
bke
spider_bka_mode
spider_multi_split_read
system variable is set to 0
, Spider uses this variable to determine how to handle statements when the optimizer resolves range retrieval to multiple conditions. -1
Uses the table parameter. 0
Uses "union all". 1
Uses a temporary table, if it is judged acceptable. 2
Uses a temporary table, if it is judged acceptable and avoids replication delay. numeric
-1
1
-1
to 2
bkm
spider_bka_table_name_type
numeric
-1
-1
to 1
spider_block_size
numeric
16384
0
to 4294967295
bsz
spider_bulk_size
INSERT
statements in a batch, (that is, bulk inserts). -1
The table parameter is adopted. 0
or greater: Size of the buffer. numeric
-1
16000
-1
to 2147483647
bsz
spider_bulk_update_mode
spider_bgs_mode
or spider_split_read
system variables, Spider sets this variable to 2
. -1
Uses the table parameter. 0
Sends UPDATE
and DELETE
statements one by one. 1
Collects multiple UPDATE
and DELETE
statements, then sends the collected statements one by one. 2
Collects multiple UPDATE
and DELETE
statements and sends them together. numeric
-1
0
-1
to 2
bum
spider_bulk_update_size
UPDATE
and DELETE
queries when generating bulk updates. -1
The table parameter is adopted. 0
or greater: Size of buffer. numeric
-1
16000
-1
to 2147483647
bus
spider_casual_read
-1
Use table parameter. 0
Use casual read. 1
Choose connection channel automatically. 2
to 63
Number of connection channels. numeric
-1
0
-1
to 63
##
spider_conn_recycle_mode
0
Disconnect. 1
Recycle by all sessions. 2
Recycle in the same session. numeric
0
to 2
0
spider_conn_recycle_strict
1
Don't force. 0
Force new connection numeric
0
0
to 1
spider_conn_wait_timeout
numeric
10
0
to 1000
spider_connect_error_interval
numeric
1
spider_connect_mutex
0
Not serialized. 1
: Serialized. boolean
0
spider_connect_retry_count
numeric
1000
0
to 2147483647
spider_connect_retry_interval
numeric
1000
-1
to 9223372036854775807#
spider_connect_timeout
-1
The table parameter is adopted. 0
Less than 1. 1
and greater: Number of seconds. numeric
-1
0
-1
to 2147483647
cto
spider_crd_bg_mode
spider_crd_mode
system variable is set to 3
or when the spider_crd_interval
variable is set to 0
. -1
Uses the table parameter. 0
Disables background confirmation. 2
Enables background confirmation. numeric
-1
1
-1
to 1
cbm
spider_crd_interval
0
to always get the latest information from remote servers. -1
The table parameter is adopted. 1
or more: Interval in seconds table state confirmation. numeric
-1
51
-1
to 2147483647
civ
spider_crd_mode
SHOW
at the table-level. -1,0
Uses the table parameter. 1
Uses the SHOW
command. 2
Uses the Information Schema. 3
Uses the EXPLAIN
command. numeric
-1
1
-1
to 3
cmd
spider_crd_sync
-1
Uses the table parameter. 0
Disables synchronization. 1
Uses table state synchronization when opening a table, but afterwards performs no synchronization. 2
Enables synchronization. numeric
-1
0
-1
to 2
csy
spider_crd_type
spider_crd_mode
system variable is set to use SHOW
(1
) or to use the Information Schema (2
). -1
Uses the table parameter. 0
Uses the value of the spider_crd_weight
system variable, as a fixed value. 1
Uses the value of the spider_crd_weight
system variable, as an addition value. 2
Uses the value of the spider_crd_weight
system variable, as a multiplication value. numeric
-1
2
-1
to 2
ctp
spider_crd_weight
spider_crd_type
system variable. -1
Uses the table parameter. 0
or greater: Weight. numeric
-1
2
-1
to 2147483647
cwg
spider_delete_all_rows_type
numeric
-1
-1
to 1
Spider 3.2
spider_direct_dup_insert
REPLACE
, INSERT IGNORE
and LOAD DATA LOCAL INFILE
to remote servers. This can save on network roundtrips if the key always maps to a single partition. For bulk operations, records are checked for duplicate key errors one by one on the remote server, unless you set it to avoid duplicate checks on local servers (1
). -1
Uses the table parameter. 0
Performs duplicate checks on the local server. 1
Avoids duplicate checks on the local server. numeric
-1
0
-1
to 1
ddi
spider_direct_order_limit
ORDER BY
and LIMIT
operations to the remote server. -1
Uses the table parameter. 0
Uses local execution. 1
Uses push down execution. numeric
-1
0
-1
to 9223372036854775807
dol
spider_dry_access
0
Normal access. 1
All access from Spider to data node is suppressed. boolean
OFF
spider_error_read_mode
-1
Uses the table parameter. 0
Returns an error. 1
Returns an empty result. numeric
-1
0
-1
to 1
erm
spider_error_write_mode
-1
Uses the table parameter. 0
Returns an error. 1
Returns an empty result-set on error. numeric
-1
0
-1
to 1
ewm
spider_first_read
spider_split_read
and spider_semi_split_read
system variables fetches the remaining data source in a single last fetch. -1
Use the table parameter. 0
Usually retrieves records. 1
and greater: Sets the number of first read records. numeric
-1
2
-1
to 9223372036854775807
frd
spider_force_commit
XA PREPARE
, XA COMMIT
, and XA ROLLBACK
statements. 0
Returns the error. 1
Returns the error when the xid
doesn't exist, otherwise it continues processing the XA transaction. 2
Continues processing the XA transaction, disregarding all errors. numeric
0
0
to 2
spider_general_log
spider_log_result_errors
system variable. OFF
Logs no commands. ON
Logs commands to the General Log. boolean
OFF
spider_index_hint_pushdown
force_index
. boolean
OFF
spider_init_sql_alloc_size
-1
Uses the table parameter. 0
or greater: Size of the buffer. numeric
-1
1024
isa
-1
to 2147483647
spider_internal_limit
-1
The table parameter is adopted. 0
or greater: Records limit. numeric
-1
9223372036854775807
-1
to 9223372036854775807
ilm
spider_internal_offset
-1
Uses the table parameter. 0
or more : Number of records to skip. numeric
-1
0
-1
to 9223372036854775807
ios
spider_internal_optimize
OPTIMIZE TABLE
statements. -1
Uses the table parameter. 0
Transmitted. 1
Not transmitted. numeric
-1
0
-1
to 1
iom
spider_internal_optimize_local
OPTIMIZE TABLE
statements are executed on the local server. -1
Uses the table parameter. 0
Not transmitted. 1
Transmitted. -1
-1
to 1
iol
spider_internal_sql_log_off
ON
or OFF
causes the Spider node to send a SET sql_log_off
statement to each of the data nodes using the SUPER
privilege. OFF
Logs SQL statements to the remote server. ON
Doesn't log SQL statements to the remote server. boolean
ON
spider_internal_unlock
SELECT
statements. 0
Not transmitted. 1
Transmitted. boolean
0
spider_internal_xa
server_id
system variable on all server instances to generate different xid
values. OFF
Uses the storage engine protocol. ON
Uses the server protocol. boolean
OFF
spider_internal_xa_id_type
numeric
0
-1
to 1
spider_internal_xa_snapshot
START TRANSACTION WITH CONSISTENT SNAPSHOT
. 0
Raise error when using a Spider table. 1
Raise error when issued a START TRANSACTION
statement. 2
Takes a consistent snapshot on each backend, but loses global consistency. 3
Starts transactions with XA, but removes CONSISTENT SNAPSHOT
. numeric
0
to 3
0
spider_load_crd_at_startup
boolean
-1
spider_load_sts_at_startup
boolean
-1
spider_local_lock_table
LOCK TABLES
statements down to the remote server. 0
Transmitted. 1
Not transmitted. boolean
0
spider_lock_exchange
SELECT... LOCK IN SHARE MODE
and SELECT... FOR UPDATE
statements into a LOCK TABLE
statement. 0
Not converted. 1
Converted. boolean
0
spider_log_result_error_with_sql
numeric
0
0
to 3
spider_log_result_errors
0
: Logs no errors from data nodes. 1
: Logs errors from data nodes. 2
: Logs errors from data nodes, as well as warning summaries. 3
: Logs errors from data nodes, as well as warning summaries and details. 4
: Logs errors from data nodes, as well as warning summaries and details, and result summaries. numeric
0
0
to 4
spider_low_mem_read
-1
Uses the table parameter. 0
Doesn't use low memory mode. 1
Uses low memory mode. numeric
-1
-1
to 1
spider_max_connections
0
, which is no limit. --spider-max-connections
numeric
0
0
to 99999
spider_max_order
ORDER BY
operations. -1
The table parameter is adopted. 0
and greater: Maximum number of columns. numeric
-1
32767#
-1
to 32767
mod
spider_multi_split_read
-1
Uses the table parameter. 0
Doesn't divide statements. 1
Divides statements. numeric
-1
0
-1
to 2147483647
msr
spider_net_read_timeout
-1
Uses the table parameter. 0
Less than 1 second timeout. 1
and greater: Timeout in seconds. numeric
-1
0
-1
to 2147483647
nrt
spider_net_write_timeout
-1
The table parameter is adopted. 0
Less than 1 second timeout. 1
and more: Timeout in seconds. numeric
-1
0
-1
to 2147483647
nwt
spider_ping_interval_at_trx_start
0
At every transaction. 1
and greater: Number of seconds. numeric
2147483647
3600
0
to 2147483647
spider_quick_mode
-1
Uses the table parameter. 0
Local buffering, it acquires records collectively with store_result
. 1
Remote buffering, it acquires records one by one. Interrupts don't wait and recovery on context switch back. 2
Remote buffering, it acquires records one by one. Interrupts wait to the end of the acquisition. 3
Local buffering, uses a temporary table on disk when the result-set is greater than the value of the spider_quick_page_size
system variable. numeric
-1
0
-1
to 3
qmd
spider_quick_page_size
-1
The table parameter is adopted. 0
or greater: Number of records. numeric
-1
100
-1
to 9223372036854775807
qps
spider_read_only_mode
-1
Uses the table parameter. 0
Allows writes to Spider tables. 1
Makes tables read- only. numeric
-1
0
-1
to 1
rom
spider_remote_access_charset
string
null
spider_remote_autocommit
-1
Doesn't change the auto-commit mode. 0
Sets the auto-commit mode to 0
. 1
Sets the auto-commit mode to 1
. numeric
-1
-1
to 1
spider_remote_default_database
0
OFF 1
ON string
spider_remote_sql_log_off
sql_log_off
system variable to use when connecting to backend servers. -1
Doesn't set the value. 0
Doesn't log Spider SQL statements to remote backend servers. 1
Logs SQL statements on remote backend numeric
-1
-1
to 1
spider_remote_time_zone
string
null
spider_remote_trx_isolation
-1
Doesn't set the Isolation Level. 0
Sets to the READ UNCOMMITTED
level. 1
Sets to the READ COMMITTED
level. 2
Sets to the REPEATABLE READ
level. 3
Sets to the SERIALIZABLE
level. numeric
-1
-1
to 3
spider_reset_sql_alloc
-1
Uses the table parameter. 0
Doesn't reset. 1
Resets. numeric
-1
1
-1
to 1
rsa
spider_same_server_link
0
Disables linking. 1
Enables linking. boolean
OFF
spider_second_read
LIMIT
using the spider_first_read
and spider_second_read
variables. -1
Uses the table parameter. 0
Usually retrieves records. 1
and greater: Number of records. -1
9223372036854775807
-1
to 9223372036854775807
srd
spider_select_column_mode
-1
Uses the table parameter. 0
Uses index columns when the SELECT
statement can resolve with an index, otherwise uses all columns. 1
Uses all columns judged necessary to resolve the query. numeric
-1
1
-1
to 1
scm
spider_selupd_lock_mode
INSERT SELECT
. -1
Uses the table parameter. 0
Takes no locks. 1
Takes shared locks. 2
Takes exclusive locks. numeric
-1
1
-1
to 2
slm#
spider_semi_split_read
spider_split_read
system variable. -1
Uses the table parameter. 0
Doesn't use chunk retrieval. 1 or more
Uses chunk retrieval. numeric
-1
0
-1
to 2147483647
ssr#
spider_semi_split_read_limit
spider_semi_split_read
system variable. -1
Uses the table parameter. 0
or more: The limit value. numeric
-1
9223372036854775807
-1
to 9223372036854775807
ssl#
spider_semi_table_lock
LOCK TABLES
statement to SQL executions sent to the remote backend server when using non-transactional storage engines to preserve consistency between roundtrips. 0
Disables semi-table locking. 1
Enables semi-table locking. numeric
1
0
to 1
stl#
spider_semi_table_lock_connection
spider_semi_table_lock
system variable. -1
Uses the table parameter. 0
Uses the same connection. 1
Uses different connections. numeric
-1
1
-1
to 1
stc#
spider_semi_trx
READ COMMITTED
or READ UNCOMMITTED
transaction isolation levels to force consistency, set the spider_semi_trx_isolation
system variable to 2
. 0
Disables semi-transaction consistency. 1
Enables semi-transaction consistency. boolean
ON
spider_semi_trx_isolation
-1
OFF 0
READ UNCOMMITTED 1
READ COMMITTED 2
REPEATABLE READ 3
SERIALIZABLE numeric
-1
-1
to 3
spider_skip_default_condition
-1
Uses the table parameter. 0
Computes condition push downs. 1
Doesn't compute condition push downs. numeric
-1
0
-1
to 1
sdc
spider_skip_parallel_search
--spider-skip-parallel-search=#
numeric
-1
-1
to 3
spider_split_read
-1
Uses the table parameter. 0
or more: Number of records. numeric
-1
9223372036854775807
-1
to 9223372036854775807
srd
spider_store_last_crd
--spider-store-last-crd=#
numeric
-1
-1
to 1
spider_store_last_sts
--spider-store-last-sts=#
numeric
-1
-1
to 1
spider_sts_bg_mode
spider_sts_interval
system variable is set to 0
, which causes Spider to always retrieve the latest information as need. It is effective, when the spider_sts_interval
system variable is set to 10
. -1
Uses the table parameter. 0
Disables background confirmation. 1
Enables background confirmation. numeric
-1
1
-1
to 1
sbm
spider_sts_interval
-1
Uses the table parameter. 0
Retrieves the latest table statistics on request. 1
or more: Interval in seconds for table state confirmation. numeric
-1
10
-1
to 2147483647
siv
spider_sts_mode
-1,0
Uses the table parameter. 1
Uses the SHOW
command. 2
Uses the Information Schema. numeric
-1
1
-1
to 2
smd
spider_sts_sync
-1
Uses the table parameter. 0
Doesn't synchronize table statistics in partitioned tables. 1
Synchronizes table state when opening a table, doesn't synchronize after opening. 2
Synchronizes table statistics. numeric
-1
0
-1
to 2
ssy
spider_support_xa
1
Enables XA Protocol for these Spider operations. 0
Disables XA Protocol for these Spider operations. boolean
1
spider_sync_autocommit
OFF
Pushes down local auto-commits. ON
Doesn't push down local auto-commits. boolean
ON
spider_sync_time_zone
OFF
Doesn't synchronize time zones. ON
Synchronize time zones. boolean
OFF
spider_sync_trx_isolation
ON
Doesn't push down local isolation levels. OFF
Pushes down local isolation levels. boolean
ON
spider_table_crd_thread_count
--spider-table-crd-thread-count=#
numeric
10
1
to 4294967295
spider_table_init_error_interval
numeric
1
0
to 4294967295
spider_table_sts_thread_count
--spider-table-sts-thread-count=#
numeric
10
1
to 4294967295
spider_udf_ct_bulk_insert_interval
-1
Uses the UDF parameter. 0
and more: Time in milliseconds. numeric
-1
2147483647
-1
to 2147483647
spider_udf_ct_bulk_insert_rows
-1, 0
: Uses the table parameter. 1
and more: Number of rows numeric
-1
18446744073709551615
-1# to
9223372036854775807
spider_udf_ds_bulk_insert_rows
-1, 0
Uses the UDF parameter. 1
or more: Number of rows numeric
-1
9223372036854775807
-1# to
9223372036854775807
spider_udf_ds_table_loop_mode
-1
Uses the table parameter. 0
Drops records. 1
Inserts the last table. 2
Inserts hte first table and loops again. numeric
-1
-1# to
2
spider_udf_ds_use_real_table
numeric
-1
-1# to
1
spider_udf_table_lock_mutex_count
numeric
20
1# to
4294967295
spider_udf_table_mon_mutex_count
numeric
20
1# to
4294967295
spider_use_all_conns_snapshot
START TRANSACTION WITH SNAPSHOT
statements to all connections. OFF
Doesn't pass statement to all connections. ON
Passes statement to all connections. boolean
OFF
spider_use_consistent_snapshot
START TRANSACTION WITH CONSISTENT
statement down to remote backend servers. OFF
Doesn't push the local statement down. ON
Pushes the local statement down. boolean
OFF
spider_use_default_database
OFF
Doesn't use the default database. ON
Uses the default database. boolean
ON
spider_use_flash_logs
FLUSH LOGS
statements down to remote backend servers. OFF
Doesn't push the statement down. ON
Pushes the statement down. boolean
OFF
spider_use_handler
HANDLER
SQL statements. When the spider_sync_trx_isolation
system variable is set to 0
, Spider disables HANDLER
conversions to prevent use of the statement on the SERIALIZABLE
isolation level. 0
Converts HANDLER
statements into SELECT
statements. 1
Passes HANDLER
to the remote backend server. 2
Converts SQL statements to HANDLER
statements. 3
Converts SQL statements to HANDLER
statements and HANDLER
statements to SQL statements.
numeric
-1
-1# to
3
uhd
spider_use_pushdown_udf
engine_condition_pushdown
system variable is set to 1
, whether to execute the UDF function locally or push it down. -1
Uses the table parameter. 0
Doesn't transmit the UDF 1
Transmits the UDF. numeric
-1
1
-1# to
1
upu
spider_use_snapshot_with_flush_tables
FLUSH LOGS
and UNLOCK TABLES
statements when START TRANSACTION WITH CONSISTENT
and FLUSH TABLE WITH READ LOCK
statements are sent to the remote backend servers. 0
: No encapsulation. 1
: Encapsulates, only when the spider_use_all_conns_snapshot
system variable i set to 1
. 2
: Synchronizes the snapshot using a LOCK TABLES
statement and [flush|FLUSH TABLES]]
at the XA transaction level. This is only effective when the spider_use_all_cons_snapshot
system variable is set to 1
. numeric
0
0# to
2
spider_use_table_charset
-1
Uses the table parameter. 0
Use utf8
. 1
Uses the table character set. numeric
-1
1
-1# to
1
utc
spider_version
string
spider_xa_register_mode
--spider-xa-register-mode=#
numeric
1
0
to 1
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/spider-server-system-variables/