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

  1. spider_auto_increment_mode
  2. spider_bgs_first_read
  3. spider_bgs_mode
  4. spider_bgs_second_read
  5. spider_bka_engine
  6. spider_bka_mode
  7. spider_block_size
  8. spider_bulk_access_free
  9. spider_bulk_size
  10. spider_bulk_update_mode
  11. spider_bulk_update_size
  12. spider_conn_recycle_mode
  13. spider_conn_recycle_strict
  14. spider_connect_mutex
  15. spider_connect_retry_count
  16. spider_connect_retry_interval
  17. spider_connect_timeout
  18. spider_crd_bg_mode
  19. spider_crd_interval
  20. spider_crd_mode
  21. spider_crd_sync
  22. spider_crd_type
  23. spider_crd_weight
  24. spider_direct_dup_insert
  25. spider_direct_order_limit
  26. spider_error_read_mode
  27. spider_error_write_mode
  28. spider_first_read
  29. spider_force_commit
  30. spider_general_log
  31. spider_init_sql_alloc_size
  32. spider_internal_limit
  33. spider_internal_offset
  34. spider_internal_optimize
  35. spider_internal_optimize_local
  36. spider_internal_sql_log_off
  37. spider_internal_unlock
  38. spider_internal_xa
  39. spider_internal_xa_snapshot
  40. spider_local_lock_table
  41. spider_lock_exchange
  42. spider_log_result_errors
  43. spider_low_mem_read
  44. spider_max_order
  45. spider_multi_split_read
  46. spider_net_read_timeout
  47. spider_net_write_timeout
  48. spider_ping_interval_at_trx_start
  49. spider_quick_mode
  50. spider_quick_page_size
  51. spider_read_only_mode
  52. spider_remote_access_charset
  53. spider_remote_autocommit
  54. spider_remote_default_database
  55. spider_remote_sql_log_off
  56. spider_remote_time_zone
  57. spider_remote_trx_isolation
  58. spider_reset_sql_alloc
  59. spider_same_server_link
  60. spider_second_read
  61. spider_select_column_mode
  62. spider_selupd_lock_mode
  63. spider_semi_split_read
  64. spider_semi_split_read_limit
  65. spider_semi_table_lock
  66. spider_semi_table_lock_connection
  67. spider_semi_trx
  68. spider_semi_trx_isolation
  69. spider_skip_default_condition
  70. spider_split_read
  71. spider_sts_bg_mode
  72. spider_sts_interval
  73. spider_sts_mode
  74. spider_sts_sync
  75. spider_support_xa
  76. spider_sync_autocommit
  77. spider_sync_time_zone
  78. spider_sync_trx_isolation
  79. spider_table_init_error_interval
  80. spider_udf_ct_bulk_insert_interval
  81. spider_udf_ct_bulk_insert_rows
  82. spider_udf_ds_bulk_insert_rows
  83. spider_udf_ds_table_loop_mode
  84. spider_udf_ds_use_real_table
  85. spider_udf_table_lock_mutex_count
  86. spider_udf_table_mon_mutex_count
  87. spider_use_all_conns_snapshot
  88. spider_use_consistent_snapshot
  89. spider_use_default_database
  90. spider_use_flash_logs
  91. spider_use_handler
  92. spider_use_pushdown_udf
  93. spider_use_table_charset

spider_auto_increment_mode

  • Description: The auto increment mode.
    • -1 The table parameter is adopted.
    • 0 Normal mode. Uses a count that Spider gets from the remote 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 count for the auto increment value. Fast, but it is possible for duplicates to occur if you update the same table from multiple Spider and self tables.
    • 2 Set zero value. Auto increment value is given by the remote server. 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 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 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

spider_bgs_first_read

  • Description: Number of first read records when background search is used. To start a range scan on the remote server the engine first needs to send the first record. Fetching a second record can save a network round trip stopping the plan if the shard gets a single record on the remote data source. First read and second read are used for warming 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 when background search is used. When the first records are found from spider_bgs_first_read, the engine will continue scanning using a limit spider_bgs_first_read, spider_bgs_second_read on the remote data source.
    • -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 engine when the bka_mode table parameter is 1. 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 as 1. Avoiding replication delay.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Session Value: -1
  • Default Table Value: 1

spider_block_size

  • Description: Pseudosize of block used in mysql. 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 reset
    • 1 in close
  • Data Type: numeric
  • Default Session Value: -1
  • Introduced: Spider 3.0

spider_bulk_size

  • Description: Size in bytes of insert query generated at 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 or 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

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

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

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 execution
    • 1- Push down execution
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Session Value: -1
  • Default Table Value: 0

spider_error_read_mode

spider_error_write_mode

spider_first_read

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 processing
    • 2 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
    • 0 It does not log
    • 1 It log
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Session Value: 0

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.
    • 0 Log SQL to remote server.
    • 1 Do not log SQL to remote server.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Session Value: 1

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.
    • 0 Engine protocol.
    • 1 Server protocol.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Session Value: 0

spider_internal_xa_snapshot

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

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_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

  • 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 protocol
    • 0 don't use xa protocol.
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Table Value: 1

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

  • Description: Conversion of HANDLER SQL command
    • 0 HANDLER are converted to SELECT
    • 1 HANDLER are 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

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.