Spider Server System Variables
You are viewing an old version of this article. View
the current version here.
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.
Contents
- spider_auto_increment_mode
- spider_bgs_first_read
- spider_bgs_mode
- spider_bgs_second_read
- spider_bka_engine
- spider_bka_mode
- spider_block_size
- spider_bulk_access_free
- spider_bulk_size
- spider_bulk_update_mode
- spider_bulk_update_size
- spider_conn_recycle_mode
- spider_conn_recycle_strict
- spider_connect_mutex
- spider_connect_retry_count
- spider_connect_retry_interval
- spider_connect_timeout
- spider_crd_bg_mode
- spider_crd_interval
- spider_crd_mode
- spider_crd_sync
- spider_crd_type
- spider_crd_weight
- spider_direct_dup_insert
- spider_direct_order_limit
- spider_error_read_mode
- spider_error_write_mode
- spider_first_read
- spider_force_commit
- spider_general_log
- spider_init_sql_alloc_size
- spider_internal_limit
- spider_internal_offset
- spider_internal_optimize
- spider_internal_optimize_local
- spider_internal_sql_log_off
- spider_internal_unlock
- spider_internal_xa
- spider_internal_xa_snapshot
- spider_local_lock_table
- spider_lock_exchange
- spider_log_result_errors
- spider_low_mem_read
- spider_max_order
- spider_multi_split_read
- spider_net_read_timeout
- spider_net_write_timeout
- spider_ping_interval_at_trx_start
- spider_quick_mode
- spider_quick_page_size
- spider_read_only_mode
- spider_remote_access_charset
- spider_remote_autocommit
- spider_remote_default_database
- spider_remote_sql_log_off
- spider_remote_time_zone
- spider_remote_trx_isolation
- spider_reset_sql_alloc
- spider_same_server_link
- spider_second_read
- spider_select_column_mode
- spider_selupd_lock_mode
- spider_semi_split_read
- spider_semi_split_read_limit
- spider_semi_table_lock
- spider_semi_table_lock_connection
- spider_semi_trx
- spider_semi_trx_isolation
- spider_skip_default_condition
- spider_split_read
- spider_sts_bg_mode
- spider_sts_interval
- spider_sts_mode
- spider_sts_sync
- spider_support_xa
- spider_sync_autocommit
- spider_sync_time_zone
- spider_sync_trx_isolation
- spider_table_init_error_interval
- spider_udf_ct_bulk_insert_interval
- spider_udf_ct_bulk_insert_rows
- spider_udf_ds_bulk_insert_rows
- spider_udf_ds_table_loop_mode
- spider_udf_ds_use_real_table
- spider_udf_table_lock_mutex_count
- spider_udf_table_mon_mutex_count
- spider_use_all_conns_snapshot
- spider_use_consistent_snapshot
- spider_use_default_database
- spider_use_flash_logs
- spider_use_handler
- spider_use_pushdown_udf
- spider_use_table_charset
spider_auto_increment_mode
- Description: The auto increment mode.
-1
The table parameter is adopted.0
Normal mode. Uses a counter that Spider gets from the remote backend server with an exclusive lock for the auto increment value. Slow. Switch to quick mode if you use Spider tables with the table partitioning feature and the auto increment column is first column of index.1
Quick mode. Uses an internal Spider counter for the auto increment value. Fast, but it is possible for duplicates to occur if you update the same table from multiple Spider proxies and the same table.2
Set zero value. Auto increment value is given by the remote backend. Sets zero value even if you set a value to the auto increment column. Sets zero value after choosing an inserted partition if you use a table with the table partitioning feature.3
The auto increment value is given by the remote backend server, if you set an auto increment column to a null value. The auto increment value is given by the local server, if you set a zero value to the auto increment column. Rather usespider_reset_auto_increment=2 or 3
if you want to use an auto increment column on the remote server.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_bgs_first_read
- Description: Number of first read records when concurrent background search is used. To start a range scan on the remote backend the storage engine first needs to send the first record. Fetching a second record in the same query can save a network round trip stopping the plan if the backend have a single record. First read and second read are used to warm up for background search, third read without using spider_split_read and spider_semi_split_read will fetch the remaining data source in a single last fetch.
-1
The table parameter is adopted.0
Records are usually retrieved.1 or more
Number of records.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
2
spider_bgs_mode
- Description: Background search mode. Partitioning plugin scans partitions one after the other to optimize the memory usage. Because the shards are external, reading all shards can be performed in parallel when the plan prunes multiple partitions.
-1
The table parameter is adopted.0
Background search is disabled.1
Background search is used if search with no lock.2
Background search is used if search with no lock or shared lock.3
Background search is used regardless of the lock.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_bgs_second_read
- Description: Number of second read records on the backend server when background search is used. When the first records are found from
spider_bgs_first_read
, the engine will continue scanning a range adding a LIMIT spider_bgs_first_read, spider_bgs_second_read.-1
The table parameter is adopted.0
Records are usually retrieved.1 or more
Number of records.
- Scope: Global, Session
- Dynamic: Yes
- Default Session Value:
-1
- Default Table Value:
100
spider_bka_engine
- Description: The temporary table storage engine used when the
bka_mode
table parameter is1
. The table parameter default is memory. - Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
Memory
spider_bka_mode
- Description: The internal action for using
multi_split_read=0
.-1
The table parameter is adopted.0
Use "union all".1
Use temporary table if it is judged acceptable.2
Same as1
. Avoiding replication delay.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
1
spider_block_size
- Description: Size of memory block used in MariaDB. Can usually be left unchanged.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
16384
spider_bulk_access_free
- Description: Free mode of bulk access resources.
-1
The table parameter is adopted.0
in reset1
in close
- Data Type:
numeric
- Default Session Value:
-1
- Introduced: Spider 3.0
spider_bulk_size
- Description: Size in bytes of the buffer when multiple INSERT's group in a batch, bulk insert.
-1
The table parameter is adopted.0 or more
: Size of buffer.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
16000
spider_bulk_update_mode
- Description: Bulk update and delete mode. If
spider_bgs_mode
orspider_split_read
are changed from their default settings, Spider treats this parameter setting as2
.-1
The table parameter is adopted.0
Send "update" and "delete" statements one by one.1
Send collected multiple "update" and "delete" statements. Collected statements are sent one by one.2
Send collected multiple "update" and "delete" statements. Collected statements are sent together.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_bulk_update_size
- Description: Size in bytes of the "update" and "delete" query generated at bulk update.
-1
The table parameter is adopted.0 or more
Size of buffer.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
16000
spider_conn_recycle_mode
- Description: Connection recycle mode.
0
Disconnect.1
Recycles by all sessions.2
Recycles in the same session.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
0
spider_conn_recycle_strict
- Description: Force to re create new connection.
ON
Don't force.OFF
Force new connection
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
OFF
spider_connect_mutex
- Description: Serialized remote servers connections. You can use this parameter if you get an error or slowdown from too many connection processes.
0
Not serialized.1
: Serialized.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
0
spider_connect_retry_count
- Description: Connect-retry-count for connection failure caused by too many connection processes.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
1000
spider_connect_retry_interval
- Description: Connect-retry-interval in micro-seconds for connection failure caused by too many connection processes.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
1000
spider_connect_timeout
- Description: The timeout in second to declare remote backend no responsive when opening a connection. Can be change for slow latency network.
-1
The table parameter is adopted.0
Less then 1.1 and more
Number of second.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Max Value:
2147483647
- Default Session Value:
-1
- Default Table Value:
0
spider_crd_bg_mode
- Description: Indexes the cardinality statistics in the background.
Disable when crd_mode = 3 or crd_interval = 0
-1
The table parameter is adopted.0
Background confirmation is disabled.1
Background confirmation is enabled.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
1
spider_crd_interval
- Description: Time interval of index cardinality statistics.
Set to 0 to always get the newest information from remote servers.
-1
The table parameter is adopted.1 or more
Interval in second table state confirmation.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
51
spider_crd_mode
- Description: Mode for index cardinality statistics
The show command is used at the table level default.
-1,0
The table parameter is adopted.1
The show command is used.2
The information schema is used.3
The explain command is used.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
1
spider_crd_sync
- Description: Index cardinality statistics synchronization in partitioned table.
-1
The table parameter is adopted.0
No synchronization.1
Table state is synchronized when opening a table, no more synchronization later.2
Synchronization.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_crd_type
- Description: Type of cardinality calculation. Only effective when crd_mode is 1 or 2.
-1
The table parameter is adopted.0
crd_weight is used as a fixed value.1
crd_weight is used as an addition value.2
crd_weight is used as a multiplication value.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
2
spider_crd_weight
- Description: Weight coefficient to calculate effectiveness of index from cardinality of column.
-1
The table parameter is adopted.0 or more
Weight.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
2
spider_direct_dup_insert
- Description: Push down REPLACE and INSERT IGNORE to remote server. Can save some network roundtrip if the key always maps to a single partition.
-1
The table parameter is adopted.0
Duplicate check on local server.1
Avoid duplicate check on local server.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_direct_order_limit
- Description: Push down ORDER BY and LIMIT to remote server
-1
The table parameter is adopted.0
Local execution1
Push down execution
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_error_read_mode
- Description: Can send empty result when error raised reading from backend server. Can be use for application that does not implement replaying transactions.
-1 :use table parameter
0 :return error
1 :return empty record on error
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_error_write_mode
- Description: Can send empty result when error raised writing to backend server. Can be use for application that does not implement replaying transactions.
-1 :use table parameter
0 :return error
1 :return empty record on error
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_first_read
- Description: Number of first read records to start a range scan on the backend server, the storage engine first needs to send the first record. Fetching the second record can save a network round trip stopping the plan if the backend have a single record. First read and second read are used to warm up for background search, third read without using spider_split_read and spider_semi_split_read will fetch the remaining data source in a single last fetch.
-1
The table parameter is adopted.0
Records are usually retrieved.1 or more
Number of records.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
2
spider_force_commit
- Description: Behavior when error occurs on xa prepare, xa commit, and xa rollback.
0
The error is returned.1
The error is returned when xid doesn't exist else continues processing2
Processing is continued disregarding all the errors.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
0
spider_general_log
- Description: Log all Spider command to the general log. Error code are log according to spider_log_result_errors
OFF
It does not logON
It log
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
OFF
spider_init_sql_alloc_size
spider_internal_limit
- Description: Limit the number of records when acquired from remote server.
-1
The table parameter is adopted.0 or more
Limitation of number of records.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
9223372036854775807
spider_internal_offset
- Description: Skip record when acquired from remote server.
-1
The table parameter is adopted.0
or more : Skip number of records.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_internal_optimize
- Description: Push down of OPTIMIZE TABLE
-1
The table parameter is adopted.0
It doesn't transmit.1
It transmits.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_internal_optimize_local
spider_internal_sql_log_off
- Description: SQL transmitted to remote server general log.
OFF
Log SQL to remote server.ON
Do not log SQL to remote server.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
ON
spider_internal_unlock
spider_internal_xa
- Description: XA implementation at the server or the storage level.
Set different server id in the all server instances for generating different xa id when using Server.
OFF
Engine protocol.ON
Server protocol.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
OFF
spider_internal_xa_snapshot
- Description: When using MariaDB internal XA implementation and START TRANSACTION WITH CONSISTENT SNAPSHOT. There is a limitation for reading consistent data on all backend.
0
Raise error when use a spider table1
Raise error when START TRANSACTION2
Take a consistent CONSISTENT SNAPSHOT on each backend . But global consistency is lost3
Take a start transaction with XA but remove CONSISTENT SNAPSHOT.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
0
spider_local_lock_table
- Description: LOCK TABLE push down to remote server.
0
Push down.1
Don't transmit.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
0
spider_lock_exchange
- Description: Convert SELECT LOCK IN SHARE MODE and SELECT FOR UPDATE to LOCK TABLE
0
It doesn't convert it.1
It converts it.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
0
spider_log_result_errors
spider_low_mem_read
spider_max_order
spider_multi_split_read
- Description: Division of SQL issued to remote server when range retrieval is resolved to two or more conditions by internal processing of MySQL.
-1
The table parameter is adopted.0
It doesn't divide.1
It divides.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_net_read_timeout
- Description: The TCP timeout in second to declare remote backend no responsive when reading from a connection. Can be change for slow latency network.
-1
The table parameter is adopted.0
Less then 1.1 and more
Number of second.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Max Value:
2147483647
- Default Session Value:
-1
- Default Table Value:
0
spider_net_write_timeout
- Description: The TCP timeout in second to declare remote backend no responsive when writing to a connection. Can be change for slow latency network.
-1
The table parameter is adopted.0
Less then 1.1 and more
Number of second.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Max Value:
2147483647
- Default Session Value:
-1
- Default Table Value:
0
spider_ping_interval_at_trx_start
- Description: Reset the connection keepalive timeout in second by sending a ping.
0
At every transaction.1 and more
Number of second.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Max Value:
2147483647
- Default Session Value:
3600
spider_quick_mode
- Description:Type of backend query buffering, buffer on the remote backend server or in the local buffer.
-1
The table parameter is adopted.0
Local buffering, it acquires records collectively.1
Remote buffering, acquisition record per record. Interrupt does not wait and recover on context switch back.2
Remote buffering, acquisition record per record. Interrupt wait the end of the acquisition
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_quick_page_size
spider_read_only_mode
spider_remote_access_charset
spider_remote_autocommit
spider_remote_default_database
spider_remote_sql_log_off
spider_remote_time_zone
spider_remote_trx_isolation
spider_reset_sql_alloc
spider_same_server_link
spider_second_read
spider_select_column_mode
spider_selupd_lock_mode
spider_semi_split_read
spider_semi_split_read_limit
spider_semi_table_lock
spider_semi_table_lock_connection
spider_semi_trx
- Description: On AUTO COMMIT encapsulate SQL with BEGIN and COMMIT (if spider_sync_autocommit is 1)
0
OFF1
ON
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
ON
spider_semi_trx_isolation
spider_skip_default_condition
spider_split_read
spider_sts_bg_mode
- Description: Mode of table statistic at background.
Background confirmation is enabled by default at the table level, one thread per partition is created to maintain table status. Disable when sts_interval=0, effective as default: sts_interval is 10.
-1
The table parameter is adopted.0
Background confirmation is disabled.1
Background confirmation is enabled.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
1
spider_sts_interval
- Description: Time interval of table statistics.
Set to 0 to always get the newest information from remote servers.
-1
The table parameter is adopted.1 or more
Interval in second table state confirmation.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
10
spider_sts_mode
- Description: Mode for table statistic
The show command is used at the table level default.
-1,0
The table parameter is adopted.1
The show command is used.2
The information schema is used.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
1
spider_sts_sync
- Description: Table statistic synchronization in partitioned table.
-1
The table parameter is adopted.0
No synchronization.1
Table state is synchronized when opening a table, no more synchronization later.2
Synchronization.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
0
spider_support_xa
- Description: Use xa protocol for mirroring and for multi shard transactions
1
Use xa protocol0
Don't use xa protocol.
- Scope: Global
- Dynamic: No
- Data Type:
numeric
- Default Table Value:
1
spider_sync_autocommit
- Description: Local AUTO COMMIT push down to remote backends.
OFF
It doesn't push down.ON
It push down.
- Scope: Global
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
ON
spider_sync_time_zone
- Description: Local TIME ZONE push down to remote backends.
OFF
It doesn't push down.ON
It push down.
- Scope: Global
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
ON
spider_sync_trx_isolation
- Description: Local transaction isolation level push down to remote backends.
ON
It doesn't push down.OFF
It push down.
- Scope: Global
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
ON
spider_table_init_error_interval
spider_udf_ct_bulk_insert_interval
spider_udf_ct_bulk_insert_rows
spider_udf_ds_bulk_insert_rows
spider_udf_ds_table_loop_mode
spider_udf_ds_use_real_table
spider_udf_table_lock_mutex_count
spider_udf_table_mon_mutex_count
spider_use_all_conns_snapshot
- Description: On START TRANSACTION WITH SNAPSHOT, it pass to all connections
OFF
It doesn't pass.ON
It pass to all connections.
- Scope: Global
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
ON
spider_use_consistent_snapshot
- Description: local START TRANSACTION WITH CONSISTENT is push down to backend
OFF
Don't push down .ON
Push down.
- Scope: Global
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
ON
spider_use_default_database
- Description:
OFF
Don't use default database .ON
Use default database.
- Scope: Global
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
ON
spider_use_flash_logs
- Description: Push down FLUSH LOGS to remote backends
OFF
It doesn't push down.ON
It push down.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
boolean
- Default Session Value:
OFF
spider_use_handler
- Description: Conversion of HANDLER SQL command
Handler conversion will be disable if spider_sync_trx_isolation =0 to avoid to use handler command on serializable isolation level.
0
HANDLER are converted to SELECT1
HANDLER are passed to the backend2
SQL is converted to HANDLER3
SQL is converted to HANDLER and HANDLER is converted to SQL
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
0
spider_use_pushdown_udf
- Description: When engine_condition_pushdown=1 and an UDF function is used in a condition. Define if the UDF execution is local or push down.
-1
The table parameter is adopted.0
It doesn't transmit.1
It transmits.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
1
spider_use_snapshot_with_flush_tables
- Description: On START TRANSACTION WITH CONSISTENT send to remote backend FLUSH TABLE WITH READ LOCK If this value is 1 or 2, FLUSH LOGS and UNLOCK TABLES are encapsulated
0 : It doesn't add.
1 : It adds. (Only when spider_use_all_conns_snapshot is 1)
2 : The snap shot synchronizes by using lock tables and flush tables at the xa transaction level. (Only when spider_use_all_conns_snapshot is 1, it is effective.)
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
0
spider_use_table_charset
- Description: Use local table charset for backend connection.
-1
The table parameter is adopted.0
Use utf8.1
Use table charset.
- Scope: Global, Session
- Dynamic: Yes
- Data Type:
numeric
- Default Session Value:
-1
- Default Table Value:
1
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.