Storage layer parameters

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.

results matching ""

    No results matching ""