10 performance configuration items that need to be adjusted after installing MySQL

In this blog, we will discuss ten performance settings that are recommended for adjustment after installing the MySQL database.

Typically, when we need to do a MySQL performance audit, we will review the MySQL configuration and make recommendations for improvements. In most cases, we recommend only changing a few core MySQL performance tuning parameters after installation, even though there are hundreds of options available. The purpose of this article is to give you a list of the most critical parameter settings and tell you how to adjust them.

Before you start adjusting

Even experienced people can make mistakes that can cause a lot of trouble. Therefore, before applying the configuration items recommended in this article, please keep the following in mind:

• Change one setting at a time! This is the only way to verify that your setup worked.
• Most configuration items can be modified at runtime using the SET GLOBAL command. This method is very convenient, and if problems occur after modification, the original settings can be restored immediately. But in the end, you still need to write this change to the configuration file to make it permanent.
• Sometimes even after MySQL is restarted, the parameters in the configuration file do not take effect. At this point you need to consider: Are you using the correct configuration file? Did you put this parameter in the correct place? (All configurations in this article belong to the [mysqld] section)
• If the database fails to start after changing the configuration, do you need to check whether the correct unit is used? For example, innodb_buffer_pool_size is in bytes, while max_connection has no units.
• Duplicate settings are not allowed in the configuration file. If you want to track changes to your configuration, use version control.
• Don't do naive math like "my new server has twice the RAM as my old one, so I can set all of my configuration options to twice their original values".

Basic Settings

Here we mainly explain 3 very important MySQL performance configuration items, you should see these parameters often. If you don't adjust, you are likely to run into problems.

innodb_buffer_pool_size:

This is the first configuration you should look at after installing any MySQL installation that uses the InnoDB storage engine. The buffer pool is used to cache data and indexes. It should be allocated as much memory as possible to ensure that most read operations are performed from memory rather than disk. Typical settings are 5-6GB (8GB RAM), 20-25G (32GB RAM), 100-120GB (128GB RAM).

innodb_log_file_size:

This option sets the size of the redo log. The redo log is used to ensure that written data can be written quickly and persisted, and can also be used for crash recovery. Prior to MySQL 5.1, this option was difficult to adjust because you wanted to increase the redo log size for better performance, but also want to reduce the redo log size for fast crash recovery. Fortunately, since MySQL 5.5, crash recovery performance has been greatly improved, and now you can have fast write performance while also meeting fast crash recovery requirements. Until MySQL 5.5, the total size of the redo log was limited to 4GB (with 2 log files by default). This was added in MySQL 5.6.

Set innodb_log_file_size = 512M (that is, 1GB of redo log) at startup to provide sufficient write space. If you know your application is write-heavy, you can increase it further.

max_connections:

If you often encounter "Too many connections" errors, it is because max_connections is too small. This error is commonly seen because the application does not properly close the connection to the database. You need to set the number of connections to a larger value than the default 151. A major disadvantage of setting max_connections too high (such as 1000 or more) is that the server can become slow or even unresponsive when running 1000 or more transactions. Using connection pooling on the application side or thread pooling on the MySQL side can help solve this problem.

InnoDB Settings

Since MySQL 5.5, InnoDB has become the default storage engine, and it is used much more frequently than other storage engines. That's why it's important to configure it carefully.

innodb_file_per_table:

This configuration item determines whether InnoDB uses a shared tablespace (innodb_file_per_table = OFF) to store data and indexes, or uses a separate ibd file for each table (innodb_file_per_table = ON). Using one file for each table will reclaim the table space when you drop, truncate, or rebuild the table. Some advanced features, such as compression, also require the use of independent tablespaces. However, this option does not provide any performance improvements.

In MySQL 5.6 and later versions, this configuration item is enabled by default, so in most cases, you do not need to do anything. For earlier MySQL versions, you need to set it to ON before starting, because it only affects newly created tables.

innodb_flush_log_at_trx_commit:

The default value is 1, which means that InnoDB fully supports ACID features. For example, on a master node, where you are primarily concerned with data security, this is the best setting. However, it can incur significant overhead on slow disk systems, since an additional fsync operation is required each time changes are flushed to the redo log. A setting of 2 will be less reliable, since committed transactions will only be flushed to the redo log once a second, but this may still be acceptable for a master in some cases, and is a good value for a slave in a replication relationship. Setting it to 0 is faster, but it is likely to lose some data in the event of a crash. This is only a good setting for slaves.

innodb_flush_method:

This setting determines how data and logs are flushed to disk. When the server hardware has a RAID controller, power-off protection, and a write-back cache mechanism, the most commonly used value is O_DIRECT; the default value fdatasync is used in most other scenarios. Sysbench is a good tool to help you choose between these two values.

innodb_log_buffer_size:

This setting is used to set the size of the buffer used to cache transactions that have not yet been committed. The default value (1MB) is usually sufficient, but once transactions have large blob/text fields, this buffer will fill up quickly and cause additional I/O load. Check the value of the innodb_log_waits status variable. If it is not 0, you need to increase innodb_log_buffer_size.

Other settings

query_cache_size:

Everyone knows that query cache is a bottleneck, even when concurrency is not high. The best setting is to disable the query cache (query_cache_size = 0) on the first day of use. This option is disabled by default after MySQL 5.6. We can improve query speed in other ways: design good indexes, increase read-write separation, or use additional cache (memcache or redis for instance). If you have MySQL with query cache enabled and have never experienced problems, then query cache is probably beneficial to you and you should proceed with caution if you want to disable it.

log_bin:

If you want a node to be the primary node in a replication relationship, it is necessary to enable binary log. At the same time, you need to set a globally unique server_id. If it is a single-instance database, if you want to restore the data to a previous point in time (restore using the latest backup, and then recover using binlog), then binary logs are required. Once a binary log is created, it will be saved permanently, so if you do not want to use up disk space, you should use PURGE BINARY LOGS to clean up old binary log files, or set the expire_logs_days option to specify how many days it will take to automatically clean up expired binary logs.

Binary file logging consumes resources, so in a master-slave replication environment, if the standby database does not need Binlog, it can be disabled.

skip_name_resolve:

When a client connects, the server performs hostname resolution, and when DNS is slow, the connection will be slow to establish. It is therefore recommended to set skip-name-resolve at boot time to disable DNS lookups. The only limitation is that the GRANT statement can only be used with IP addresses, so extreme caution is required when adding this option to an existing system.

in conclusion

Of course, there are other settings that can be used for tuning, depending on your load and hardware conditions: for example, small memory, high-speed disks, high concurrency, and write-intensive loads require specific tuning. However, the purpose of this article is to provide several MySQL performance tuning configuration items, so that you can quickly configure a reasonable MySQL configuration file and understand which parameters are important to you without spending a lot of time reading official documents.

The above are the details of the 10 performance configuration items that need to be adjusted after installing MySQL. For more information about MySQL performance configuration items, please pay attention to other related articles on 123WORDPRESS.COM!