The unreasonable MaxIdleConns of MySQL will cause short connections

1 Background

Recently, some performance issues have occurred in Shimo Document's online business. Under sudden traffic conditions, the performance of one business has dropped sharply. This service is dependent on the database and will obtain data from the database in batches. After a series of troubleshooting, we found that the number of connections from the service to the database often exceeded MaxIdleConns. We suspected that the performance problem was caused by the database configuration, so we analyzed the database code and conducted related experiments.

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 that the above four configurations are the most important configurations of our Go MySQL client.

maxIdleCount The maximum number of idle connections. By default, it is not configured and is 2 maximum idle connections.

maxOpen The maximum number of connections. By default, it is not configured, which means that the maximum number of connections is not limited.

maxLifetime Maximum connection lifetime

maxIdleTime Maximum lifetime of idle connections

3 Source code analysis

Our scenario is that the number of connections established by the client to 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 is returned and the maximum number of connection closure counter is increased by 1.

Then in the picture above, we can see that the connection is closed (MySQL source code does not leave any buffer time before closing). This operation of the Go MySQL client will result in that when there is a burst of traffic, the request volume is too large and exceeds the load of the maximum number of idle connections. In this case, when new connections are put into the connection pool, they will be ruthlessly closed and become short connections, causing your service performance to further deteriorate.

4 Experiments

4.1 Simulate the situation where the number of online concurrent calls is greater than MaxIdConns

Test code,In order to test the above logic, the following scenario is assumed. The maximum number of connections is set to 100, the maximum number of idle connections is 1, and the number of concurrent goroutines is 10 to request the database. Through the statistics of maxIdleClosed in MySQL stats, we can see the following figure, our connections are constantly being closed.

4.2 Simulating the situation where the number of online concurrent connections is less than MaxIdConns

The test code assumes the following scenario: the maximum number of connections is set to 100, the maximum number of idle connections is set to 20, and the number of concurrent goroutines is set to 10 to request the database. As can be seen in the figure below, there is no MaxIdleClosed closing statistics.

4.3 Capture packets to verify that the number of online concurrency is greater than MaxIdConns

Test the code. In order to verify that you have not misunderstood the code, it is safest to capture a package. We put a select{} in the main function. After the program executes the mysql statement, we check the TCP status and packet capture data.

It can be found that the TCP status statistics are consistent with the MySQL client statistics, and the fin package exists.

5 Conclusion

When there is a burst of traffic, the request volume is too large and exceeds the load of the maximum number of idle connections. In this case, new connections will be closed when they are placed in the connection pool, turning the connections into short connections, causing further deterioration of service performance. To avoid this situation, the following are some optimization measures that can be taken.

Set maxIdleConns to a larger value in advance to avoid short connections

Do a good job of mysql read and write separation

Improve MySQL throughput: streamline the returned fields, do not return unnecessary fields, and reuse connections quickly

The throughput packet should not be too large to avoid packet fragmentation.

Optimize the connection pool. When the number of connections from the client to MySQL is greater than the maximum idle connections, the closure can be delayed (officially not supported, probably can only be implemented by yourself)

It is best not to put read requests in MySQL, try to put them in redis

6 Test Code

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

The above is the detailed content of the reason why MySQL's MaxIdleConns is unreasonable and will become a short connection. For more information about MySQL's MaxIdleConns unreasonable, please pay attention to other related articles on 123WORDPRESS.COM!