MySQL最佳配置模板

Based on：

• 32 CPU core
• 256G Memory
• SSD storage with 20000 IOPS in 16K page size

 

[mysql]prompt = [\\u@\\h][\\d]>\\_[mysqld]# basic settings #user = mysqlsql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"autocommit = 1character_set_server=utf8mb4transaction_isolation = READ-COMMITTEDexplicit_defaults_for_timestamp = 1max_allowed_packet = 16777216event_scheduler = 1# connection #interactive_timeout = 1800wait_timeout = 1800lock_wait_timeout = 1800skip_name_resolve = 1max_connections = 512max_connect_errors = 1000000# table cache performance settingstable_open_cache = 4096table_definition_cache = 4096table_open_cache_instances = 128# session memory settings #read_buffer_size = 16Mread_rnd_buffer_size = 32Msort_buffer_size = 32Mtmp_table_size = 64Mjoin_buffer_size = 128Mthread_cache_size = 64# log settings #log_error = error.logslow_query_log = 1slow_query_log_file = slow.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_throttle_queries_not_using_indexes = 10expire_logs_days = 90long_query_time = 2min_examined_row_limit = 100binlog-rows-query-log-events = 1log-bin-trust-function-creators = 1expire-logs-days = 90log-slave-updates = 1# innodb settings #innodb_page_size = 16384innodb_buffer_pool_size = 160Ginnodb_buffer_pool_instances = 16innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_lru_scan_depth = 4096innodb_lock_wait_timeout = 5innodb_io_capacity = 10000innodb_io_capacity_max = 20000innodb_flush_method = O_DIRECTinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_undo_logs = 128innodb_undo_tablespaces = 3innodb_flush_neighbors = 0innodb_log_file_size = 17179869184innodb_log_files_in_group = 2innodb_log_buffer_size = 16777216innodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_buffer_size = 67108864innodb_write_io_threads = 16innodb_read_io_threads = 16 innodb_file_per_table = 1innodb_stats_persistent_sample_pages = 64innodb_autoinc_lock_mode = 2innodb_online_alter_log_max_size=1Ginnodb_open_files=4096# replication settings #master_info_repository = TABLErelay_log_info_repository = TABLEsync_binlog = 1gtid_mode = onenforce_gtid_consistency = 1log_slave_updatesbinlog_format = ROWbinlog_rows_query_log_events = 1relay_log = relay.logrelay_log_recovery = 1slave_skip_errors = ddl_exist_errorsslave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'# semi sync replication settings #plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"rpl_semi_sync_master_enabled = 1rpl_semi_sync_master_timeout = 3000rpl_semi_sync_slave_enabled = 1# password plugin #validate_password_policy=STRONGvalidate-password=FORCE_PLUS_PERMANENT[mysqld-5.6]# metalock performance settingsmetadata_locks_hash_instances=64[mysqld-5.7]# new innodb settings #loose_innodb_numa_interleave=1innodb_buffer_pool_dump_pct = 40innodb_page_cleaners = 16innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2Ginnodb_purge_rseg_truncate_frequency = 128# new replication settings #slave-parallel-type = LOGICAL_CLOCKslave-parallel-workers = 16slave_preserve_commit_order=1slave_transaction_retries=128# other change settings #binlog_gtid_simple_recovery=1log_timestamps=systemshow_compatibility_56=on