不要轻易放弃。学习成长的路上,我们长路漫漫,只因学无止境。学无止境

mysql5.7 8核16G 配置参数优化

若疆~赤云 2019-03-30 00:32:50.0 530人围观
参数名参数默认值运行参数值是否重启可修改参数值参数描述
automatic_sp_privilegesONON[ON|OFF]
auto_increment_increment11[1-65535]
auto_increment_offset11[1-65535]
avoid_temporal_upgradeOFFOFF[ON|OFF]
back_log30003000[0-65535]
binlog_cache_size20971522048KB[4096-16777216]
binlog_checksumCRC32CRC32[CRC32|NONE]
binlog_order_commitsONON[ON|OFF]
binlog_rows_query_log_eventsOFFOFF[ON|OFF]
binlog_row_imagefullfull[full|minimal]
binlog_stmt_cache_size3276832768[4096-16777216]
block_encryption_mode"aes-128-ecb""aes-128-ecb"["aes-128-ecb"|"aes-...
bulk_insert_buffer_size41943044194304[0-4294967295]
character_set_filesystembinaryutf8[utf8|latin1|gbk|bin...
character_set_serverutf8utf8mb4[utf8|latin1|gbk|utf...
concurrent_insert11[0|1|2]
connect_timeout1010[1-3600]
default_storage_engineInnoDBInnoDB[InnoDB|innodb]
default_time_zoneSYSTEMSYSTEM[SYSTEM|-12:00|-11:0...
default_week_format00[0-7]
delayed_insert_limit100100[1-4294967295]
delayed_insert_timeout300300[1-3600]
delayed_queue_size10001000[1-4294967295]
delay_key_writeONON[ON|OFF|ALL]
disconnect_on_expired_passwordONON[ON|OFF]
div_precision_increment44[0-30]
end_markers_in_jsonOFFOFF[ON|OFF]
eq_range_index_dive_limit1010[0-4294967295]
event_schedulerOFFOFFON
explicit_defaults_for_timestampOFFOFF[ON|OFF]
flush_time00[0-31536000]
ft_max_word_len8484[10-4294967295]
ft_min_word_len44[1-3600]
ft_query_expansion_limit2020[0-1000]
group_concat_max_len10241024[4-18446744073709547...
host_cache_size644644[0-65535]
innodb_adaptive_flushingONON[ON|OFF]
innodb_adaptive_flushing_lwm1010[0-70]
innodb_adaptive_hash_indexONON[ON|OFF]
innodb_adaptive_max_sleep_delay150000150000[1-1000000]
innodb_autoextend_increment6464[1-1000]
innodb_autoinc_lock_mode11[0|1|2]
innodb_buffer_pool_dump_at_shutdownONON[ON|OFF]
innodb_buffer_pool_dump_pct2525[1-100]
innodb_buffer_pool_instances11[1-64]
innodb_buffer_pool_load_at_startupONON[ON|OFF]
innodb_change_bufferingallall[none|inserts|delete...
innodb_change_buffer_max_size2525[0-50]
innodb_checksum_algorithmcrc32crc32[innodb|crc32|none|s...
innodb_cmp_per_index_enabledOFFOFF[ON|OFF]
innodb_commit_concurrency00[0-1000]
innodb_compression_failure_threshold_pct55[0-100]
innodb_compression_level66[0-9]
innodb_compression_pad_pct_max5050[0-70]
innodb_concurrency_tickets50005000[1-4294967295]
innodb_deadlock_detectONON[ON|OFF]
innodb_disable_sort_file_cacheONON[ON|OFF]
innodb_flush_methodO_DIRECTO_DIRECT[fsync|O_DSYNC|littl...
innodb_flush_neighbors11[0|1|2]
innodb_flush_syncONON[ON|OFF]
innodb_ft_cache_size80000008000000[1600000-80000000]
innodb_ft_enable_diag_printOFFOFF[ON|OFF]
innodb_ft_enable_stopwordONON[ON|OFF]
innodb_ft_max_token_size8484[10-84]
innodb_ft_min_token_size33[0-16]
innodb_ft_num_word_optimize20002000[0-10000]
innodb_ft_result_cache_limit20000000002000000000[1000000-4294967295]
innodb_ft_sort_pll_degree22[1-16]
innodb_ft_total_cache_size640000000640000000[32000000-1600000000...
innodb_io_capacity20002000[100-4294967295]
innodb_io_capacity_max40004000[100-4294967295]
innodb_large_prefixOFFOFF[ON|OFF]
innodb_lock_wait_timeout5050[1-1073741824]
innodb_log_checksumsONON[ON|OFF]
innodb_log_compressed_pagesOFFOFF[ON|OFF]
innodb_lru_scan_depth10241024[100-4294967295]
innodb_max_dirty_pages_pct7575[50-90]
innodb_max_dirty_pages_pct_lwm00[0-75]
innodb_max_purge_lag00[0-4294967295]
innodb_max_purge_lag_delay00[0-10000000]
innodb_max_undo_log_size10737418241073741824[10485760-1844674407...
innodb_monitor_disable\s\sall
innodb_monitor_enable\s\sall
innodb_old_blocks_pct3737[5-95]
innodb_old_blocks_time10001000[0-1024]
innodb_online_alter_log_max_size134217728134217728[134217728-214748364...
innodb_open_files30003000[1-8192]
innodb_optimize_fulltext_onlyOFFOFF[ON|OFF]
innodb_page_cleaners11[1-64]
innodb_print_all_deadlocksOFFOFF[OFF|ON]
innodb_purge_batch_size300300[1-5000]
innodb_purge_rseg_truncate_frequency128128[1-128]
innodb_purge_threads11[1-32]
innodb_random_read_aheadOFFOFF[ON|OFF]
innodb_read_ahead_threshold5656[0-64]
innodb_read_io_threads44[1-64]
innodb_rollback_on_timeoutOFFOFF[OFF|ON]
innodb_rollback_segments128128[1-128]
innodb_sort_buffer_size10485761048576[65536-67108864]
innodb_spin_wait_delay3030[0-4294967295]
innodb_stats_auto_recalcONON[ON|OFF]
innodb_stats_methodnulls_equalnulls_equal[nulls_equal|nulls_u...
innodb_stats_on_metadataOFFOFF[ON|OFF]
innodb_stats_persistentONON[ON|OFF]
innodb_stats_persistent_sample_pages2020[0-4294967295]
innodb_stats_sample_pages88[1-4294967296]
innodb_stats_transient_sample_pages88[1-4294967295]
innodb_status_outputOFFOFF[ON|OFF]
innodb_status_output_locksOFFOFF[ON|OFF]
innodb_strict_modeOFFOFF[ON|OFF]
innodb_sync_array_size11[1-1024]
innodb_sync_spin_loops100100[0-4294967295]
innodb_table_locksONON[ON|OFF]
innodb_thread_concurrency00[0-128]
innodb_thread_sleep_delay1000010000[1-3600000]
innodb_write_io_threads44[1-64]
interactive_timeout72007200[10-86400]
join_buffer_size262144432KB[128-4294967295]
key_cache_age_threshold300300[100-4294967295]
key_cache_block_size10241024[512-16384]
key_cache_division_limit100100[1-100]
lc_time_namesen_USen_US[ja_JP|pt_BR|en_US]
lock_wait_timeout3153600031536000[1-1073741824]
log_queries_not_using_indexesOFFOFF[ON|OFF]
log_throttle_queries_not_using_indexes00[0-4294967295]
long_query_time11[0.03-31536000]
loose_optimizer_traceenabled=off,one_line=offenabled=off,one_line=off.*
loose_optimizer_trace_featuresgreedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=ongreedy_search=on,range_opt....*
loose_opt_rds_enable_show_slave_lagONONbool
loose_opt_rds_last_error_gtidONONbool
loose_rds_check_core_file_enabledONONbool
loose_rds_kill_connections2020[0, 1844674407370955...
loose_thread_handling"one-thread-per-connection""one-thread-per-connection...one-thread-per-conne...
loose_thread_pool_oversubscribe1010

loose_thread_pool_stall_limit3030

loose_validate_password_length88[1-12]
low_priority_updates00[0|1]
master_verify_checksumOFFOFF[ON|OFF]
max_allowed_packet10737418241024M[16384-1073741824]
max_binlog_stmt_cache_size1844674407370954752018446744073709547520[4096-18446744073709...
max_connect_errors100100[1-4294967295]
max_error_count6464[0-65535]
max_heap_table_size6710886467108864[16384-1844674407370...
max_join_size1844674407370955161518446744073709551615[1-18446744073709551...
max_length_for_sort_data10241024[0-838860]
max_points_in_geometry6553665536[3-1048576]
max_prepared_stmt_count1638216382[0-1048576]
max_seeks_for_key1844674407370950000018446744073709500000[1-18446744073709551...
max_sort_length10241024[4-8388608]
max_sp_recursion_depth00[0-255]
max_write_lock_count102400102400[1-102400]
metadata_locks_cache_size10241024[1-1048576]
min_examined_row_limit00[0-4294967295]
myisam_sort_buffer_size262144262144[262144-16777216]
mysql_native_password_proxy_usersOFFOFF[ON|OFF]
net_buffer_length1638416384[1024-1048576]
net_read_timeout3030[1-31536000]
net_retry_count1010[1-4294967295]
net_write_timeout6060[1-31536000]
ngram_token_size22[0-20]
old_passwords00[0|2]
open_files_limit6553565535[4000-65535]
optimizer_prune_level11[0|1]
optimizer_search_depth6262[0-62]
optimizer_trace_limit11[0-4294967295]
optimizer_trace_max_mem_size1638416384[0-4294967295]
optimizer_trace_offset-1-1[0-4294967295]
performance_schemaOFFOFF[ON|OFF]
preload_buffer_size3276832768[1024-1073741824]
query_alloc_block_size81928192[1024-16384]
query_cache_limit10485761048576[1-1048576]
query_cache_min_res_unit10241024[512-184467440737095...
query_cache_size31457283145728[0-104857600]
query_cache_type00[0|1|2]
query_cache_wlock_invalidateOFFOFF[ON|OFF]
query_prealloc_size81928192[8192-1048576]
range_alloc_block_size40964096[4096-18446744073709...
range_optimizer_max_mem_size83886088388608[0-18446744073709551...
rds_reserved_connections512512[0-512]
session_track_gtidsOFFOFF[OFF|OWN_GTID|ALL_GT...
session_track_schemaONON[ON|OFF]
session_track_state_changeOFFOFF[ON|OFF]
sha256_password_proxy_usersOFFOFF[ON|OFF]
show_compatibility_56OFFOFF[ON|OFF]
show_old_temporalsOFFOFF[ON|OFF]
slave_net_timeout6060[15-300]
slave_parallel_typeLOGICAL_CLOCKLOGICAL_CLOCKDATABASE,LOGICAL_CLO...
slow_launch_time22[1-1024]
slow_query_logONON[ON|OFF]
sort_buffer_size262144848KB[32768-4294967295]
sql_mode\s\s(支持空格及|REAL_AS_FLOAT...
stored_program_cache256256[16-524288]
table_definition_cache512512[400-80480]
table_open_cache20002000[1-524288]
table_open_cache_instances11[1-64]
thread_cache_size100100[0-16384]
thread_stack262144262144[131072-1073741824]
tls_versionTLSv1,TLSv1.1,TLSv1.2TLSv1,TLSv1.1,TLSv1.2[TLSv1,TLSv1.1,TLSv1...
tmp_table_size20971522097152[262144-67108864]
transaction_alloc_block_size81928192[1024-131072]
transaction_isolationREAD-COMMITTEDREAD-COMMITTED[READ-UNCOMMITTED|RE...
transaction_prealloc_size40964096[1024-131072]
updatable_views_with_limitYESYES[YES|NO]
wait_timeout8640086400[1-31536000]
注:如有侵权行为,请联系我们,我们会及时删除。联系方式:QQ2421979663

上一篇:高并发优化之一:返回数据要少

下一篇:Java webp图片处理