I happened to come across this article: Configuring sql.DB for Better Performance. It’s a good article that clearly lays out the three settings for controlling connection pool sizes. That said, I just can’t bring myself to agree with the settings recommended in the article. I’d like to explain what settings I would recommend myself.
Limit ConnMaxLifetime instead of MaxIdleConns
Allowing just 1 idle connection to be retained and reused makes a massive difference to this particular benchmark — it cuts the average runtime by about 8 times and reduces memory usage by about 20 times. Going on to increase the size of the idle connection pool makes the performance even better, although the improvements are less pronounced.
The issue lies in the words "to this particular benchmark." In this benchmark, the database is constantly receiving queries in 8 parallel. When one query finishes, another pops up immediately, so with DB.SetMaxIdleConns(1) you get significant results.
The way this benchmark works is effective in certain cases, such as when batch processing insertion requests for large amounts of data in a database. However, it doesn’t work for web applications.
I’ve written up a simple simulation to demonstrate an application that handles 1,000 queries a second. The queries are evenly distributed and appear randomly, with each query taking 10 ms to form a new connection. (View the Github gist for this simulator here.)
Let’s compare how MaxIdleConns(4) and MaxIdleConns(10) perform when using MaxOpenConns(20). The orange line is the overall number of connections, the blue line is the number of connections in use, and the green line is the maximum wait time before a connection becomes available shown in milliseconds.
Despite handling a 1,000 queries, there were 285 connections for MaxIdleConns(4), while with MaxIdleConns(10), it went down to 69. On the other hand, even after the initial load goes away the number of stabilized connections still increase.
Next let’s look at a simulator for SetMaxIdleConns(100); SetConnMaxLifetime(time.MilliSecond * 300).
We’re creating 80 (20 x 4) connections, which is more than the 69 connections from MaxIdleConns(10), but the lifetime is shortened so that we can more easily see the operation. If we extend the simulation time by 100 seconds, the number of connections is about 690 for MaxIdleConns(10) and about 80 for SetConnMaxLifetime(time.Second * 30).
You might notice that in this graph, the reconnections are clustered at a specific time which has an extended latency. This is because the simulation is completely evenly distributed, with all the connections in the beginning created at the same time. In an application where the load changes depending on the time, the times the connections are made would be further apart, so spikes like this wouldn’t happen. Similar to the graph above, this is what’s happening with the load at 1000 ms in the next graph, after the load gradually increases over the course of 200 ms.
Other Reasons for Using SetConnMaxLifetime
I proposed and implemented DB.SetConnMaxLifetime(). This is a better method than SetMaxIdleConns() for reducing the number of idle connections, but that’s not all it can do.
As introduced in "Configuring sql.DB for Better Performance," there is a chance that MySQL’s wait_timeout setting will automatically close idle connections in the server. It will also close TCP connections that have not been used by the OS or router for extended periods of time. In either case, we first find out that the TCP has been closed when trying to receive the response after go-sql-driver/mysql sends a query. It may take several dozen seconds before the disconnection is detected. As such, it’s impossible to know if the sent query has been carried out. This renders a safe retry impossible.
In order to avoid this danger, we should close connections that haven’t been used for extended periods of time instead of reusing them. We should simply create new connections. SetConnMaxLifetime() is an API that sets the lifetime of a connection. If you set it to 10 seconds, you won’t be able to reuse connections that haven’t been used for 10 seconds.
By setting the lifetime of connections, you can mop up all sorts of other problems.
It makes it easier to increase and decrease servers when the database server is load balanced.
It makes failover easier for database servers.
When changing settings for MySQL online, you can root out and stop connections operating under old settings.
After weighing how much adding a separate API that limits a connection’s idle time would affect performance in a real environment against the difficulty of implementing it in sql.DB, I didn’t add one.
Recommended sql.DB Settings
Definitely set SetMaxOpenConns(). You need this in order to stop opening new connections and sending queries when the load is high and server response slows. If possible, it’s good to do a load test and set the minimum number of connections to ensure maximum throughput, but even if you can’t do that, you should decide on a reasonably appropriate number based on max_connection and the number of cores.
Configure SetMaxIdleConns() to be equal to or higher than SetMaxOpenConns(). Let SetConnMaxLifetime handle closing idle connections.
Set SetConnMaxLifetime() to be the maximum number of connections x 1 second. In most environments, a load of one connection per second won’t be a problem. When you want to set it for longer than an hour, discuss that with an infrastructure/network engineer.