Storage Layer Variables

PolarDB-X embraces a decoupled storage and computation architecture. The variable names and their meanings in the storage layer (DN) are aligned with MySQL. Here, we mainly list the common DN variables that can be configured via the console. For other variables, you can refer to MySQL Variables.

Name Restart Required Default Value Range Comments
loose_enable_gts Yes 1 [0|1] Switch for the global timestamp.
loose_gts_lease Yes 5000 [0-10000] Lease time for the global timestamp.
loose_performance-schema-instrument No wait/lock/metadata/sql/mdl=ON .* For retrieving MDL lock information during MySQL runtime.
performance_schema No ON [ON,OFF] Monitors MySQL server execution details.
binlog_rows_query_key_content No ON [ON,OFF] Logs SQL to the binlog.
loose_polarx_max_allowed_packet No 16777216 (0-2147483648) Limits the size of data packets sent and received under a private protocol server.
innodb_buffer_pool_load_at_startup Yes ON [ON|OFF] Whether to reload the buffer pool after startup.
bulk_insert_buffer_size No 4194304 [0-4294967295] Temporary cache size for buffering data during bulk insert operations.
show_old_temporals No OFF [ON|OFF]
ft_query_expansion_limit Yes 20 [0-1000] Specifies the maximum number of matches for full-text searches using query expansion on MyISAM engine tables.
innodb_old_blocks_time No 1000 [0-1024] Time a page must wait after being read into the mid position before it can be moved to the hot end of the LRU list.
innodb_stats_sample_pages No 8 [1-4294967296] Controls the precision of statistics collection.
thread_stack Yes 262144 [131072-18446744073709551615] Memory size allocated for each thread when a new connection is created in MySQL.
lc_time_names No en_US [ja_JP|pt_BR|en_US] Controls the language used to display names and abbreviations of dates and months.
innodb_thread_concurrency No 0 [0-1000] Limits the number of concurrent operations.
default_time_zone Yes SYSTEM [SYSTEM|-12:00|
-11:00|-10:00|
-9:00|-8:00|
-7:00|-6:00|
-5:00|-4:00|
-3:00|-2:00|
-1:00|
\+0:00|\+1:00|
\+2:00|\+3:00|
\+4:00|\+5:00|
\+5:30|\+5:45|
\+6:00|\+6:30
|\+7:00|\+8:00
|\+9:00|\+10:00|
\+11:00|\+12:00|
\+13:00]		 Settings for the time zone.
old_passwords No 0 [0|2]
optimizer_search_depth No 62 [0-62] Controls the recursive depth of the optimizer in multi-table join scenarios.
innodb_compression_level No 6 [0-9]
loose_innodb_log_optimize_ddl No OFF [ON|OFF]
max_sort_length No 1024 [4-8388608]
slave_pending_jobs_size_max No 1073741824 [1024-18446744073709551615]
innodb_online_alter_log_max_size No 134217728 [134217728-2147483647]
key_cache_block_size No 1024 [512-16384]
mysql_native_password_proxy_users No OFF [ON|OFF]
innodb_adaptive_max_sleep_delay No 150000 [1-1000000]
innodb_purge_rseg_truncate_frequency No 128 [1-128]
query_alloc_block_size No 8192 [1024-16384]
innodb_lock_wait_timeout No 50 [1-1073741824]
innodb_purge_threads Yes 1 [1-32]
innodb_compression_failure_threshold_pct No 5 [0-100]
innodb_compression_pad_pct_max No 50 [0-70]
binlog_rows_query_log_events No ON [ON|OFF]
innodb_stats_persistent_sample_pages No 20 [0-4294967295]
innodb_ft_total_cache_size Yes 640000000 [32000000-1600000000]
innodb_flush_method Yes O_DIRECT [fsync|O_DSYNC|littlesync|nosync|O_DIRECT|O_DIRECT_NO_FSYNC]
eq_range_index_dive_limit No 10 [0-4294967295]
loose_max_execution_time No 0 [0-4294967295]
loose_optimizer_trace_features No greedy_search=on,
range_optimizer=on,
dynamic_range=on,
repeated_subselect=on		 .*
rds_reserved_connections No 512 [0-512]
connect_timeout No 10 [1-3600]
innodb_purge_batch_size Yes 300 [1-5000]
div_precision_increment No 4 [0-30]
avoid_temporal_upgrade No OFF [ON|OFF]
innodb_sync_array_size Yes 1 [1-64]
sync_binlog No 1 [0-2147483647]
innodb_stats_method No nulls_equal [nulls_equal|nulls_unequal|nulls_ignored]
lock_wait_timeout No 31536000 [1-1073741824]
net_read_timeout No 30 [1-18446744073709551615]
innodb_deadlock_detect No ON [ON|OFF]
innodb_write_io_threads Yes 4 [1-64]
end_markers_in_json No OFF [ON|OFF]
ngram_token_size Yes 2 [0-20]
loose_innodb_numa_interleave Yes ON [ON|OFF]
max_binlog_stmt_cache_size No 18446744073709547520 [4096-18446744073709547520]
innodb_checksum_algorithm No crc32 [innodb|crc32|none|strict_innodb|strict_crc32|strict_none]
query_cache_type Yes 0 [0|1|2]
innodb_ft_enable_diag_print No OFF [ON|OFF]
innodb_ft_enable_stopword No ON [ON|OFF]
innodb_io_capacity No 20000 [0-18446744073709551615]
slow_launch_time No 2 [1-1024]
innodb_table_locks No ON [ON|OFF]
innodb_stats_persistent No ON [ON|OFF]
tmp_table_size No 2097152 [262144-134217728]
disconnect_on_expired_password Yes ON [ON|OFF]
default_storage_engine Yes InnoDB [InnoDB|innodb]
net_retry_count No 10 [1-4294967295]
innodb_ft_cache_size Yes 8000000 [1600000-80000000]
binlog_cache_size No 2097152 [4096-16777216]
innodb_max_dirty_pages_pct No 75 [0-99]
query_cache_limit No 1048576 [1-1048576]
innodb_disable_sort_file_cache No ON [ON|OFF]
innodb_lru_scan_depth No 1024 [100-18446744073709551615]
innodb_ft_result_cache_limit No 2000000000 [1000000-4294967295]
long_query_time No 1 [0.03-31536000]
interactive_timeout No 7200 [10-86400]
innodb_read_io_threads Yes 4 [1-64]
transaction_prealloc_size No 4096 [1024-131072]
open_files_limit Yes 65535 [1-18446744073709551615]
innodb_open_files Yes 3000 [10-4294967295]
max_heap_table_size No 67108864 [16384-1844674407370954752]
automatic_sp_privileges No ON [ON|OFF]
explicit_defaults_for_timestamp No OFF [ON|OFF]
ft_max_word_len Yes 84 [10-4294967295]
innodb_autoextend_increment No 64 [1-1000]
sql_mode No (\s*|REAL_AS_FLOAT|
PIPES_AS_CONCAT|ANSI_QUOTES|
IGNORE_SPACE|ONLY_FULL_GROUP_BY|
NO_UNSIGNED_SUBTRACTION|NO_DIR_IN_CREATE&
vert;POSTGRESQL|ORACLE|
MSSQL|DB2|MAXDB|
NO_KEY_OPTIONS|NO_TABLE_OPTIONS|
NO_FIELD_OPTIONS|
MYSQL323|MYSQL40|
ANSI|NO_AUTO_VALUE_ON_ZERO|
NO_BACKSLASH_ESCAPES|STRICT_TRANS_TABLES|
STRICT_ALL_TABLES|NO_ZERO_IN_DATE|
NO_ZERO_DATE|ALLOW_INVALID_DATES|
ERROR_FOR_DIVISION_BY_ZERO|TRADITIONAL|
HIGH_NOT_PRECEDENCE|NO_ENGINE_SUBSTITUTION|
PAD_CHAR_TO_FULL_LENGTH|
NO_AUTO_CREATE_USER)
(NO_AUTO_CREATE_USER|
,REAL_AS_FLOAT|
,PIPES_AS_CONCAT|
,ANSI_QUOTES|
,IGNORE_SPACE|
,ONLY_FULL_GROUP_BY|,
NO_UNSIGNED_SUBTRACTION|
,NO_DIR_IN_CREATE|
,POSTGRESQL|
,ORACLE|,MSSQL|
,DB2|,MAXDB|
,NO_KEY_OPTIONS|,NO_TABLE_OPTIONS|
,NO_FIELD_OPTIONS|,MYSQL323|
,MYSQL40|,ANSI|
,NO_AUTO_VALUE_ON_ZERO|,NO_BACKSLASH_ESCAPES|
,STRICT_TRANS_TABLES|,STRICT_ALL_TABLES|
,NO_ZERO_IN_DATE|,NO_ZERO_DATE|
,ALLOW_INVALID_DATES|,ERROR_FOR_DIVISION_BY_ZERO|
,TRADITIONAL|,HIGH_NOT_PRECEDENCE|
,NO_ENGINE_SUBSTITUTION|,PAD_CHAR_TO_FULL_LENGTH)*
innodb_stats_transient_sample_pages No 8 [1-4294967295]
innodb_random_read_ahead No OFF [ON|OFF]
session_track_state_change No OFF [ON|OFF]
table_open_cache No 2000 [1-524288] The size of the table file handle cache.
range_optimizer_max_mem_size No 8388608 [0-18446744073709551615]
innodb_status_output No OFF [ON|OFF]
innodb_log_compressed_pages No OFF [ON|OFF]
slave_net_timeout No 60 [15-300]
delay_key_write No ON [ON|OFF|ALL]
query_cache_wlock_invalidate No OFF [ON|OFF]
max_points_in_geometry No 65536 [3-1048576]
max_prepared_stmt_count No 16382 [0-1048576]
wait_timeout No 86400 [1-31536000]
query_cache_min_res_unit No 1024 [512-18446744073709551608] Result cache configuration.
innodb_print_all_deadlocks No OFF [OFF|ON]
loose_thread_pool_size No 32 [1-1024] The number of groups, with a default value of 32. Threads in the thread pool are evenly distributed across several groups for management.
binlog_stmt_cache_size No 32768 [4096-16777216]
transaction_isolation No READ-COMMITTED [READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE] Transaction Isolation level strategy.
innodb_buffer_pool_dump_at_shutdown No ON [ON|OFF]
query_prealloc_size No 8192 [8192-1048576]
key_cache_age_threshold No 300 [100-4294967295]
loose_rds_kill_connections No 20 [0, 18446744073709551615]
transaction_alloc_block_size No 8192 [1024-131072]
optimizer_trace_limit No 1 [0-4294967295]
metadata_locks_cache_size Yes 1024 [1-1048576]
optimizer_prune_level No 1 [0|1]
innodb_max_purge_lag No 0 [0-4294967295]
innodb_buffer_pool_dump_pct No 25 [1-100]
innodb_max_dirty_pages_pct_lwm No 0 [0-99]
max_sp_recursion_depth No 0 [0-255]
innodb_status_output_locks No OFF [ON|OFF]
updatable_views_with_limit No YES [YES|NO]
binlog_row_image No full [full|minimal]
innodb_change_buffer_max_size No 25 [0-50]
innodb_optimize_fulltext_only No OFF [ON|OFF]
loose_opt_rds_last_error_gtid No ON [ON|OFF]
innodb_ft_max_token_size Yes 84 [10-84]
innodb_max_undo_log_size No 1073741824 [10485760-18446744073709551615]
slave_parallel_type No LOGICAL_CLOCK DATABASE,LOGICAL_CLOCK
loose_rds_check_core_file_enabled No ON [ON|OFF]
innodb_adaptive_hash_index No ON [ON|OFF]
innodb_sync_spin_loops No 30 [0-4294967295]
net_write_timeout No 60 [1-18446744073709551615]
flush_time No 0 [0-31536000]
lower_case_table_names Yes 1 [0|1]
sha256_password_proxy_users No OFF [ON|OFF]
character_set_filesystem No binary [utf8|latin1|gbk|binary]
innodb_flush_sync No ON [ON|OFF]
tls_version Yes TLSv1,TLSv1.1,TLSv1.2 [TLSv1,TLSv1.1,TLSv1.2|TLSv1,TLSv1.1]
key_cache_division_limit No 100 [1-100]
delayed_insert_timeout No 300 [1-3600]
preload_buffer_size No 32768 [1024-1073741824]
innodb_read_ahead_threshold No 56 [0-1024]
loose_optimizer_switch No index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
subquery_materialization_cost_based=on,
use_index_extensions=on		 .*
concurrent_insert No 1 [0|1|2] Concurrent insert feature settings.
block_encryption_mode No "aes-128-ecb" ["aes-128-ecb"|"aes-192-ecb"|"aes-256-ecb"|"aes-128-cbc"|"aes-192-cbc"|"aes-256-cbc"]
slow_query_log No ON [ON|OFF] Logging slow queries.
net_buffer_length No 16384 [1024-1048576]
query_cache_size No 3145728 [0-104857600]
delayed_insert_limit No 100 [1-4294967295]
innodb_large_prefix No OFF [ON|OFF]
innodb_monitor_disable No all
innodb_adaptive_flushing_lwm No 10 [0-70]
innodb_log_checksums No ON [ON|OFF]
delayed_queue_size No 1000 [1-4294967295]
session_track_gtids No OFF [OFF|OWN_GTID|ALL_GTIDS]
innodb_thread_sleep_delay No 10000 [0-1000000]
loose_rds_set_connection_id_enabled No ON [ON|OFF]
innodb_old_blocks_pct No 37 [5-95]
innodb_ft_sort_pll_degree Yes 2 [1-16]
log_slow_admin_statements No OFF [ON|OFF]
innodb_stats_on_metadata No OFF [ON|OFF]
stored_program_cache No 256 [16-524288]
group_concat_max_len No 1024 [4-1844674407370954752]
innodb_sort_buffer_size Yes 1048576 [65536-67108864]
innodb_page_cleaners Yes 1 [1-64]
innodb_spin_wait_delay No 6 [0-4294967295]
myisam_sort_buffer_size No 262144 [262144-16777216]
innodb_rollback_segments No 128 [1-128]
innodb_commit_concurrency Yes 0 [0-1000]
innodb_concurrency_tickets No 5000 [1-4294967295]
table_definition_cache No 512 [400-524288]
auto_increment_increment No 1 [1-65535]
binlog_checksum Yes CRC32
max_seeks_for_key No 18446744073709500000 [1-18446744073709551615]
sync_relay_log No 1 [0-2147483647]
max_length_for_sort_data No 1024 [0-838860]
back_log Yes 3000 [0-65535]
max_error_count No 64 [0-65535]
innodb_io_capacity_max No 40000 [0-18446744073709551615]
innodb_strict_mode No OFF [ON|OFF]
binlog_order_commits Yes OFF
min_examined_row_limit No 0 [0-4294967295]
innodb_ft_min_token_size Yes 3 [0-16]
innodb_stats_auto_recalc No ON [ON|OFF]
max_connect_errors No 100 [0-4294967295]
session_track_schema No ON [ON|OFF]
join_buffer_size No 262144 [128-4294967295]
innodb_change_buffering No all [none|inserts|deletes|changes|purges|all]
optimizer_trace_max_mem_size No 16384 [0-4294967295]
innodb_autoinc_lock_mode Yes 2 [0|1|2]
innodb_rollback_on_timeout Yes OFF [OFF|ON]
loose_opt_rds_enable_show_slave_lag No ON [ON|OFF]
max_write_lock_count No 102400 [1-102400]
master_verify_checksum Yes OFF
innodb_ft_num_word_optimize No 2000 [0-10000]
max_join_size No 18446744073709551615 [1-18446744073709551615]
loose_validate_password_length No 8 [1-12]
log_throttle_queries_not_using_indexes No 0 [0-4294967295]
innodb_max_purge_lag_delay No 0 [0-10000000]
loose_optimizer_trace No enabled=off,one_line=off .*
loose_thread_handling Yes one-thread-per-connection [one-thread-per-connection|pool-of-threads]
default_week_format No 0 [0-7]
innodb_cmp_per_index_enabled No OFF [ON|OFF]
show_compatibility_56 No OFF [ON|OFF]
host_cache_size No 644 [0-65535] Host cache size.
low_priority_updates No 0 [0|1]
auto_increment_offset No 1 [1-65535] Increment step for auto-Increment columns.
range_alloc_block_size No 4096 [4096-18446744073709551615]
ft_min_word_len Yes 4 [1-3600]
sort_buffer_size No 262144 [32768-4294967295] Buffer size for sorting operations.
max_allowed_packet No 1073741824 [16384-1073741824] Maximum packet size accepted by the server.
thread_cache_size No 256 [0-16384] Thread cache for execution.
optimizer_trace_offset No -1 [0-4294967295]
character_set_server Yes utf8 [utf8|latin1|gbk|gb18030|utf8mb4] Character set at the database level.
innodb_adaptive_flushing No ON [ON|OFF] Switch for adaptive flushing of dirty pages.
log_queries_not_using_indexes No OFF [ON|OFF] Queries not using indexes will not be logged in the slow Log.
innodb_monitor_enable No all
table_open_cache_instances Yes 16 [1-64] Number of table cache instances.
innodb_flush_neighbors No 1 [0|1|2] Controls flushing of neighboring dirty pages along with dirty pages to disk in buffer pool.
innodb_buffer_pool_instances Yes 1 [1-64] Size of buffer pool instances.
innodb_data_file_purge No OFF [ON|OFF] Enabling asynchronous purge strategy.
innodb_data_file_purge_all_at_shutdown No OFF [ON|OFF] Complete cleanup on normal shutdown.
innodb_data_file_purge_immediate No OFF [ON|OFF] Unlink data files without cleanup.
innodb_data_file_purge_interval No 100 [1-1073741824] Cleanup interval in milliseconds.
innodb_data_file_purge_max_size No 512 [1-1073741824] Maximum size for single file cleanup per operation in megabytes.
hotspot No OFF [ON|OFF] Switch for hotspot update.
hotspot_lock_type No OFF [ON|OFF] Lock type for hotspot update.

results matching ""

    No results matching ""