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 | Restart required | 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. |
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 | [1-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 | 8 | [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. |
loose_innodb_data_file_purge_interval | No | 100 | [0-10000] | The interval at which files are purged. Unit: milliseconds. |
loose_innodb_data_file_purge_max_size | No | 128 | [16-1073741824] | The maximum size of a single file that can be purged. Unit: MB. |