MySQL 5.6 Initial Configuration Optimization

Original article: What to tune in MySQL 5.6 after installation
Original Article Date: January 1, September 17, 2013
Translated on: February 1, June 01, 2014
Translated by: Tie

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-- The default value is 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.
  If you set this value to a greater value, there may be risks. For example, some MySQL subsystems (subsystems) that do not have enough free memory to be reserved for the operating system or dependent on the file system cache ), including binary logs and InnoDB transaction logs.
• Innodb_log_file_size-- The default value is 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 ).
  If you do not use a hardware RAID Controller or SAN storage, O_DIRECT may cause performance degradation.MySQL user manualAnd Bug #54306.
• 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.
There is a simple calculation: if each disk can read/write (IOPS) up to 200 times per second, the raid 10 disk array IOPS with 10 disks theoretically = (10/2) * 200 = 1000. I said it is "very simple" because RAID controllers can usually provide additional merging and effectively improve IOPS capabilities. for SSD disks, IOPS can easily reach several thousand.
Setting these two values too large may lead to some risks. You certainly do not want background operations to impede the performance of foreground task I/O operations. past experience has shown that setting these two values too high will lead to performance degradation by the internal locks held by InnoDB (according to what I know, this has been greatly improved in MySQL5.6 ).

• Innodb_lru_scan_depth-The default value is 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)
If the server needs to support master-slave replication or restore by time point, in this case, we need:

• Log-bin -- enable binary logs. By default, binary logs are not accident safe,As mentioned in previous articlesIn this case, you 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 = GMTSet 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 it is best to set the new application as 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-resolve-- Disable Reverse Domain name resolution. DNS resolution may be slow/unstable On 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 protection against meaningless brute-force access attacks." In fact, max_connect_errors does not even work when skip-name-resolve is set (see 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.
I usually set Max_connect_errorrs = 100000In this way, 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 relatively large value, mostly between 300 and ~ Between 1000.

This value is usually set to be larger, but I am a little nervous that 16-core machines only have about 2x ~ 10 x connection execution capability.
You may expect that many open connections are idle and sleep, but if they are all active, a large number of new threads (thread-thrash) may be created ).
If conditions permit, you can configure the connection pool (connection-pools) for the application to solve this problem, rather than opening and maintaining a large number of connections;
Of course, it is also feasible for applications that do not use a connection pool (non-pooled) to quickly open and close the connection as soon as possible after the task is executed.

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)
Assume that the MySQL server is configured as follows:

• 64 GB physical memory
• Hardware RAID Controller (assuming I/O can reach 2000 IOPS per second)
• Master-slave Replication is required)
• New applications (eg. Non-legacy systems)
• Firewall protection
• Authorization based on domain name (hostnames, host name) is not required
• Global applications do not want to be fixed in a certain time zone.
• ProgramDurable).

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.