MySQL - A detailed introduction to the 10 configurations that must be adjusted for the newly installed MySQL

Are you still worried about the newly installed mysql service and don’t know which default configurations to modify? There are more than 100 adjustable parameters of mysql. Which of the most important parameters should be adjusted immediately! Immediately!

This article mainly introduces 10 configurations that must be adjusted for MySQL optimization. Using these methods can allow you to quickly obtain a robust MySQL configuration. Friends in need can refer to the following:

When we are being When hired to monitor MySQL performance, people expect us to review the MySQL configuration and make suggestions for improvements. Many people are surprised when we suggest they change only a few settings, even though there are hundreds of configuration options. The purpose of this article is to give you a very important list of configuration items.

We gave this advice on the blog a few years ago, but the world of MySQL is changing so fast!

Written before we start...

Even though Even experienced people can make mistakes, which can cause a lot of trouble. So before blindly applying these recommendations, please remember the following:

Change only one setting at a time! This is the only way to test whether the changes are beneficial.

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

A change does not work even if Mysql is restarted?

Please make sure you use the correct configuration file. Please make sure you place the configuration in the correct area (all configurations mentioned in this article belong to [mysqld])

The server cannot start after changing a configuration: Please make sure you use the correct configuration The unit.

For example, the unit of innodb_buffer_pool_size is MB and max_connection has no unit.

Do not have duplicate configuration items 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 of before, so I have to change all values ​​​​to double the previous value."

1. Basic configuration

You need to check the following three configuration items frequently. Otherwise, problems may arise 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 the value, the better. This ensures that you use memory instead of the hard disk for most read operations. Typical values ​​are 5-6GB (8GB memory), 20-25GB (32GB memory), 100-120GB (128GB memory).

innodb_log_file_size:

This is the size of the redo log. Redo logging is used to ensure write operations are fast and reliable and to recover from crashes.

Up until MySQL 5.1, it was difficult to tune because on the one hand you wanted it to be bigger to improve performance, and on the other hand you wanted to make it smaller to recover faster after a crash. Fortunately, since MySQL 5.5, the crash recovery performance has been greatly improved, so that you can have high write performance and crash recovery performance at the same time. Until MySQL 5.5, the total size of the redo log was limited to 4GB (there can be 2 log files by default). This has been improved in MySQL 5.6.

Set innodb_log_file_size to 512M from the beginning (so there is a 1GB redo log), which will give you ample space for write operations. If you know that your application needs to write data frequently and you are using MySQL 5.6, you can set it to 4G from the beginning.

max_connections:

If you often see the 'Too many connections' error, it is because the value of max_connections is too low. This is very common because the application does not close database connections properly and you need a higher value than the default 151 connections. A major drawback when the max_connection value is set high (such as 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.

2. InnoDB configuration

Starting from MySQL version 5.5, InnoDB is the default storage engine and it is used much more than any other storage engine. That's why it needs to be configured carefully.

innodb_file_per_table:

This setting tells InnoDB whether it needs to store data and indexes for all tables in a shared table space (innodb_file_per_table = OFF) or for each table The data is placed in a separate .ibd file (innodb_file_per_table = ON). One file per table allows you to reclaim disk space when dropping, truncating, or rebuilding tables. This is also necessary for some advanced features, such as data compression. But it doesn't bring any performance gain. The main scenario where you don't want one file per table is if you have a very large number of tables (e.g. 10k+).

In MySQL 5.6, the default value of this property is ON, so in most cases you don't need to do anything. With previous versions you had to set this property to ON before loading data, as it only affected newly created tables.

innodb_flush_log_at_trx_commit:

The default value is 1, indicating that InnoDB fully supports ACID features. This value is most appropriate when your main concern is data security, such as on a master node. But for systems with slow disk (read and write) speeds, it will bring a huge overhead, because each time flushing changes to the redo log requires additional fsyncs. Setting its value to 2 will result in less reliable (reliable) because the submitted transaction is only flushed to the redo log once per second, but it is acceptable for some scenarios, such as the backup node of the primary node. This value is acceptable. of. A value of 0 is faster, but may result in some data loss in the event of a system crash: only applies to backup nodes.

innodb_flush_method:

This configuration determines how data and logs are written to the hard disk. Generally speaking, if you have a hardware RAID controller and its independent cache uses a write-back mechanism and has battery power failure protection, it should be configured as O_DIRECT; otherwise, in most cases it should be set to fdatasync (default value). sysbench is a great tool to help you decide this option.

innodb_log_buffer_size:

This configuration determines the cache allocated for transactions that have not yet been executed. The default value (1MB) is generally sufficient, but if your transaction contains large binary objects or large text fields, this cache will quickly fill up and trigger additional I/O operations. Look at the Innodb_log_waits status variable, if it is not 0, increase innodb_log_buffer_size.

3. Other settings

query_cache_size:

query cache (query cache) is a well-known bottleneck, even in This is also true when there is not much concurrency.

The best option is to disable it from the beginning, set query_cache_size = 0 (now the default in MySQL 5.6) and use other methods to speed up queries: optimize indexes, add copies to spread the load, or enable additional cache (such as memcache or redis). If you have enabled query cache for your application and haven't noticed any problems, query cache may be useful to you. This is something to be careful of if you want to disable it.

log_bin:

If you want the database server to act as a backup node for the master node, then enabling the binary log is necessary. If you do this, don't forget to set server_id to a unique value. Even with just one server, this (turning on binary logging) is useful if you want to do point-in-time data recovery: restore from your most recent backup (full backup) and apply the changes in the binary log (incremental backup ). Once created, the binary log is saved permanently. So if you don't want to run out of disk space, you can use PURGE BINARY LOGS to purge old files, or set expire_logs_days to specify how many days after which the logs will be automatically purged.

Logging binary logs is not without overhead, so it is recommended to turn this option off if you do not need it on a replica node that is not the primary node.

skip_name_resolve:

When the client connects to the database server, the server performs host name resolution, and when DNS is slow, establishing a connection will also be slow. It is therefore recommended to turn off the skip_name_resolve option when starting the server without doing a DNS lookup. The only limitation is that only IP addresses can be used in GRANT statements later, so care must be taken when adding this setting to an existing system.

Summary

Of course there are other settings that may work, depending on your load or hardware: slow memory and fast disk, high concurrency and write-intensive Under load, you will need special adjustments. However, the goal here is that you can quickly get a robust MySQL configuration without spending too much time adjusting some insignificant MySQL settings or reading the documentation to find out which settings are important to you.

The above is the detailed content of MySQL - A detailed introduction to the 10 configurations that must be adjusted for the newly installed MySQL. For more information, please follow other related articles on the PHP Chinese website!