存储层变量
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] | 热点更新锁类型 |