可以通过 此链接 进行下载mysql的配置文件。
新增加的参数有：
- metadata_locks_hash_instances = 64 — 提升大并发下的元数据锁性能，注意该参数仅在MySQL 5.6版本下有效，5.7版本已经使用无锁结构进行优化
- innodb_open_files = 4096 — 增大InnoDB层缓存文件句柄的数量
- table_open_cache = 4096 — 增大缓存表的数量
- table_definition_cache = 4096 — 调大缓存frm表的数量，很多小伙伴遇到这个问题，导致show table status运行时等待非常长的时间
- table_open_cache_instances = 128 — 提升大并发下的性能
- thread_cache_size = 64 — 增大线程缓存，提升短连接的性能。通常来说都用连接池技术进行长连接操作，但是最近有一个项目用了短连接，遇到了坑。
- innodb_online_alter_log_max_size=1G — 增大内存，防止大表在DML比较多的场景下Online DDL操作失败
移除的参数有：
- relay_log_recovery = 1 — 若开启GTID功能，crash safe功能无需开启此参数。具体可见 官方文档说明
最后，对上篇文章 拿走不谢，Flashback for MySQL 5.7 做一下说明，在编辑时漏了下面一段话，可能大家产生了误解：
Flashback最早由淘宝的彭立勋同学开发，后集成在InnoSQL中，并由网易的同学持续开发与维护。
因此官方的mysqlbinlog是不包含flashback功能，下载文章中指定mysqlbinlog才有。
补充，mysql的配置：
|
# author: jiangchengyao@gmail.com |
|
|
[mysql] |
|
|
prompt = [\\u@\\h][\\d]>\\_ |
|
|
[mysqld] |
|
|
# basic settings # |
|
|
user = mysql |
|
|
sql_mode = “STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER” |
|
|
autocommit = 1 |
|
|
character_set_server=utf8mb4 |
|
|
transaction_isolation = READ-COMMITTED |
|
|
explicit_defaults_for_timestamp = 1 |
|
|
max_allowed_packet = 16777216 |
|
|
event_scheduler = 1 |
|
|
# connection # |
|
|
interactive_timeout = 1800 |
|
|
wait_timeout = 1800 |
|
|
lock_wait_timeout = 1800 |
|
|
skip_name_resolve = 1 |
|
|
max_connections = 512 |
|
|
max_connect_errors = 1000000 |
|
|
# table cache performance settings |
|
|
table_open_cache = 4096 |
|
|
table_definition_cache = 4096 |
|
|
table_open_cache_instances = 128 |
|
|
# 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 |
|
|
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 = 90 |
|
|
long_query_time = 2 |
|
|
min_examined_row_limit = 100 |
|
|
binlog-rows-query-log-events = 1 |
|
|
log-bin-trust-function-creators = 1 |
|
|
expire-logs-days = 90 |
|
|
log-slave-updates = 1 |
|
|
# 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 = 17179869184 |
|
|
innodb_log_files_in_group = 2 |
|
|
innodb_log_buffer_size = 16777216 |
|
|
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 |
|
|
# 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_recovery = 1 |
|
|
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 |
|
|
[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 |
|
|
log_timestamps=system |
|
|
show_compatibility_56=on |
|
如需转载请注明： 转载自26点的博客
本文链接地址: mysql的最优化配置
转载请注明：26点的博客 » mysql的最优化配置
