MySQL optimizes the 10 configurations that must be tuned

When we were hired to monitor MySQL performance, we were expected to look at 1 MySQL configuration and suggest 1 improvement. Many people are surprised afterwards when we suggest that they change only a few Settings, even if there are hundreds of configuration items. The purpose of this article is to give you a list of very important configuration items.

We gave this advice on our blog a few years ago, but the MySQL world is changing so fast!
Write before you start...
Even experienced people make mistakes, which can cause a lot of trouble. So before blindly applying these recommendations, keep in mind the following:

Change only 1 setting once! This is the only way to test whether a change is beneficial.

Most configurations can be changed at run time using SET GLOBAL. This is a very convenient way to quickly undo changes if something goes wrong. However, to be permanent you need to make a change in the configuration file.

1 Change that doesn't work even if you restart MySQL? Make sure you use the correct configuration file. Make sure you have the configuration in the right area (all the configurations mentioned in this article belong to [mysqld])

Server will not start after 1 configuration change: make sure you use the correct unit. For example, innodb_buffer_pool_size has units of MB and max_connection has no units.

Do not have duplicate configuration items in one configuration file. If you want to track changes, use version control.

Don't use naive calculations like, "My server has twice as much memory as it used to, so I'll have to double everything."

The basic configuration
You need to check the following three configuration items frequently. Otherwise, things could go wrong very quickly.

innodb_buffer_pool_size: This is the first option you should set after installing InnoDB. The buffer pool is where the data and index are cached: the bigger the better, ensuring that you use memory rather than hard disk for most reads. Typical values are 5-6GB(8GB memory), 20-25ES43en (32GB memory), 100-120ES45en (128GB memory).

innodb_log_file_size: This is the size of the redo log. The redo log is used to ensure that write operations are fast and reliable and to recover from crashes. 1 until MySQL 5.1, it was difficult to adjust because in one area you wanted it to be bigger to improve performance and in another you wanted it to be smaller to recover faster after a crash. Fortunately, since MySQL 5.5, crash recovery performance has improved significantly, so you can have both high write performance and crash recovery performance. 1 Until MySQL 5.5, the total size of the redo log is limited to 4GB(there can be 2 log files by default). This was improved in MySQL 5.6.

Setting innodb_log_file_size to 512M from the start (this gives you 1GB's redo log) will give you plenty of write space. If you know that your application needs to write data frequently and you use MySQL 5.6, you can start with 1 and call it 4G.

max_connections: If you often see 'Too many connections' errors, it is because the value of max_connections is too low. This is very common because the application does not close the database connection properly and you need a value larger than the default of 151 connections. A major drawback after the value of max_connection is set high (e.g. 1000 or higher) is that the server becomes unresponsive when running 1000 or higher active transactions. Using connection pooling in your application or process pooling in MySQL can help solve this problem.
InnoDB configuration
Starting with version 5.5 of MySQL, InnoDB is the default storage engine and is used by far more than any other storage engine. That's why it needs to be configured carefully.

innodb_file_per_table: This setting tells InnoDB whether to store data and indexes for all tables in a Shared table space (innodb_file_per_table = OFF) or to place data for each table in a separate.ibd file (innodb_file_per_table = ON). One file per table allows you to recycle disk space when drop, truncate, or rebuild tables. This is also necessary for some advanced features such as data compression. But it doesn't bring any performance gains. The main scenario where you don't want one file per table is to have a lot of tables (such as 10k+).

In MySQL 5.6, the default value for this property is ON, so for the most part you don't need to do anything. For previous versions you had to set this property to ON before loading the data because it only affected the newly created table.

innodb_flush_log_at_trx_commit: The default value is 1, indicating that InnoDB fully supports the ACID feature. This value is most appropriate when your primary concern is data security, such as on a primary node. But for slow disk (read and write) systems, it can be a huge overhead, as the flush to redo logs require an additional fsyncs each time a change is made. Setting this value to 2 is not very reliable (reliable) because committed transactions are logged to flush only once per second, but this value is acceptable for 1 scenario, such as the backup of the primary node. A value of 0 is faster, but you may lose some data in a system crash: only for backup nodes.

innodb_flush_method: This configuration determines how data and logs are written to the hard disk. 1 In general, if you have a hardware RAID controller that USES write-ES140en for its own cache and has battery power protection, you should set it to O_DIRECT. Otherwise, it should be set to fdatasync (the default) in most cases. sysbench is a great tool to help you decide on this option.

innodb_log_buffer_size: This configuration determines the cache allocated for transactions that have not yet been executed. The default value (1MB) 1 is generally sufficient, but if your transaction contains large binary objects or large text fields, the cache will quickly fill up and trigger additional I/O operations. Look at the Innodb_log_waits state variable. If it is not 0, add innodb_log_buffer_size.
Other Settings
query_cache_size: query cache (query cache) is a well-known bottleneck, even when there is not much concurrency. The best option is to disable it from 1, set query_cache_size = 0 (now the default for MySQL 5.6), and use other ways to speed up the query: optimize the index, increase copy spread load, or enable additional caching (such as memcache or redis). If you have enabled query cache for your application and have not found any problems, query cache may be useful. This is if you want to stop using it, you have to be careful.

log_bin: Turning on base 2 logging is a must if you want the database server to act as a backup to the primary node. If you do this, don't forget to set server_id to a unique value of 1. Even with only one server, this is useful if you want to do point-in-time data recovery: recover from your most recent backup (full backup) and apply the changes in the base 2 log (incremental backup). Base 2 logs are permanently saved once they are created. So if you don't want to run out of disk space, you can use PURGE BINARY LOGS to clear old files, or set expire_logs_days to specify how many days the logs will be automatically cleared.

Logging in base 2 is not without overhead, so if you do not need it on a replication node that is not the primary, it is recommended to turn this option off.

skip_name_resolve: When the client connects to the database server, the server resolves the hostname, and when DNS is slow, the connection is slow. It is therefore recommended that the skip_name_resolve option be turned off when the server is started without the DNS lookup. The only limitation of 1 is that you can only use the IP address later in the GRANT statement, so you must be careful when adding this setting to an existing system.

conclusion

Of course, there are other Settings that can come into play, depending on your load or hardware: under slow memory and fast disk, high concurrency, and write-intensive workloads, you'll need special adjustments. The goal here, however, is to quickly get a robust MySQL configuration without spending too much time tweaking a few trivial MySQL Settings or reading the documentation to find out which Settings are important to you.