The maxidleconns of MySQL is unreasonable, which will become the cause of short connection

Time:2022-5-27

1 background

Recently, there have been some performance problems in graphite document online business. In case of sudden traffic, the performance of one business has decreased sharply. This service is a business that depends on the database and will obtain the data in the database in batches. After a series of troubleshooting, it is found that the number of connections between the service and the database often exceeds maxidleconns. Therefore, it is suspected that the performance problem is caused by the configuration of the database. Therefore, the following code for the database is analyzed and relevant experiments are done.

2 configuration interpretation


maxIdleCount      int                    // zero means defaultMaxIdleConns; negative means 0
maxOpen      int          // <= 0 means unlimited
maxLifetime    time.Duration     // maximum amount of time a connection may be reused
maxIdleTime       time.Duration          // maximum amount of time a connection may be idle before being closed

You can see the above four configurations, which are the most important configurations of our go MySQL client.

Maxidlecount is the maximum number of idle connections. It is not configured by default. It is the two maximum idle connections

Maxopen maximum number of connections. It is not configured by default. It does not limit the maximum number of connections

Maxlifetime maximum connection lifetime

MaxIdleTime maximum idle connection lifetime

3 source code analysis

Our scenario is that the number of connections established between the client and MySQL is often greater than the maximum number of idle connections. What problems will this cause? Let’s look at the source code in the figure below.

We can see that when the maximum number of idle connections is less than the number of connections established between the client and the database, false will be returned, and the maximum number of connections closed counter will be increased by 1.

Then in the figure above, we can see that the connection is closed (there is no buffer time left in the MySQL source code). The MySQL client operation of go will lead to the load exceeding the maximum number of idle connections due to the excessive request magnitude in case of sudden traffic. When the new connection is put into the connection pool, it will be ruthlessly closed and become a short connection, resulting in further deterioration of your service performance.

4 Experiment

4.1 simulate online concurrency greater than maxidconns

Test the code. In order to detect the above logic, assume the following scenarios, and set goroutine with the maximum number of connections of 100, the maximum number of idle connections of 1 and the number of concurrent connections of 10 to request the database. Through the statistics of maxidleclosed in MySQL stats, we can see the following figure. Our connection is constantly closed.

4.2 simulate online concurrency less than maxidconns

The test code assumes the following scenarios. Set the goroutine with the maximum number of connections of 100, the maximum number of idle connections of 20 and the number of concurrent connections of 10 to request the database. You can see that there is no maxidleclosed closing statistics in the following figure.

4.3 packet capture verification: the number of online concurrency is greater than maxidconns

Test the code. In order to verify that you don’t understand the wrong code, it’s safest to grab a package. We put a select {} in the main function. After the program executes the MySQL statement, look at the TCP status and packet capture data.

It can be found that the status statistics of TCP are consistent with those of MySQL client, and there are fin packets.

5 Summary

In the case of burst traffic, because the request magnitude is too large and exceeds the load of the maximum number of idle connections, the new connection will be closed when it is put into the connection pool, turning the connection into a short connection, resulting in further deterioration of service performance. In order to avoid this situation, the measures that can be optimized are listed below.

Set maxidleconns large in advance to avoid short connections

Do a good job in MySQL read-write separation

Improve the throughput of MySQL: simplify the return fields, and don’t return unnecessary fields. It can reuse connections quickly enough

The packet throughput should not be too large to avoid subcontracting

Optimize the connection pool. When the number of connections between the client and MySQL is greater than the maximum idle connection, the shutdown can be delayed (it is not supported officially, and it is estimated that it can only be realized by itself)

It’s better not to put the read request in mysql, but in redis as much as possible

6 test code

https://github.com/gotomicro/test/tree/main/gorm

The above is the details of the reason why MySQL’s maxidleconns is unreasonable and will become a short connection. For more information about MySQL’s maxidleconns, please pay attention to other relevant articles of developeppaer!