The key to MySQL performance tuning lies in the rational configuration of my.cnf parameters. 1. Innodb_buffer_pool_size is recommended to set to 50%~80% of physical memory. For example, 64GB of memory can be set to 48G to improve data and index cache efficiency; 2. max_connections can be set to 500 or higher according to concurrency requirements to avoid connection timeouts; 3. For MySQL 5.7 and previous versions, query cache should be turned off in scenarios that write more and read less (query_cache_type=0, query_cache_size=0), read-only scenarios can be enabled and set to 64M~256M; 4. tmp_table_size and max_heap_table_size are recommended to set to 64M or higher to reduce the use of temporary disk tables; 5. Innodb_log_file_size is recommended to set to 512M or 1G to improve write performance. Other optimizations include adjusting the innodb_flush_log_at_trx_commit policy, table_open_cache improves table opening speed, and innodb_io_capacity adaptation disk type. Different business loads need to be flexibly configured in accordance with actual conditions, and the buffer pool and connection number should be adjusted first.

A large part of the work of MySQL's performance tuning focuses on tuning my.cnf (or my.ini ) configuration file. If you find that the database response is slow, the connection is frequent, or the resource utilization is extremely high, it is probably not a problem with the code, but the configuration is not adjusted properly.

Here are some key MySQL configuration parameters that have a great impact on performance and are suitable for most production environments to adjust properly.

1. innodb_buffer_pool_size

This is one of the most important configuration items of the InnoDB engine, which determines the memory size of cached data and indexes.

• The default value is usually small (such as 128M), which is far from enough for applications with a large number of read and write operations.
• It is recommended to set it to 50%~80% of physical memory, but leave enough space for the system and other services.
• If you are using a dedicated database server and the amount of data is not large, you can consider allocating about 70% of the memory directly to this parameter.
• If your server has 64GB of memory, you can try setting it to innodb_buffer_pool_size = 48G .

Note: If this value is too large, it may cause system swap, which will affect performance.

2. max_connections

Controls the maximum number of concurrent connections allowed by MySQL.

• The default is usually 151, but it is easy to reach the upper limit in high concurrency scenarios.
• It can be set to 500 or higher according to business expectations, but also note:
  • Each connection will take up a certain amount of memory, setting it too high may cause resource exhaustion.
  • If the upper limit is often approached, it is recommended to optimize slow queries or add connection pool middleware at the same time.

3. query_cache_type and query_cache_size (only available for MySQL 5.7 and earlier)

These two parameters are used to enable and control the query caching function.

• Query caching can significantly improve the performance of duplicate queries, but can also lead to write bottlenecks.
• In scenarios where more writes, reads less, it is recommended to turn off ( query_cache_type = 0 , query_cache_size = 0 ).
• For read-only or low-frequency updated data, cache space can be turned on and appropriately allocated, such as 64M to 256M.

MySQL 8.0 has removed the query caching function, so these parameters are not required.

4. tmp_table_size and max_heap_table_size

These two parameters together determine the maximum capacity of the memory temporary table.

• When performing complex queries (such as GROUP BY, DISTINCT, etc.), MySQL will first try to create temporary tables in memory.
• If the limit is exceeded, it will automatically be transferred to disk tables, resulting in performance degradation.
• It is recommended to set both to 64M or higher, for example:

 tmp_table_size = 64M
max_heap_table_size = 64M

5. innodb_log_file_size

Controls the size of each InnoDB log file.

• Larger log files help improve write performance, especially when there are a lot of write operations.
• The default may be 48M or 128M. If it is too small, it will cause frequent disk flushing.
• Recommended setting is 512M or 1G, such as:

 innodb_log_file_size = 512M

After modifying this parameter, MySQL needs to be restarted, and the log file will be regenerated when the first startup is started.

Other common optimization points (optional)

• innodb_flush_log_at_trx_commit
  Controls the transaction log refresh policy, which is to flush the disk every time you commit (the safest). If you can accept certain risks, you can set it to 2 (sweep once per second) to improve performance.

• table_open_cache
  Improve the speed of opening tables, especially databases with large numbers of tables. The default is 2000, which can be adjusted appropriately according to the actual quantity.

• innodb_io_capacity
  Set disk IO capability, mechanical hard disk is generally set to 200, and SSD can be set to 1000 or higher.

Basically, these more critical my.cnf parameters are all. Different business loads require different configuration combinations, so don’t blindly copy other people’s experiences. You can start with buffer pool and connection number first, and then gradually adjust other parameters.

The above is the detailed content of Key MySQL configuration parameters for performance (my.cnf). For more information, please follow other related articles on the PHP Chinese website!