When we are hired to monitor MySQL performance, people expect us to review the MySQL configuration and offer suggestions for improvement. Many are surprised afterward because we recommend changing only a few settings, even though there are hundreds of configuration options. The goal of this article is to give you a list of the most critical configuration items.
We gave similar advice on our blog a few years ago, but the world of MySQL changes so rapidly!
Before We Begin…
Even experienced people make mistakes that can cause a lot of trouble. So before blindly applying these recommendations, please keep the following in mind:
-
Change only one setting at a time! This is the only way to test whether a change is beneficial.
-
Most settings can be changed at runtime using SET GLOBAL. This is a very convenient method that allows you to quickly roll back changes if problems arise. However, to make them permanent, you need to make the changes in the configuration file.
-
A change didn’t take effect even after restarting MySQL? Make sure you are using the correct configuration file. Make sure you placed the setting in the correct section (all settings mentioned in this article belong in [mysqld]).
-
The server won’t start after changing a setting: Make sure you used the correct units. For example, the unit for innodb_buffer_pool_size is MB, while max_connections has no unit.
-
Do not have duplicate configuration entries in a configuration file. If you want to track changes, use version control.
-
Don’t use naive calculation methods, such as “now my server has twice the memory as before, so I’ll just double all the values.”
Basic Configuration
You need to frequently check the following 3 configuration items. Otherwise, problems may arise very quickly.
innodb_buffer_pool_size: This is the first option you should set after installing InnoDB. The buffer pool is where data and indexes are cached: the larger this value, the better, ensuring that you use memory rather than disk for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), and 100-120GB (128GB RAM).
innodb_log_file_size锛?/strong>This is the size of the redo log. The redo log is used to ensure fast and reliable write operations and for crash recovery. Up to MySQL 5.1, it was difficult to tune because, on one hand, you wanted it larger for better performance, and on the other, you wanted it smaller for faster crash recovery. Fortunately, since MySQL 5.5, crash recovery performance has improved greatly, so you can have both high write performance and good crash recovery performance. Up to MySQL 5.5, the total redo log size was limited to 4GB (with a default of 2 log files). This was increased in MySQL 5.6.
Start by setting innodb_log_file_size to 512M (giving you 1GB of redo log), which will provide ample write space. If you know your application requires frequent writes and you are using MySQL 5.6, you can start by setting it to 4G.
max_connections: If you frequently see the ‘Too many connections’ error, it’s because the max_connections value is too low. This is very common because applications do not close database connections properly, and you need a value higher than the default of 151 connections. A major drawback of setting max_connections too high (e.g., 1000 or higher) is that the server can become unresponsive when running 1000 or more active transactions. Using a connection pool in your application or a thread pool in MySQL can help solve this problem.
InnoDB Configuration
Starting from MySQL 5.5, InnoDB is the default storage engine and is used far more than any other storage engine. That is why it needs careful configuration.
innodb_file_per_table锛歍his setting tells InnoDB whether to store data and indexes for all tables in a shared tablespace (innodb_file_per_table = OFF) or in a separate .ibd file per table (innodb_file_per_table = ON). File-per-table allows you to reclaim disk space when you drop, truncate, or rebuild a table. It is also necessary for some advanced features like data compression. However, it does not bring any performance benefit. The main scenario where you would not want file-per-table is when you have a very large number of tables (e.g., 10k+).
In MySQL 5.6, this attribute defaults to ON, so in most cases you don’t need to do anything. For previous versions, you must set this attribute to ON before loading data, because it only affects newly created tables.
innodb_flush_log_at_trx_commit锛歍he default value is 1, meaning InnoDB is fully ACID-compliant. This value is most suitable when your primary concern is data safety, such as on a master node. However, for systems with slow disk (read/write) speed, it can bring significant overhead because each flush of changes to the redo log requires extra fsyncs. Setting it to 2 results in less reliability because committed transactions are only flushed to the redo log once per second, but it is acceptable for some scenarios, such as for a backup node of a master node. A value of 0 is even faster, but data loss may occur during a system crash: it is only suitable for backup nodes.
innodb_flush_method: This configuration determines the method used to write data and logs to disk. Generally, if you have a hardware RAID controller with a battery-backed write-back cache, you should set this to O_DIRECT; otherwise, in most cases, you should set it to fdatasync (the default). sysbench is a good 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 committed. Its default value (1MB) is generally sufficient, but if your transactions contain large binary objects or large text fields, this cache can quickly fill up and trigger extra I/O operations. Check the Innodb_log_waits status variable; if it is not 0, increase innodb_log_buffer_size.
Other Settings
query_cache_size: The query cache is a well-known bottleneck, even under low concurrency. The best option is to disable it from the start by setting query_cache_size = 0 (now the default in MySQL 5.6) and using other methods to speed up queries: optimize indexes, add replicas to distribute the load, or enable additional caching (like memcache or redis). If you have enabled query cache for your application and haven’t seen any issues, the query cache might be working for you. But if you want to disable it, be careful.
log_bin锛?/strong>If you want your database server to act as a replication master, enabling the binary log is mandatory. If you do this, don’t forget to set server_id to a unique value. Even if you only have a single server, this (enabling the binary log) is useful if you want to do point-in-time data recovery: restore from your latest backup (full backup) and apply the changes from the binary logs (incremental backup). Binary logs are kept forever once 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 after how many days logs will be automatically purged.
Recording binary logs is not without overhead, so if you don’t need it on a non-master replica node, it is recommended to disable this option.
skip_name_resolve锛?/strong>When a client connects to the database server, the server performs hostname resolution, and when DNS is slow, establishing a connection is also slow. Therefore, it is recommended to enable the skip_name_resolve option at startup to skip DNS lookups. The only limitation is that afterward, you can only use IP addresses in GRANT statements, so you must be especially careful when adding this setting to an existing system.
Summary
Of course, there are other settings that can be effective depending on your workload or hardware: in scenarios with slow memory and fast disks, high concurrency, and write-intensive loads, you will need special tuning. However, the goal here is to enable you to quickly obtain a robust MySQL configuration without spending too much time tweaking insignificant MySQL settings or reading documentation to find out which settings are important to you.
Original English Article: 10 MySQL settings to tune after installation
Translation Participants(3 people)锛?/span>BoydWang, 缇庡ソ鐨?014, DrZ