存储层变量

PolarDB-X是存储计算分离架构，存储层（DN）的变量名和含义与MySQL对齐，这里主要罗列了常见的DN变量（控制台可配置），其他变量可以参考MySQL变量 。

名称 是否重启 默认值 修改范围 备注
loose_enable_gts 是 1 [0\ 1] 全局时间戳开关
loose_gts_lease 是 5000 [0-10000] 全局时间戳的租约时间
loose_performance-schema-instrument 否 wait/lock/metadata/sql/mdl=ON .* 用于获取MySQL运行时的MDL锁信息
performance_schema 否 ON [ON,OFF] 用于监控MySQL服务器的执行细节
binlog_rows_query_key_content 否 ON [ON,OFF] 打印sql到binlog日志
loose_polarx_max_allowed_packet 否 16777216 (0-2147483648) 限制私有协议下Server接受和发送的数据包大小
innodb_buffer_pool_load_at_startup 是 ON [ON\ OFF] 是否启动之后,重新加载buffer pool
bulk_insert_buffer_size 否 4194304 [0-4294967295] 用来缓存批量插入数据的时候临时缓存写入数
show_old_temporals 否 OFF [ON\ OFF]
ft_query_expansion_limit 是 20 [0-1000] 指定MyISAM引擎表使用 with query expansion 进行全文搜索的最大匹配数
innodb_old_blocks_time 否 1000 [0-1024] 用来表示页读取到mid位置后，需要等待多久才会被加入到LRU列表的热端
innodb_stats_sample_pages 否 8 [1-4294967296] 控制采集精度
thread_stack 是 262144 [131072-18446744073709551615] 每个连接线程被创建时，MySQL给它分配的内存大小
lc_time_names 否 en_US [ja_JP\ pt_BR\ en_US] 控制用于显示日期和月份名称及缩写的语言
innodb_thread_concurrency 否 0 [0-1000] 并发数限制
default_time_zone 是 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] 时区设置
old_passwords 否 0 [0\ 2]
optimizer_search_depth 否 62 [0-62] 在多表关联场景下，控制优化器递归深度
innodb_compression_level 否 6 [0-9]
loose_innodb_log_optimize_ddl 否 OFF [ON\ OFF]
max_sort_length 否 1024 [4-8388608]
slave_pending_jobs_size_max 否 1073741824 [1024-18446744073709551615]
innodb_online_alter_log_max_size 否 134217728 [134217728-2147483647]
key_cache_block_size 否 1024 [512-16384]
mysql_native_password_proxy_users 否 OFF [ON\ OFF]
innodb_adaptive_max_sleep_delay 否 150000 [1-1000000]
innodb_purge_rseg_truncate_frequency 否 128 [1-128]
query_alloc_block_size 否 8192 [1024-16384]
innodb_lock_wait_timeout 否 50 [1-1073741824]
innodb_purge_threads 是 1 [1-32]
innodb_compression_failure_threshold_pct 否 5 [0-100]
innodb_compression_pad_pct_max 否 50 [0-70]
binlog_rows_query_log_events 否 ON [ON\ OFF]
innodb_stats_persistent_sample_pages 否 20 [0-4294967295]
innodb_ft_total_cache_size 是 640000000 [32000000-1600000000]
innodb_flush_method 是 O_DIRECT [fsync\ O_DSYNC\ littlesync\ nosync\ O_DIRECT\ O_DIRECT_NO_FSYNC]
eq_range_index_dive_limit 否 10 [0-4294967295]
loose_max_execution_time 否 0 [0-4294967295]
loose_optimizer_trace_features 否 greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on .*
rds_reserved_connections 否 512 [0-512]
connect_timeout 否 10 [1-3600]
innodb_purge_batch_size 是 300 [1-5000]
div_precision_increment 否 4 [0-30]
avoid_temporal_upgrade 否 OFF [ON\ OFF]
innodb_sync_array_size 是 1 [1-64]
sync_binlog 否 1 [0-2147483647]
innodb_stats_method 否 nulls_equal [nulls_equal\ nulls_unequal\ nulls_ignored]
lock_wait_timeout 否 31536000 [1-1073741824]
net_read_timeout 否 30 [1-18446744073709551615]
innodb_deadlock_detect 否 ON [ON\ OFF]
innodb_write_io_threads 是 4 [1-64]
end_markers_in_json 否 OFF [ON\ OFF]
ngram_token_size 是 2 [0-20]
loose_innodb_numa_interleave 是 ON [ON\ OFF]
max_binlog_stmt_cache_size 否 18446744073709547520 [4096-18446744073709547520]
innodb_checksum_algorithm 否 crc32 [innodb\ crc32\ none\ strict_innodb\ strict_crc32\ strict_none]
query_cache_type 是 0 [0\ 1\ 2]
innodb_ft_enable_diag_print 否 OFF [ON\ OFF]
innodb_ft_enable_stopword 否 ON [ON\ OFF]
innodb_io_capacity 否 20000 [0-18446744073709551615]
slow_launch_time 否 2 [1-1024]
innodb_table_locks 否 ON [ON\ OFF]
innodb_stats_persistent 否 ON [ON\ OFF]
tmp_table_size 否 2097152 [262144-134217728]
disconnect_on_expired_password 是 ON [ON\ OFF]
default_storage_engine 是 InnoDB [InnoDB\ innodb]
net_retry_count 否 10 [1-4294967295]
innodb_ft_cache_size 是 8000000 [1600000-80000000]
binlog_cache_size 否 2097152 [4096-16777216]
innodb_max_dirty_pages_pct 否 75 [0-99]
query_cache_limit 否 1048576 [1-1048576]
innodb_disable_sort_file_cache 否 ON [ON\ OFF]
innodb_lru_scan_depth 否 1024 [100-18446744073709551615]
innodb_ft_result_cache_limit 否 2000000000 [1000000-4294967295]
long_query_time 否 1 [0.03-31536000]
interactive_timeout 否 7200 [10-86400]
innodb_read_io_threads 是 4 [1-64]
transaction_prealloc_size 否 4096 [1024-131072]
open_files_limit 是 65535 [1-18446744073709551615]
innodb_open_files 是 3000 [10-4294967295]
max_heap_table_size 否 67108864 [16384-1844674407370954752]
automatic_sp_privileges 否 ON [ON\ OFF]
explicit_defaults_for_timestamp 否 OFF [ON\ OFF]
ft_max_word_len 是 84 [10-4294967295]
innodb_autoextend_increment 否 64 [1-1000]
sql_mode 否 (\s*\ 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\ 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 否 8 [1-4294967295]
innodb_random_read_ahead 否 OFF [ON\ OFF]
session_track_state_change 否 OFF [ON\ OFF]
table_open_cache 否 2000 [1-524288] 表文件句柄高速缓存的大小
range_optimizer_max_mem_size 否 8388608 [0-18446744073709551615]
innodb_status_output 否 OFF [ON\ OFF]
innodb_log_compressed_pages 否 OFF [ON\ OFF]
slave_net_timeout 否 60 [15-300]
delay_key_write 否 ON [ON\ OFF\ ALL]
query_cache_wlock_invalidate 否 OFF [ON\ OFF]
max_points_in_geometry 否 65536 [3-1048576]
max_prepared_stmt_count 否 16382 [0-1048576]
wait_timeout 否 86400 [1-31536000]
query_cache_min_res_unit 否 1024 [512-18446744073709551608] 结果缓存配置
innodb_print_all_deadlocks 否 OFF [OFF\ ON]
loose_thread_pool_size 否 32 [1-1024] 分组的数量，默认值：32。线程池中的线程被平均分到多个组中进行管理
binlog_stmt_cache_size 否 32768 [4096-16777216]
transaction_isolation 否 READ-COMMITTED [READ-UNCOMMITTED\ READ-COMMITTED\ REPEATABLE-READ\ SERIALIZABLE] 事务隔离级别策略
innodb_buffer_pool_dump_at_shutdown 否 ON [ON\ OFF]
query_prealloc_size 否 8192 [8192-1048576]
key_cache_age_threshold 否 300 [100-4294967295]
loose_rds_kill_connections 否 20 [0, 18446744073709551615]
transaction_alloc_block_size 否 8192 [1024-131072]
optimizer_trace_limit 否 1 [0-4294967295]
metadata_locks_cache_size 是 1024 [1-1048576]
optimizer_prune_level 否 1 [0\ 1]
innodb_max_purge_lag 否 0 [0-4294967295]
innodb_buffer_pool_dump_pct 否 25 [1-100]
innodb_max_dirty_pages_pct_lwm 否 0 [0-99]
max_sp_recursion_depth 否 0 [0-255]
innodb_status_output_locks 否 OFF [ON\ OFF]
updatable_views_with_limit 否 YES [YES\ NO]
binlog_row_image 否 full [full\ minimal]
innodb_change_buffer_max_size 否 25 [0-50]
innodb_optimize_fulltext_only 否 OFF [ON\ OFF]
loose_opt_rds_last_error_gtid 否 ON [ON\ OFF]
innodb_ft_max_token_size 是 84 [10-84]
innodb_max_undo_log_size 否 1073741824 [10485760-18446744073709551615]
slave_parallel_type 否 LOGICAL_CLOCK DATABASE,LOGICAL_CLOCK
loose_rds_check_core_file_enabled 否 ON [ON\ OFF]
innodb_adaptive_hash_index 否 ON [ON\ OFF]
innodb_sync_spin_loops 否 30 [0-4294967295]
net_write_timeout 否 60 [1-18446744073709551615]
flush_time 否 0 [0-31536000]
lower_case_table_names 是 1 [0\ 1]
sha256_password_proxy_users 否 OFF [ON\ OFF]
character_set_filesystem 否 binary [utf8\ latin1\ gbk\ binary]
innodb_flush_sync 否 ON [ON\ OFF]
tls_version 是 TLSv1,TLSv1.1,TLSv1.2 [TLSv1,TLSv1.1,TLSv1.2\ TLSv1,TLSv1.1]
key_cache_division_limit 否 100 [1-100]
delayed_insert_timeout 否 300 [1-3600]
preload_buffer_size 否 32768 [1024-1073741824]
innodb_read_ahead_threshold 否 56 [0-1024]
loose_optimizer_switch 否 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 否 1 [0\ 1\ 2] 并发插入功能设置
block_encryption_mode 否 "aes-128-ecb" ["aes-128-ecb"\ "aes-192-ecb"\ "aes-256-ecb"\ "aes-128-cbc"\ "aes-192-cbc"\ "aes-256-cbc"]
slow_query_log 否 ON [ON\ OFF] 记录慢日志
net_buffer_length 否 16384 [1024-1048576]
query_cache_size 否 3145728 [0-104857600]
delayed_insert_limit 否 100 [1-4294967295]
innodb_large_prefix 否 OFF [ON\ OFF]
innodb_monitor_disable 否 all
innodb_adaptive_flushing_lwm 否 10 [0-70]
innodb_log_checksums 否 ON [ON\ OFF]
delayed_queue_size 否 1000 [1-4294967295]
session_track_gtids 否 OFF [OFF\ OWN_GTID\ ALL_GTIDS]
innodb_thread_sleep_delay 否 10000 [0-1000000]
loose_rds_set_connection_id_enabled 否 ON [ON\ OFF]
innodb_old_blocks_pct 否 37 [5-95]
innodb_ft_sort_pll_degree 是 2 [1-16]
log_slow_admin_statements 否 OFF [ON\ OFF]
innodb_stats_on_metadata 否 OFF [ON\ OFF]
stored_program_cache 否 256 [16-524288]
group_concat_max_len 否 1024 [4-1844674407370954752]
innodb_sort_buffer_size 是 1048576 [65536-67108864]
innodb_page_cleaners 是 1 [1-64]
innodb_spin_wait_delay 否 6 [0-4294967295]
myisam_sort_buffer_size 否 262144 [262144-16777216]
innodb_rollback_segments 否 128 [1-128]
innodb_commit_concurrency 是 0 [0-1000]
innodb_concurrency_tickets 否 5000 [1-4294967295]
table_definition_cache 否 512 [400-524288]
auto_increment_increment 否 1 [1-65535]
binlog_checksum 是 CRC32
max_seeks_for_key 否 18446744073709500000 [1-18446744073709551615]
sync_relay_log 否 1 [0-2147483647]
max_length_for_sort_data 否 1024 [0-838860]
back_log 是 3000 [0-65535]
max_error_count 否 64 [0-65535]
innodb_io_capacity_max 否 40000 [0-18446744073709551615]
innodb_strict_mode 否 OFF [ON\ OFF]
binlog_order_commits 是 OFF
min_examined_row_limit 否 0 [0-4294967295]
innodb_ft_min_token_size 是 3 [0-16]
innodb_stats_auto_recalc 否 ON [ON\ OFF]
max_connect_errors 否 100 [0-4294967295]
session_track_schema 否 ON [ON\ OFF]
join_buffer_size 否 262144 [128-4294967295]
innodb_change_buffering 否 all [none\ inserts\ deletes\ changes\ purges\ all]
optimizer_trace_max_mem_size 否 16384 [0-4294967295]
innodb_autoinc_lock_mode 是 2 [0\ 1\ 2]
innodb_rollback_on_timeout 是 OFF [OFF\ ON]
loose_opt_rds_enable_show_slave_lag 否 ON [ON\ OFF]
max_write_lock_count 否 102400 [1-102400]
master_verify_checksum 是 OFF
innodb_ft_num_word_optimize 否 2000 [0-10000]
max_join_size 否 18446744073709551615 [1-18446744073709551615]
loose_validate_password_length 否 8 [1-12]
log_throttle_queries_not_using_indexes 否 0 [0-4294967295]
innodb_max_purge_lag_delay 否 0 [0-10000000]
loose_optimizer_trace 否 enabled=off,one_line=off .*
loose_thread_handling 是 one-thread-per-connection [one-thread-per-connection\ pool-of-threads]
default_week_format 否 0 [0-7]
innodb_cmp_per_index_enabled 否 OFF [ON\ OFF]
show_compatibility_56 否 OFF [ON\ OFF]
host_cache_size 否 644 [0-65535] host缓存大小
low_priority_updates 否 0 [0\ 1]
auto_increment_offset 否 1 [1-65535] 自增列的自增步长
range_alloc_block_size 否 4096 [4096-18446744073709551615]
ft_min_word_len 是 4 [1-3600]
sort_buffer_size 否 262144 [32768-4294967295] 排序申请的buffer
max_allowed_packet 否 1073741824 [16384-1073741824] 限制Server接受的数据包大小
thread_cache_size 否 256 [0-16384] 执行线程缓存
optimizer_trace_offset 否 -1 [0-4294967295]
character_set_server 是 utf8 [utf8\ latin1\ gbk\ gb18030\ utf8mb4] 数据库级的字符集
innodb_adaptive_flushing 否 ON [ON\ OFF] 自适应刷新脏页开关
log_queries_not_using_indexes 否 OFF [ON\ OFF] 没有使用索引的查询就不会记录到慢日志去
innodb_monitor_enable 否 all
table_open_cache_instances 是 16 [1-64] 控制 table cache instance的个数
innodb_flush_neighbors 否 1 [0\ 1\ 2] 用来控制buffer pool刷脏页时是否把脏页邻近的其他脏页一起刷到磁盘
innodb_buffer_pool_instances 是 1 [1-64] 缓冲池实例的大小
innodb_data_file_purge 否 OFF [ON\ OFF] 是否启用异步清除策略。
innodb_data_file_purge_all_at_shutdown 否 OFF [ON\ OFF] 正常关机时全部清理。
innodb_data_file_purge_immediate 否 OFF [ON\ OFF] 取消数据文件的链接但不清理。
innodb_data_file_purge_interval 否 100 [1-1073741824] 清理时间间隔。单位：ms。
innodb_data_file_purge_max_size 否 512 [1-1073741824] 每次清理单个文件大小的最大值。单位：MB。
hotspot 否 OFF [ON\ OFF] 热点更新的开关
hotspot_lock_type 否 OFF [ON\ OFF] 热点更新锁类型

results matching ""

    No results matching ""