All Products
Search
Document Center

Storage layer parameters

Last Updated: Apr 06, 2022

PolarDB-X uses an architecture in which storage is decoupled from computing. The parameter names and definitions of the storage layer in which data nodes are deployed are consistent to those in MySQL. The following table describes the common parameters of the storage layer. You can configure the parameters in the PolarDB-X console. For more information about other parameters, see MySQL parameters.

Parameter

Restarted

Default value

Valid value

Description

loose_enable_gts

Yes

1

[0|1]

Specifies whether to enable the Global Timestamp Service (GTS).

loose_gts_lease

Yes

5000

[0-10000]

The lease period of the GTS.

loose_performance-schema-instrument

No

wait/lock/metadata/sql/mdl=ON

.*

Specifies whether to obtain the information about metadata locks (MDLs) used when the PolarDB-X instance is running.

performance_schema

No

ON

[ON,OFF]

Specifies whether to monitor the performance of the ApsaraDB RDS for MySQL server.

binlog_rows_query_key_content

No

ON

[ON,OFF]

Specifies whether to display SQL statements in binary logs.

loose_polarx_max_allowed_packet

No

16777216

(0-2147483648)

Specifies the size of packets received and sent by the server over proprietary protocols.

innodb_buffer_pool_load_at_startup

Yes

ON

[ON|OFF]

Specifies whether to reload the buffer pool after the server is started.

bulk_insert_buffer_size

No

4194304

[0-4294967295]

The number of records written to the temporary cache when data that is inserted in batches is cached.

show_old_temporals

No

OFF

[ON|OFF]

ft_query_expansion_limit

Yes

20

[0-1000]

The maximum number of characters that are matched when full-text searches are performed on MyISAM tables by using WITH QUERY EXPANSION.

innodb_old_blocks_time

No

1000

[0-1024]

The time window after the mid position of a page is read. During the time window, the page can be accessed without being moved to the most recently used end of the least recently used (LRU) list.

innodb_stats_sample_pages

No

8

[1-4294967296]

The interval at which data is collected.

thread_stack

Yes

262144

[131072-18446744073709551615]

The size of memory allocated by PolarDB-X when each connection thread is created.

lc_time_names

No

en_US

[ja_JP|pt_BR|en_US]

The language used to display the names of days, months, and abbreviations.

innodb_thread_concurrency

No

0

[0-1000]

The number of concurrent threads.

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]

The time zone.

old_passwords

No

0

[0|2]

optimizer_search_depth

No

62

[0-62]

The recursion depth of the optimizer when multiple tables are correlated.

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|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 high-speed cache for table file handles.

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]

The minimum size of a block that stores query results in the query cache.

innodb_print_all_deadlocks

No

OFF

[OFF|ON]

loose_thread_pool_size

No

32

[1-1024]

The number of groups in the thread pool. Default value: 32. Threads in the thread pool are evenly divided into groups and managed by group.

binlog_stmt_cache_size

No

32768

[4096-16777216]

transaction_isolation

No

READ-COMMITTED

[READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE]

The transaction isolation level.

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]

Specifies whether to enable concurrent inserts.

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]

Specifies whether to record slow query logs.

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]

The size of the host cache.

low_priority_updates

No

0

[0|1]

auto_increment_offset

No

1

[1-65535]

The auto-increment step size of the auto-increment column.

range_alloc_block_size

No

4096

[4096-18446744073709551615]

ft_min_word_len

Yes

4

[1-3600]

sort_buffer_size

No

262144

[32768-4294967295]

The size of the sort buffer used to sort data.

max_allowed_packet

No

1073741824

[16384-1073741824]

The maximum size of the packet that can be received by the server.

thread_cache_size

No

256

[0-16384]

The size of the thread cache.

optimizer_trace_offset

No

-1

[0-4294967295]

character_set_server

Yes

utf8

[utf8|latin1|gbk|gb18030|utf8mb4]

The character set for databases.

innodb_adaptive_flushing

No

ON

[ON|OFF]

Specifies whether to dynamically adjust the speed at which dirty pages are flushed based on the workload.

log_queries_not_using_indexes

No

OFF

[ON|OFF]

Specifies whether to record queries that do not use indexes in slow query logs.

innodb_monitor_enable

No

all

table_open_cache_instances

Yes

16

[1-64]

The number of table cache instances.

innodb_flush_neighbors

No

1

[0|1|2]

Specifies whether to flush neighbor dirty pages of a dirty page to the disk when the dirty page in the buffer pool is flushed. Neighbor dirty pages are in the same extent as the dirty page.

innodb_buffer_pool_instances

Yes

1

[1-64]

The size of each buffer pool instance.

innodb_data_file_purge

No

OFF

[ON|OFF]

Specifies whether to enable the Purge Large File Asynchronously feature.

innodb_data_file_purge_all_at_shutdown

No

OFF

[ON|OFF]

Specifies whether to delete all files when the host on which your PolarDB-X instance is deployed is shut down.

innodb_data_file_purge_immediate

No

OFF

[ON|OFF]

Specifies whether to retain data files and revoke only the links of the data files.

innodb_data_file_purge_interval

No

100

[1-1073741824]

Specifies the interval at which InnoDB deletes files. Unit: milliseconds.

innodb_data_file_purge_max_size

No

512

[1-1073741824]

Specifies the maximum size of a single file that can be deleted. Unit: MB.

hotspot

No

OFF

[ON|OFF]

Specifies whether to enable the hot spot update feature.

hotspot_lock_type

No

OFF

[ON|OFF]

The type of the lock for the hot spot update.