MySQL5.6 Initial Configuration Optimization

Original article: What to tune in MySQL 5.6 after installation
WithImprovement on a large number of default optionsMySQL 5.6 has fewer options to be optimized than earlier versions. In this article, I will describe the configuration items to be optimized.

InnoDB settings
Innodb_buffer_pool_size -- default value: 128 M. this is the most important optimization option because it specifies how much memory InnoDB uses to load data and indexes (data + indexes ). for dedicated MySQL servers, we recommend that you specify the range of 50-80% of the physical memory. for example, for machines with 64 GB physical memory, the cache pool should be set to around 50 GB.
Innodb_log_file_size -- default value: 48 M. A system with a high write throughput needs to add this value to allow the background checkpoint activity to smoothly write data within a longer period of time to improve performance. setting this value below 4 GB is safe. past practices show that the disadvantage of log files is that they increase the repair time required for crash, but this has been significantly improved in 5.5 and 5.6.Innodb_flush_method-- The default value is fdatasync. if you use a hardware RAID disk controller, you may need to set it to O_DIRECT. this prevents the "double buffering" effect when reading the InnoDB buffer pool. Otherwise, two copies (copy) will be formed between the file system cache and the InnoDB cache ).
MySQL user manual and Bug #54306 describes this in detail.Innodb_flush_neighbors-- The default value is 1. it should be set to 0 (disabled) for SSD storage because sequential IO does not have any performance benefits. this setting should also be disabled on some hardware that uses RAID, because logically continuous blocks on physical disks cannot be ensured to be continuous.Innodb_io_capacity AndInnodb_io_capacity_max-- These settings will affect how many operations InnoDB performs in the background per second.Previous ArticleI described that most write IO (except for InnoDB logs) operations are performed in the background. if you have a deep understanding of hardware performance (for example, how many I/O operations can be performed per second), you can use these features rather than idle it. there is a good analogy: If a ticket is not sold for a certain flight, it may be a good strategy for some people on the flight to take the flight later, in case of bad weather. that is to say, the background operations will be processed by the way to reduce the competition for possible real-time operations later.
Innodb_lru_scan_depth-default value: 1024. This is a new option introduced in mysql 5.6. Mark CallaghanSome configuration suggestions are provided.. Simply put, if the innodb_io_capacity value is increased, innodb_lru_scan_depth should be added at the same time.Replication)
As mentioned in previous articles, I suggest that most users aim for stability. In this case, you also need to enable:Sync_binlog = 1, sync_relay_log = 1,Relay-log-info-repository = TABLEAndMaster-info-repository = TABLE.Expire-logs-days-- The old logs are retained by default. We recommend that you set them to 1-10 days. It is not helpful to save the logs for a longer time, because the recovery from the backup will be much faster.Server-id-- A unique server-id must be set for all servers in a master-slave replication system (replication topology.Binlog_format = ROW-- Change to row-based replication. Another article I recently wroteRow-based ReplicationThis section describes why I really like it, because it can improve performance by reducing resource locks. In addition, you need to enable two additional settings:Transaction-isolation = READ-COMMITTEDAndInnodb_autoinc_lock_mode = 2.Other configurations (Misc)
Timezone = GMT: set the time zone to GMT. more and more system administrators are advised to set all servers to GMT ). I personally like this very much, because almost all business is global now. setting your local time zone seems a bit arbitrary.Character-set-server = utf8mb4AndCollation-server = utf8mb4_general_ciAs described in the previous article, UTF-8 encoding is a better default option for new applications. You can also setSkip-character-set-client-handshakeTo ignore other character sets (character-set) that the application wants to set ).SQL-mode-- By default, MySQL is very tolerant of non-standard data and silently truncates data.In a previous articleI mentioned that the new application should be set to STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO,
Skip-name-resolve -- disable Reverse Domain name resolution. DNS resolution may be slow/unstable in some systems. Therefore, if you do not need to authorize Based on the host name, we recommend that you avoid this resolution.Max_connect_errors--Todd Farmer wrote:: "[This function] provides no practical protection for brute-force access attacks ". in fact, when skip-name-resolve is set, max_connect_errors does not even work (as described in the previous section ). firewall is a more suitable solution. Generally, I block port 3306. Whether it is a public network or an intranet port, only a specific application can access and connect to MySQL.
Max_connect_errors = 100000, so that I can avoid any "Double configuration" to ensure that it will not get in the way.
Max-connections -- the default value is 151. I have seen many users set it to a large value, mostly in the range of 300 ~ 1000. This value is usually set to be larger, but I am a little nervous that 16-core machines only have around 2x ~ 10 x connection execution capability.
Another solution starting from 5.5 (there are some differences between the MySQL Community edition and the Enterprise Edition) is to useThread Pool plug-in.

Conclusion)
Durable ).

The configuration may be as follows:

# InnoDB settingsinnodb_buffer_pool_size=50Ginnodb_log_file_size=2Ginnodb_flush_method=O_DIRECTinnodb_io_capacity=2000innodb_io_capacity_max=6000innodb_lru_scan_depth=2000# Binary log/replicationlog-binsync_binlog=1sync_relay_log=1relay-log-info-repository=TABLEmaster-info-repository=TABLEexpire_logs_days=10binlog_format=ROWtransaction-isolation=READ-COMMITTEDinnodb_autoinc_lock_mode = 2# Othertimezone=GMTcharacter-set-server=utf8collation-server=utf8_general_cisql-mode="STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY"skip-name_resolvemax-connect-errors=100000max-connections=500# Unique to this machineserver-id=123

I hope this article will clarify the main problems. If you have other suggestions, please contact the original author.