MySQL5.6 basic configuration detailed explanation

This article mainly introduces the basic optimization configuration of MySQL5.6, breaks down the configuration items that need to be optimized in MySQL5.6 in detail, and finally gives an optimization case. Friends who need it can refer to it

With the With a large number of improvements to the default options, MySQL 5.6 requires significantly fewer tuning options than previous versions. In this article I will describe the configuration items that need to be optimized.

InnoDB Settings

1.innodb_buffer_pool_size - The default value is 128M. 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, it is recommended to specify the range of 50-80% of the physical memory. For example, for a machine with 64GB of physical memory, the cache pool should be set to about 50GB.
If this value is set There may be greater risks, such as not enough free memory left for the operating system and certain MySQL subsystems (subsystems) that rely on the file system cache, including binary logs (binary logs), InnoDB transaction logs ( transaction logs), etc.

2.innodb_log_file_size - The default value is 48M. Systems with high write throughput need to increase this value to allow background Checkpoint activity improves performance by smoothing writes over a longer period of time. It is safe to set this value below 4G. Past practice has shown that the disadvantage of large log files is increased Repair time required on crash, but this has been significantly improved in 5.5 and 5.6.

3.innodb_flush_method - Default is fdatasync. If using hardware RAID disk Controller , may need to be set to O_DIRECT. This prevents the "double buffering" effect when reading from the InnoDB buffer pool, otherwise 2 copies will be formed between the file system cache and the InnoDB cache.
If a hardware RAID controller is not used, or when using SAN storage, O_DIRECT may cause performance degradation. The MySQL user manual and Bug #54306 explain this in detail.

4.innodb_flush_neighbors - The default value is 1. It should be set to 0 (disabled) on SSD storage, because there is no performance gain from using sequential IO. This setting should also be disabled on some hardware using RAID, because the logical contiguous Blocks are not guaranteed to be contiguous on the physical disk.

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 (such as how many IO operations can be performed per second), it is very advisable to use these functions instead of leaving them idle.

There is a good analogy example: If a certain flight Not a single ticket has been sold - so it may be a good strategy to let some people on later flights take this flight in case there is bad weather later. That is, if there is an opportunity, the background operation will be handled by the way. To reduce competition with possible real-time operations later.

There is a very simple calculation: If each disk can read and write 200 times per second (IOPS), then a RAID10 disk array with 10 disks IOPS theoretically = (10/2) * 200 = 1000. I say it is "very simple" because RAID controllers are usually able to provide additional merging and effectively increase IOPS capabilities. For SSD disks, IOPS can easily reach several Thousands.

There may be some risks in setting these two values ​​​​too large. You definitely don’t want background operations to hinder the performance of foreground task IO operations. Past experience shows that setting these two values ​​​​is If it is too high, the internal lock held by InnoDB will cause performance degradation (according to the information I have learned, 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 provided some configuration suggestions. Simply put, if you increase the innodb_io_capacity value, you should also increase innodb_lru_scan_depth.

Replication

If the server wants to support master-slave replication, or point-in-time recovery, in this case, we need:

1.log -bin - Enable binary logging. Binary logging is not crash safe by default, but as I said in my previous article, I recommend that most users should aim for stability. In this case 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 forever. I recommend setting it to 1-10 days. Saving it for longer is not of much use because restoring from backup will be much faster.

3.server-id - All servers in a master-slave replication system (replication topology) must be set with a unique server-id.

4.binlog_format=ROW - Modified to row-based replication. I recently wrote another row-based replication, which describes why I really like it, because it can Improves performance by reducing resource locking. Two additional settings also need to be enabled: transaction-isolation=READ-COMMITTED and innodb_autoinc_lock_mode = 2.

Other Configurations (Misc)

1. timezone=GMT Set the time zone to Greenwich Time. More and more system administrators recommend setting all servers to Greenwich Time (GMT). I personally like this very much, because almost all businesses are now global ized. Setting it to your local time zone seems a bit arbitrary.

2.character-set-server=utf8mb4 and collation-server=utf8mb4_general_ci As mentioned in the previous article, utf8 encoding is a better default option for new applications. You can also set skip-character-set-client-handshake to ignore other character-set(character-set) that the application wants to set.

3.sql-mode ——MySQL is very tolerant to non-standard data by default and will truncate the data silently. In one of my previous articles, I mentioned that new applications The program is best set to:

STRICT_TRANS_TABLES,ERROR_FOR_pISION_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 - disables reverse domain name resolution. DNS resolution can be a bit slow/unstable on some systems, so if Hostname-based authorization is not required, and I recommend avoiding this resolution.

5.max_connect_errors - Todd Farmer wrote: "[This feature] provides brute force access that is moot Attack Protection". In fact when skip-name-resolve is set, max_connect_errors doesn't even work (see the previous paragraph).

A firewall is a more suitable solution, usually I block port 3306, regardless of Public or intranet ports, only specific applications can access and connect to MySQL.
I usually set max_connect_errors=100000, so that I can avoid any "double configuration" and ensure that it doesn't get in the way.

6.max-connections ——The default value is 151. I have seen many users set it to a larger value, mostly between 300 ~ 1000.
Usually this is inevitable The value will be set larger, but what makes me a little nervous is that the 16-core machine only has about 2x~10x connection execution capacity in the case of IO blocking.
You may hope that many open connections are Idle and dormant. But if they are all active, a large number of new threads (thread-thrash) may be created.
If conditions permit, the application can be configured to optimize the database connection pool (connection-pools) to solve the problem This problem is not about opening and maintaining a large number of connections;
Of course, those applications that do not use a connection pool (non-pooled), quickly open, and then close the connection as quickly as possible after performing the task are also feasible.
From 5.5 Another solution to start with (with some differences between MySQL Community Edition and Enterprise Edition) is to use the thread pool plugin.

Conclusion

Assumptions The configuration of the MySQL server is:
1.64GB physical memory
2. Hardware RAID controller (assuming IO can reach 2000 IOPS per second)
3. Master-slave replication (Replication) is required
4. New Applications (eg. non-legacy systems)
5. Firewall protection
6. No authorization based on domain names (hostnames, host names) is required
7. Global applications do not want to be fixed in a certain time zone .
8. If 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-errors=100000
max-connections=500
# Unique to this machine
server-id=123

The above is the detailed content of MySQL5.6 basic configuration detailed explanation. For more information, please follow other related articles on the PHP Chinese website!