PolarDB-X是存储计算分离架构,存储层(DN)的变量名和含义与MySQL对齐,这里主要罗列了常见的DN变量(控制台可配置),其他变量可以参考MySQL变量。
名称 | 是否重启 | 默认值 | 修改范围 | 备注 |
loose_enable_gts | 是 | 1 | [0|1] | 全局时间戳开关。 |
loose_gts_lease | 是 | 5000 | [0-10000] | 全局时间戳的租约时间。 |
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] | 每个连接线程被创建时,PolarDB-X给它分配的内存大小。 |
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 | 否 | ON | [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 | [1-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 | 是 | 8 | [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] | 热点更新锁类型。 |
loose_innodb_data_file_purge_interval | 否 | 100 | [0-10000] | 清除文件间隔,单位:毫秒。 |
loose_innodb_data_file_purge_max_size | 否 | 128 | [16-1073741824] | 每个清除文件的最大数量,单位:MB。 |