MySQL 5.6&5.7配置文件（姜总提供）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 |
[mysql] default-character-set=utf8mb4 user = root password = 123456 port = 3306 socket = /tmp/mysqld.sock prompt="\u@\h \d>" [mysqld] # basic settings # user = mysql bind-address = 0.0.0.0 socket = /tmp/mysqld.sock character_set_server = utf8mb4 transaction_isolation = READ-COMMITTED explicit_defaults_for_timestamp = 1 max_allowed_packet = 67108864 max_long_data_size = 67108864 event_scheduler = 1 default_password_lifetime = 0 autocommit = 1 server-id = 1 sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" # connection # interactive_timeout = 1800 wait_timeout = 1800 lock_wait_timeout = 1800 skip_name_resolve = 1 max_connections = 1024 max_user_connections = 256 max_connect_errors = 1000000 # table cache performance settings # table_open_cache = 4096 table_definition_cache = 4096 table_open_cache_instances = 64 # session memory settings # read_buffer_size = 16M read_rnd_buffer_size = 32M sort_buffer_size = 32M tmp_table_size = 64M join_buffer_size = 128M thread_cache_size = 64 # log settings # log_error = error.log log-bin = mysql-bin slow_query_log = 1 slow_query_log_file = slow.log log_queries_not_using_indexes = 1 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log_throttle_queries_not_using_indexes = 10 expire_logs_days = 30 long_query_time = 2 min_examined_row_limit = 100 binlog-rows-query-log-events = 1 log-bin-trust-function-creators = 1 log-slave-updates = 1 log_timestamps = system # innodb settings # innodb_page_size = 16384 innodb_buffer_pool_size = 160G innodb_buffer_pool_instances = 16 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_lru_scan_depth = 4096 innodb_lock_wait_timeout = 5 innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_flush_method = O_DIRECT innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_undo_logs = 128 innodb_undo_tablespaces = 3 innodb_flush_neighbors = 0 innodb_log_file_size = 200M innodb_log_files_in_group = 2 innodb_log_buffer_size = 16M innodb_purge_threads = 4 innodb_large_prefix = 1 innodb_thread_concurrency = 64 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_sort_buffer_size = 67108864 innodb_write_io_threads = 16 innodb_read_io_threads = 16 innodb_file_per_table = 1 innodb_stats_persistent_sample_pages = 64 innodb_autoinc_lock_mode = 2 innodb_online_alter_log_max_size = 1G innodb_open_files = 4096 innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 1 # replication settings # master_info_repository = TABLE relay_log_info_repository = TABLE sync_binlog = 1 gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates binlog_format = ROW binlog_rows_query_log_events = 1 relay_log = relay.log relay_log_purge = 1 relay_log_recovery = 1 report-port = 3306 report-host = 10.106.144.11 slave_skip_errors = ddl_exist_errors slave-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 = 1 rpl_semi_sync_master_timeout = 3000 rpl_semi_sync_slave_enabled = 1 # password plugin # validate_password_policy=STRONG validate-password=FORCE_PLUS_PERMANENT # perforamnce_schema settings performance-schema-instrument='memory/%=COUNTED' performance_schema_digests_size = 40000 performance_schema_max_table_instances = 40000 performance_schema_max_sql_text_length = 4096 performance_schema_max_digest_length = 4096 [mysqld-5.6] # metalock performance settings metadata_locks_hash_instances = 64 [mysqld-5.7] # new innodb settings # loose_innodb_numa_interleave = 1 innodb_buffer_pool_dump_pct = 40 innodb_page_cleaners = 16 innodb_undo_log_truncate = 1 innodb_max_undo_log_size = 2G innodb_purge_rseg_truncate_frequency = 128 # new replication settings # slave-parallel-type = LOGICAL_CLOCK slave-parallel-workers = 16 slave_preserve_commit_order = 1 slave_transaction_retries = 128 # other change settings # binlog_gtid_simple_recovery = 1 show_compatibility_56 = on # group replication settings plugin-load = "group_replication.so;validate_password.so;semisync_master.so;semisync_slave.so" transaction-write-set-extraction = XXHASH64 # report_host = 127.0.0.1 # optional for group replication # binlog_checksum = NONE # only for group replication loose_group_replication = FORCE_PLUS_PERMANENT loose_group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose_group_replication_compression_threshold = 100 loose_group_replication_flow_control_mode = 0 loose_group_replication_single_primary_mode = 0 loose_group_replication_enforce_update_everywhere_checks = 1 loose_group_replication_transaction_size_limit = 10485760 loose_group_replication_unreachable_majority_timeout = 120 loose_group_replication_start_on_boot = 0 |
I assume the MySQL Server as followings. You should tune the variables according to your server.
- 32 CPU core
- 256G Memory
- SSD storage with 20000 IOPS in 16K page size
如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。
