Spider Server System Variables - Source

Revision: 104131
사용자: KiChang Lee
Date: 2014-11-05 07:36

The following variables are available when the [[spider|Spider]] storage engine has been installed.

See [[server-system-variables|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|Full list of MariaDB options, system and status variables]]. 
<<toc>>

==== ##spider_auto_increment_mode##
* **Description:** The [[auto_increment|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 the first column of the 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.
**##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## If you set an auto increment column to a null value, the auto increment value is given by the remote backend server. If you set a zero value to the auto increment column, the auto increment value is given by the local server. Rather use ##spider_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##
* **DSN Parameter Name:** ##aim##
==== ##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 has a single record. First read and second read are used to warm up for background search, third read without using [[#spider_split_read|spider_split_read]] and [[#spider_semi_split_read|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##
* **DSN Parameter Name:** ##bfr##
==== ##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##
* **DSN Parameter Name:** ##bmd##
==== ##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|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##
* **DSN Parameter Name:** ##bsr##
==== ##spider_bka_engine##
* **Description:** The temporary table storage engine used when the [[spider-server-system-variables#spider_bka_mode|bka_mode]] table parameter is ##1##. The default is the value of the table parameter, [[memory-storage-engine|memory]] by default.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##string##
* **Default Session Value:**  ##""##
* **Default Table Value:** ##Memory##
* **DSN Parameter Name:** ##bke##
==== ##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 as ##1## and avoiding replication delay.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##1##
* **DSN Parameter Name:** ##bkm##
==== ##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##
* **DSN Parameter Name:** ##bsz##
==== ##spider_bulk_access_free##
* **Description:** Free mode of bulk access resources.
**##-1## The table parameter is adopted.
**##0## in reset
**##1## in close
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##0##
* **Introduced:** Spider 3.0
* **DSN Parameter Name:** ##baf##
==== ##spider_bulk_size##
* **Description:** Size in bytes of the buffer when multiple INSERT's are grouped in a batch, bulk insert.
** ##-1## The table parameter is adopted.
** ##0 or more##: Size of the buffer.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##16000##
* **DSN Parameter Name:** ##bsz##
==== ##spider_bulk_update_mode##
* **Description:** Bulk update and delete mode. If [[#spider_bgs_mode|spider_bgs_mode]] or [[#spider_split_read|spider_split_read]] are changed from their default settings, Spider treats this parameter setting as ##2##.
**##-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##
* **DSN Parameter Name:** ##bum##
==== ##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##
* **DSN Parameter Name:** ##bus##
==== ##spider_casual_read##
* **Description:** By default, Spider uses ##casual read##,  which enables all isolation levels such as repeatable reads to work for transactions on multiple backends.  For auto commit queries, read consistency can be relaxed and run on multiple connections to the backends.   
**##-1## Use table parameter
**##0## Casual read 
**##1## Choose connection channel automatically
**##2-63## Number of connection channel
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ####
==== ##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:** Whether or not to force the creation of a 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 seconds to declare remote backend not responsive when opening a connection. Can be changed for high latency networks. 
**##-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##
* **DSN Parameter Name:** ##cto##             
==== ##spider_crd_bg_mode## 
* **Description:** Indexes the cardinality statistics in the background. Disable when [[#spider_crd_mode|crd_mode]] = 3 or [[#spider_crd_interval|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##
* **DSN Parameter Name:** ##cbm##
==== ##spider_crd_interval##   
* **Description:** Time interval in seconds 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 seconds table state confirmation.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##51##
* **DSN Parameter Name:** ##civ##    
==== ##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##  
* **DSN Parameter Name:** ##cmd##              
==== ##spider_crd_sync##
* **Description:**  Index cardinality statistics synchronization in partitioned tables.
**##-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##
* **DSN Parameter Name:** ##csy##             
==== ##spider_crd_type##
* **Description:** Type of cardinality calculation. Only effective when [[#spider_crd_mode|crd_mode]] is 1 or 2.
**##-1## The table parameter is adopted.
**##0## [[#spider_crd_weight|crd_weight]] is used as a fixed value.
**##1## [[#spider_crd_weight|crd_weight]] is used as an addition value.
**##2## [[#spider_crd_weight|crd_weight]] is used as a multiplication value.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##  
* **Default Table Value:** ##2##
* **DSN Parameter Name:** ##ctp##       
==== ##spider_crd_weight##
* **Description:** Weight coefficient to calculate effectiveness of index from cardinality of column. See [[#spider_crd_type|spider_crd_type]].
**##-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##
* **DSN Parameter Name:** ##cwg##
==== ##spider_direct_dup_insert##
* **Description:** How to manage duplicate key check  [[replace|REPLACE]] and [[ignore|INSERT IGNORE]] and [[LOAD DATA LOCAL INFILE]]  to remote server. Can save on network roundtrips if the key always maps to a single partition. For bulk operations, records will be checked for duplicate errors one by one on the remote server unless you set to ##spider_direct_dup_insert=1##. 
**##-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##
* **DSN Parameter Name:** ##ddi##              
==== ##spider_direct_order_limit##
* **Description:** Push down ORDER BY and LIMIT to remote server
**##-1## The table parameter is adopted.
**##0## Local execution
**##1## Push down execution
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##  
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##dol##
==== ##spider_dry_access##
* **Description:** Simulates empty resultset. No queries are sent to the backend. Can be used for performance tuning
**##0## Normal access.
**##1## All access from Spider to data node is suppressed.
* **Scope:** Global
* **Dynamic:** No
* **Data Type:** ##Boolean##                  
==== ##spider_error_read_mode##
* **Description:** Can send empty result when error raised reading from backend server. Can be useful for applications that do 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## 
* **DSN Parameter Name:** ##erm##
==== ##spider_error_write_mode##
* **Description:** Can send empty result when error raised writing to backend server. Can be useful for applications that do 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##    
* **DSN Parameter Name:** ##ewm##
==== ##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 has a single record. First read and second read are used to warm up for background search, third read without using [[#spider_split_read|spider_split_read]] and [[#spider_semi_split_read|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##
* **DSN Parameter Name:** ##frd##    
==== ##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, otherwise continues processing 
**##2## Processing continues disregarding all the errors.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##0##    
==== ##spider_general_log##
* **Description:**  Whether to log all Spider commands to the general log. Error codes are logged according to the [[#spider_log_result_errors|spider_log_result_errors]] variable. 
**##OFF## No commands are logged. 
**##ON## Commands are logged to the general log.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##OFF## 
==== ##spider_init_sql_alloc_size##
* **Description:** Initial size of the local SQL buffer.
**##-1## The table parameter is adopted.
**##0 or more## Size of the buffer.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##1024## 
* **DSN Parameter Name:** ##isa## 
==== ##spider_internal_limit##
* **Description:** Limit the number of records when acquired from remote server. 
**##-1## The table parameter is adopted.
**##0 or more## Number of records limit.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##9223372036854775807##
* **DSN Parameter Name:** ##ilm##  
==== ##spider_internal_offset                
* **Description:** Skip records when acquired from the remote server.
**##-1## The table parameter is adopted.
**##0## or more : Number of records to skip.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##ios##  
==== ##spider_internal_optimize##
* **Description:** Push down of [[optimize-table|OPTIMIZE TABLE]] 
**##-1## The table parameter is adopted.
**##0## Transmitted.
**##1## Not transmitted.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##iom##       
==== ##spider_internal_optimize_local##
* **Description:** Whether to transmit to the remote server when [[optimize_table|OPTIMIZE TABLE]] is executed on the local server.
**##-1## The table parameter is adopted.
**##0## Not transmitted.
**##1## Transmitted.
* **Default Session Value:** ##-1##    
* **DSN Parameter Name:** ##iol##    
==== ##spider_internal_sql_log_off##
* **Description:** Whether SQL transmitted to the remote server is logged to the [[general-query-log|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##
* **Description:** Whether or not unlock tables is transmitted to the connection of the table used with the SELECT.
**##0## Not transmitted.
**##1## Transmitted.
* **Data Type:** ##numeric##
* **Default Session Value:** ##0##       
==== ##spider_internal_xa##
* **Description:** Whether XA implementation is at the server or the storage engine level. Set different server ids 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 backends.  
**##0## Raise error when use a spider table
**##1## Raise error when START TRANSACTION
**##2## Take a consistent CONSISTENT SNAPSHOT on each backend . But global consistency is lost
**##3## 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:** Whether or not [[transactions-lock|LOCK TABLES]] is pushed down to the remote server.
**##0## Transmitted.
**##1## Not transmitted.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##0##                
==== ##spider_lock_exchange## 
* **Description:** Whether to convert SELECT LOCK IN SHARE MODE and SELECT FOR UPDATE to LOCK TABLE
**##0## Not converted.
**##1## Converted.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##0##   
==== ##spider_log_result_errors##
* **Description:** Whether to log results from the data node to the Spider node into the [[error-log|error log]]. The default value is ##0##, no logging.
**##0## : No errors are logged.
**##1## : Errors are logged.
**##2## : As for ##1##, as well as logging warning summaries.
**##3## : As for ##2## as well as logging warning details.
**##4## : As for ##3## as well as logging all result summaries.
==== ##spider_low_mem_read## 
* **Description:** Whether to use low memory mode when a query internally issued to a remote server is executed and gets a result list.
**##-1## The table parameter is adopted.
**##0## Low memory mode is not used.
**##0## Low memory mode is used.
* **Data Type:** ##numeric##
* **Default Value:** ##-1##    
==== ##spider_max_order##
* **Description:** Maximum number of columns for ORDER BY.
**##-1## The table parameter is adopted.
**##0 and more## Maximum number of columns.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Data Range:** ##-1,32767##
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##32767#
* **DSN Parameter Name:** ##mod##    
==== ##spider_multi_split_read##
* **Description:** Determines whether to divide a statement into multiple SQL statements sent to the remote backend server when a range retrieval is resolved to multiple conditions by the optimizer.
**##-1## The table parameter is adopted.
**##0## Not divided.
**##1## Divided.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##msr##     
==== ##spider_net_read_timeout##
* **Description:** The TCP timeout in seconds to declare remote backend not responsive when reading from a connection. Can be changed for slow latency networks. 
**##-1## The table parameter is adopted.
**##0## Less than 1 second timeout.
**##1 and more## Timeout in seconds.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Max Value:** ##2147483647##     
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##nrt##          
==== ##spider_net_write_timeout##
* **Description:** The TCP timeout in seconds to declare remote backend not responsive when writing to a connection. Can be changed for slow latency networks. 
**##-1## The table parameter is adopted.
**##0## Less than 1 second timeout.
**##1 and more## Timeout in seconds.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Max Value:** ##2147483647##     
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##nwt##             
==== ##spider_ping_interval_at_trx_start## 
* **Description:** Reset the connection keepalive timeout in seconds by sending a ping. 
**##0## At every transaction.
**##1 and more## Number of seconds.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Max Value:** ##2147483647##     
* **Default Session Value:** ##3600##    
==== ##spider_quick_mode##
* **Description:** Type of backend query buffering, either buffer on the remote backend server or in the local buffer.
**##-1## The table parameter is adopted.
**##0## Local buffering, it acquires records collectively with store_result.
**##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
**##3## Local buffering, use temporary table on disk when resultset > spider_quick_page_size  
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##qmd##                
==== ##spider_quick_page_size
* **Description:** Number of records in a page when acquired record by record. 
**##-1## The table parameter is adopted.
**##0 or more## Number of records.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##100##
* **DSN Parameter Name:** ##qps##
==== ##spider_read_only_mode##
* **Description:** Whether to set Spider tables to read only 
**##-1## The table parameter is adopted.
**##0## Writeable
**##1## Read only
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##rom##          
==== ##spider_remote_access_charset##  
* **Description:** Force session character set when connecting to the backend server. Can improve connection time performance. 
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##string##   
* **Default Session Value:** ##null##       
==== ##spider_remote_autocommit## 
* **Description:** Set auto commit mode when connecting to the backend server.  Can improve connection time performance. 
**##-1## Don't set.
**##0## autocommit = 0
**##1## autocommit = 1
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##   
==== ##spider_remote_default_database##
* **Description:** Set the local default database when connecting to the backend server. Can improve connection time performance. 
**##0## OFF
**##1## ON
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##ON##     
==== ##spider_remote_sql_log_off##      
* **Description:** Set [[server-system-variables#sql_log_off|sql_log_off]] when connecting to the backend server.
**##-1## Don't set.
**##0## Don't log spider SQL to remote backend
**##1## Log on remote backend
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##       
==== ##spider_remote_time_zone##
* **Description:** Force time zone when connecting to the backend. Can improve the performance of the connection if you know the time zone.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##string##   
* **Default Session Value:** ##null## 
==== ##spider_remote_trx_isolation##
* **Description:**  Set [[set-transaction#isolation-levels|transaction isolation level]] when connecting to the backend server. 
**##-1## Don't set.
**##0## READ UNCOMMITTED
**##1## READ COMMITTED
**##2## REPEATABLE READ
**##3## SERIALIZABLE
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##        
==== ##spider_reset_sql_alloc##
* **Description:** Reset per connection SQL buffer after SQL is executed.
**##-1## The table parameter is adopted.
**##0## Not reset.
**##1## Reset.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##1##
* **DSN Parameter Name:** ##rsa##        
==== ##spider_same_server_link##
* **Description:** Enable linking a table to the same local instance.
**##0## OFF
**##1## ON
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##OFF##            
==== ##spider_second_read##
* **Description:** Number of second read records on the backend server when the first records are found from [[#spider_first_read|spider_first_read]], the engine will continue scanning a range adding a //LIMIT spider_first_read, spider_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:** ##9223372036854775807##
* **DSN Parameter Name:** ##srd##
==== ##spider_select_column_mode##
* **Description:** Set preferred mode of column retrieval from remote backend server.
**##-1## The table parameter is adopted.
**##0## Use index columns if the SELECT statement can solve by using an index, otherwise use all columns.
**##1## Use columns that are judged necessary.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##1##
* **DSN Parameter Name:** ##scm##    
==== ##spider_selupd_lock_mode##
* **Description:** Type of local lock mode on INSERT SELECT.
**##-1## The table parameter is adopted.
**##0## No lock taken.
**##1## Shared lock taken.
**##2## Exclusive lock taken.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##1##
* **DSN Parameter Name:** ##slm#    
==== ##spider_semi_split_read## 
* **Description:** Whether or not to use chunk retrieval with offset and limit parameter on the remote backend server SQL when using [[#spider_split_read|spider_split_read]].
**##-1## The table parameter is adopted.
**##0## Chunk retrieval not used.
**##1 or more ## Chunk retrieval used. 
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##ssr#    
==== ##spider_semi_split_read_limit
* **Description:** The limit value for [[#spider_semi_split_read|semi_split_read]].
 **##-1## The table parameter is adopted.
**##0 or more ## The limit value.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##    
* **Default Table Value:** ##9223372036854775807##
* **DSN Parameter Name:** ##ssl#    
==== ##spider_semi_table_lock##
* **Description:** For non transactional storage engines, add [[transactions-lock|LOCK TABLES]] to the remote backends during SQL execution to preserve consistency between roundtrips.   
**##0## Off
**##1## On
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##OFF##
* **DSN Parameter Name:** ##stl#   
==== ##spider_semi_table_lock_connection## 
* **Description:**Use multiple connections for [[#spider_semi_table_lock|semi_table_lock]].
**##-1## The table parameter is adopted.
**##0## Use same connection.
**##1## Use different connection.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##   
* **Default Session Value:** ##-1##
* **Default Table Value:** ##1##
* **DSN Parameter Name:** ##stc#        
==== ##spider_semi_trx##
* **Description:** Used to control backend consistency of transactions when an SQL statement is split into multiple statements to the backend. Consistency can be preserved or relaxed. Auto-committed SQL will be encapsulated inside a transaction on the remote backend. You should use ##spider_semi_trx_isolation=2##, if you use read commited or read uncommitted to force consistency.
**##0## OFF
**##1## ON
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##ON##
==== ##spider_semi_trx_isolation##
 * **Description:** Set consistency during range SQL execution when [[#spider_sync_trx_isolation|spider_sync_trx_isolation]] is 1
**##-1## OFF
**##0## READ UNCOMMITTED
**##1## READ COMMITTED
**##2## REPEATABLE READ
**##3## SERIALIZABLE
 * **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
==== ##spider_skip_default_condition##  
 * **Description:** Whether or not to compute condition push down. 
**##-1## The table parameter is adopted.
**##0## Compute condition push down.
**##1## Don't compute condition push down.
 * **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##0##
* **DSN Parameter Name:** ##sdc##
==== ##spider_split_read##
* **Description:** Number of records in the chunk used to retry the result when a range query is sent to a remote backend server.
**##-1## The table parameter is adopted.
**## 0 or more## Number of records.
 * **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##9223372036854775807##
* **DSN Parameter Name:** ##srd##    
==== ##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 [[#spider_sts_interval|sts_interval=0]], effective with the default ##sts_interval = 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##
* **DSN Parameter Name:** ##sbm## 
==== ##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 seconds for table state confirmation.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##10## 
* **DSN Parameter Name:** ##siv##     
==== ##spider_sts_mode##     
 * **Description:**  Mode for table statistics. The [[show|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##
* **DSN Parameter Name:** ##smd## 
==== ##spider_sts_sync##
* **Description:** Whether or not table statistics are synchronized in partitioned tables.
**##-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##
* **DSN Parameter Name:** ##ssy##             
==== ##spider_support_xa##
* **Description:** Use xa protocol for mirroring and for multi shard transactions.
**##1## Use xa protocol
**##0## Don't use xa protocol.
* **Scope:** Global
* **Dynamic:** No
* **Data Type:** ##numeric##
* **Default Table Value:** ##1##             
==== ##spider_sync_autocommit##
* **Description:** Whether or not the local AUTO COMMIT is pushed down to remote backends.
**##OFF## Not pushed down.
**##ON## Pushed down.
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##ON##     
==== ##spider_sync_time_zone##
* **Description:** Whether or not the local TIME ZONE is pushed down to remote backends.
**##OFF## Not pushed down.
**##ON## Pushed down.
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##ON##     
==== ##spider_sync_trx_isolation##
* **Description:** Whether or not the local transaction isolation level is pushed down to remote backends.
**##ON## Not pushed down.
**##OFF## Pushed down.
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##ON##                   
==== ##spider_table_init_error_interval## 
* **Description:** Interval in seconds for which the same error code is returned if the table init fails, Used to protect against endless loops in the table link.
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Value:** ##1##
* **Range:** ##-1## to ##4294967295##
==== ##spider_udf_ct_bulk_insert_interval
* **Description:** Interval in milliseconds between bulk inserts at copying.
**##-1## The UDF parameter is adopted.
**##0 and more## Time in milliseconds.
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##4294967295##
* **Range:** ##-1## to ##4294967295##            
==== ##spider_udf_ct_bulk_insert_rows
* **Description:**  Number of rows inserted at a time during a bulk insert when copying. 
**##-1, 0##: The table parameter is adopted.
**##1 or more## Number of rows
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##18446744073709551615##
* **Range:** ##-1# to ##9223372036854775807##            
==== ##spider_udf_ds_bulk_insert_rows
* **Description:** Number of rows inserted at a time during a bulk insert when result set is stored in a temporary table on executing a UDF.
**##-1, 0## The UDF parameter is adopted.
**##1 or more## Number of rows
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##18446744073709551615##
* **Range:** ##-1## upwards     
==== ##spider_udf_ds_table_loop_mode##
* **Description:** Whether or not the result set is stored in same temporary table if the temporary table list count at UDF is less than the result set count at executing UDF.
**##-1## The table parameter is adopted.
**##0## Drop records.
**##1## Insert last table.
**##2## Insert first table and loop again.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
==== ##spider_udf_ds_use_real_table##   
==== ##spider_udf_table_lock_mutex_count##      
==== ##spider_udf_table_mon_mutex_count##       
==== ##spider_use_all_conns_snapshot##
* **Description:** Whether or  not START TRANSACTION WITH SNAPSHOT is passed to all connections  
**##OFF## Not passed.
**##ON## Passed to all connections.
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##ON##       
==== ##spider_use_consistent_snapshot##
* **Description:** Whether or not a local START TRANSACTION WITH CONSISTENT is pushed down to backends.
**##OFF## Not pushed down .
**##ON## Pushed down.
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##boolean##
* **Default Session Value:** ##ON##           
==== ##spider_use_default_database##  
* **Description:** Whether or not the default database is used.
**##OFF## Default database not used.
**##ON## Use default database.
* **Scope:** Global
* **Dynamic:** Yes
* **Data Type:** ##boolean##    
* **Default Session Value:** ##ON##     
==== ##spider_use_flash_logs##
* **Description:** Whether or not [[flush-logs|FLUSH LOGS]] is pushed down to remote backends
**##OFF## Not pushed down.
**##ON## Pushed 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 disabled if [[#spider_sync_trx_isolation|spider_sync_trx_isolation =0]] to avoid to using handler command on serializable isolation level.
**##0## HANDLER converted to SELECT 
**##1## HANDLER passed to the backend 
**##2## SQL is converted to HANDLER 
**##3## SQL is converted to HANDLER and HANDLER is converted to SQL
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##0##
* **DSN Parameter Name:** ##uhd## 
==== ##spider_use_pushdown_udf##
* **Description:** When engine_condition_pushdown=1 and a UDF function is used in a condition, determines whether the UDF execution is local or pushed down.
**##-1## The table parameter is adopted.
**##0## Not transmitted.
**##1## Transmitted.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##1##
* **DSN Parameter Name:** ##upu## 
 ==== ##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:** Whether or not to use the local table [[data-types-character-sets-and-collations|character set]] for the backend connection.
**##-1## The table parameter is adopted.
**##0## Use utf8.
**##1## Use table character set.
* **Scope:** Global, Session
* **Dynamic:** Yes
* **Data Type:** ##numeric##
* **Default Session Value:** ##-1##
* **Default Table Value:** ##1##     
* **DSN Parameter Name:** ##utc##

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.

Spider Server System Variables - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB