开发指南—变量—存储层变量-阿里云开发者社区

开发者社区> -技术小能手-> 正文

开发指南—变量—存储层变量

简介: 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]

热点更新锁类型

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云文件存储NAS开发测试环境最佳实践
阿里云文件存储NAS开发测试环境最佳实践
1739 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
4479 0
1280
文章
0
问答
来源圈子
更多
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载