MySQL5.6 basic optimization configuration _ MySQL

MySQL5.6 basic optimization configuration with the improvement of a large number of default options, MySQL 5.6 has fewer options to be optimized than previous versions. in this article, I will describe the configuration items to be optimized.

InnoDB settings

1. 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.

2. 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.

3. 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 use SAN storage, O_DIRECT may cause performance degradation. MySQL User Manual and Bug #54306 describe this in detail.

4. 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.

5. innodb_io_capacity and innodb_io_capacity_max-These settings will affect how many operations InnoDB performs in the background per second. 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 Callaghan provides some configuration suggestions. Simply put, if innodb_io_capacity 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:

1. log-bin-Enable binary log. by default, binary logs are not accident-safe (not crash safe), but as I mentioned in earlier articles, we recommend that most users target stability. in this case, you also need to enable: sync_binlog = 1, sync_relay_log = 1, relay-log-info-repository = TABLE and master-info-repository = TABLE.

2. expire-logs-days-By default, old logs will be retained all the time. we recommend that you set the value to 1-10 days. it is not helpful to save the log for a longer time because the recovery from the backup will be much faster.

3. server-id-A unique server-id must be set for all servers in a master-slave replication system (replication topology.

4. binlog_format = ROW-Modify to row-based replication. another article I recently wrote about row-based replication describes why I really like it because it can improve performance by reducing resource locks. in addition, you must enable two additional settings: transaction-isolation = READ-COMMITTED and innodb_autoinc_lock_mode = 2.

Other configurations (Misc)

1. 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.

2. character-set-server = utf8mb4 and collation-server = utf8mb4_general_ciAs described in the previous article, UTF-8 encoding is a better default option for new applications. you can also set skip-character-set-client-handshake to ignore other character sets (character-set) that the application wants to set ).

3. SQL-mode-By default, MySQL is very tolerant of non-standard data and silently truncates data. in my previous article, I 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,
NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE,
NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY.

4. skip-name-resolve-Disabling reverse domain name resolution. DNS resolution may be slow/unstable on some systems. Therefore, if you do not need to authorize the DNS based on the host name, we recommend that you avoid this resolution.

5. max_connect_errors-Todd Farmer wrote: "[This function] provides protection against brute-force access attacks without practical significance ". 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.
I usually set max_connect_errors = 100000, so that I can avoid any "double configuration" to ensure that it will not get in the way.

6. max-connections-The default value is 151. I have seen that 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 use the thread pool plug-in.

Conclusion)

Assume that the MySQL server is configured as follows:
1.64GB physical memory
2. hardware RAID controller (assuming I/O can reach 2000 IOPS per second)
3. master-slave Replication is required)
4. new applications (eg. non-legacy systems)
5. firewall protection
6. authorization based on the domain name (hostnames, host name) is not required
7. global applications do not want to be fixed in a certain time zone.
8. you want the program to be reliable and stable (durable ).

The configuration may be as follows:

# InnoDB settings
Innodb_buffer_pool_size = 50G
Innodb_log_file_size = 2G
Innodb_flush_method = O_DIRECT
Innodb_io_capacity = 2000
Innodb_io_capacity_max = 6000
Innodb_lru_scan_depth = 2000

# Binary log/replication
Log-bin
Sync_binlog = 1
Sync_relay_log = 1
Relay-log-info-repository = TABLE
Master-info-repository = TABLE
Expire_logs_days = 10
Binlog_format = ROW
Transaction-isolation = READ-COMMITTED
Innodb_autoinc_lock_mode = 2

# Other
Timezone = GMT
Character-set-server = utf8
Collation-server = utf8_general_ci
SQL-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_resolve
Max-connect-errorrs = 100000
Max-connections = 500

# Unique to this machine
Servers-id = 123