There are many tutorials about the types of Go and how to use it to perform SQL database queries. But most of the content doesn't tell the SetMaxOpenConns(), SetMaxIdleConns() and SetConnMaxLifetime() methods, which you can use to configure behavior and change their performance.
In this article I will explain in detail the role of these settings and explain the (positive and negative) effects they can have.
Open and idle connections
An object is a database connection pool that contains "using" and "free" connections. A connection that is being used refers to the fact that you are using it to perform database tasks, such as executing SQL statements or row queries. When the task completes the connection, it is idle.
When you create an execution database task, it first checks whether there are free connections available in the connection pool. If there is a connection available, Go reuses the existing connection and marks it as in use during the task. If there is no idle connection in the pool and you need one, then Go will create a new connection.
SetMaxOpenConns method
By default, there is no limit on the number of connections to be opened at the same time (including in use + idle). But you can implement custom restrictions through the SetMaxOpenConns() method, as shown below:
// Initialize a new connection pooldb, err := ("postgres", "postgres://user:pass@localhost/db")
if err != nil {
(err)
}
// Set the current maximum number of open connections (including idle and in use) to 5.// If set to 0, it means there is no limit on the number of connections, and the default is no limit on the number.(5)
In this example code, the connection pool now has 5 concurrently opened connections. If all 5 connections are already marked as being in use and another new connection is required, the application will be forced to wait until one of the 5 connections is released and becomes idle.
To illustrate the impact of changing MaxOpenConns, I ran a benchmark that sets the maximum number of open connections to 1, 2, 5, 10 and infinite. Benchmarking executes parallel INSERT statements on a PostgreSQL database, where you can find the code. Test results:
BenchmarkMaxOpenConns1-8 500 3129633 ns/op 478 B/op 10 allocs/op
BenchmarkMaxOpenConns2-8 1000 2181641 ns/op 470 B/op 10 allocs/op
BenchmarkMaxOpenConns5-8 2000 859654 ns/op 493 B/op 10 allocs/op
BenchmarkMaxOpenConns10-8 2000 545394 ns/op 510 B/op 10 allocs/op
BenchmarkMaxOpenConnsUnlimited-8 2000 531030 ns/op 479 B/op 9 allocs/op
PASS
For this benchmark, we can see that the more connections allowed to be opened, the less time it takes to perform INSERT operations on the database (with 1 open connections, the execution speed is 3129633ns/op, while infinite connections: 531030ns/op—about 6 times faster). This is because the more connections are allowed to be opened, the more database queries can be executed concurrently.
SetMaxIdleConns method
By default, up to 2 free connections are allowed in the connection pool. You can change it through the SetMaxIdleConns() method as follows:
// Initialize a new connection pooldb, err := ("postgres", "postgres://user:pass@localhost/db")
if err != nil {
(err)
}
// Set the maximum number of idle connections to 5. Setting this value to less than or equal to 0 will mean that no idle connections are left.(5)
In theory, allowing more idle connections in the pool will improve performance, because it is unlikely to establish new connections from scratch - thus helping improve database performance.
Let's take a look at the same benchmarks, with maximum idle connections set to none, 1, 2, 5 and 10:
BenchmarkMaxIdleConnsNone-8 300 4567245 ns/op 58174 B/op 625 allocs/op
BenchmarkMaxIdleConns1-8 2000 568765 ns/op 2596 B/op 32 allocs/op
BenchmarkMaxIdleConns2-8 2000 529359 ns/op 596 B/op 11 allocs/op
BenchmarkMaxIdleConns5-8 2000 506207 ns/op 451 B/op 9 allocs/op
BenchmarkMaxIdleConns10-8 2000 501639 ns/op 450 B/op 9 allocs/op
PASS
When MaxIdleConns is set to none, a new connection must be created from scratch for each INSERT, and we can see from the benchmark that the average runtime and memory usage are relatively high.
Only allowing retention and reuse of one idle connection is particularly significant for the benchmarking – it reduces average runtime by about 8 times and reduces memory usage by about 20 times. Continuing to increase the size of the idle connection pool will make performance better, although the improvement is not obvious.
So, should you maintain a large pool of idle connections? The answer depends on the application. It is important to realize that there is a price to keep an idle connection—it takes up memory that can be used for applications and databases.
Another possibility is that if a connection is idle for too long, it may become unavailable. For example, MySQL's wait_timeout setting will automatically close any unused connections within 8 hours (default).
When this happens, it is handled gracefully. The bad connection will automatically retry twice, then abandon, at which point Go removes the connection from the connection pool and creates a new connection. Therefore, setting MaxIdleConns too large may cause the connection to become unavailable, taking up more resources than a smaller idle connection pool (fewer connections used more frequently). So, if you will most likely be using it again soon, you just need to keep an idle connection.
Finally, it is important to point out that MaxIdleConns should always be less than or equal to MaxOpenConns. Go enforces this and automatically reduces MaxIdleConns if necessary.
SetConnMaxLifetime method
Now let's take a look at the SetConnMaxLifetime() method, which sets the maximum length of time the connection can be reused. This is useful if your SQL database also implements the maximum connection lifecycle, or for example, you want to conveniently exchange the database after the load balancer.
You can use it like this:
// Initialize a new connection pooldb, err := ("postgres", "postgres://user:pass@localhost/db")
if err != nil {
(err)
}
// Set the maximum lifetime of the connection to 1 hour. Setting it to 0 means there is no maximum lifetime and the connection will be reusable forever (this is the default behavior)()
In this example, all connections will "expirate" 1 hour after creation and cannot be reused after expiration. But note:
- This does not guarantee that the connection will be in the pool for a full hour; most likely, for some reason, the connection becomes unavailable and automatically closes before that.
- A connection can still be used more than an hour after creation - it just cannot be reused after this time.
- This is not an idle timeout. The connection will expire 1 hour after the first creation—not 1 hour after the last idle.
- Automatically run a cleanup operation every second to remove "expired" connections from the connection pool.
In theory, the shorter the ConnMaxLifetime, the more frequent the connection expires—and therefore, the more frequent the connection needs to be created from scratch. To illustrate this, I ran a benchmark that sets ConnMaxLifetime to 100ms, 200ms, 500ms, 1000ms, and infinite (reused forever), with default setting to unlimited open connections and 2 idle connections. These time periods are obviously much shorter than you can use in most applications, but they help illustrate the behavior very well.
BenchmarkConnMaxLifetime100-8 2000 637902 ns/op 2770 B/op 34 allocs/op
BenchmarkConnMaxLifetime200-8 2000 576053 ns/op 1612 B/op 21 allocs/op
BenchmarkConnMaxLifetime500-8 2000 558297 ns/op 913 B/op 14 allocs/op
BenchmarkConnMaxLifetime1000-8 2000 543601 ns/op 740 B/op 12 allocs/op
BenchmarkConnMaxLifetimeUnlimited-8 3000 532789 ns/op 412 B/op 9 allocs/op
PASS
In these specific benchmarks, we can see that memory usage increases by more than 3 times over the 100ms lifetime compared to the infinite lifetime, and the average runtime per INSERT is also slightly longer.
If you set up ConnMaxLifetime in your code, be sure to remember how often the connection will expire (and then recreate). For example, if you have a total of 100 connections and ConnMaxLifetime is 1 minute, then your application may kill and recreate 1.67 connections per second (average). You don't want this frequency to be too high and it will eventually hinder performance rather than improve performance.
Exceeded connections
Finally, this article is incomplete without explaining what will happen if the hard limit exceeds the number of database connections. To illustrate this, I will modify the file so that only 5 connections are allowed in total (the default is 100)...
max_connections = 5
Then rerun the benchmark with unlimited connections...
BenchmarkMaxOpenConnsUnlimited-8 --- FAIL: BenchmarkMaxOpenConnsUnlimited-8
main_test.go:14: pq: sorry, too many clients already
main_test.go:14: pq: sorry, too many clients already
main_test.go:14: pq: sorry, too many clients already
FAIL
Once the hard limit of 5 connections is reached, the database driver (pq) immediately returns an error message with too many client connections to fail to complete INSERT. To prevent this error, we need to set the maximum total number of open connections (in use + idle) to be less than 5. Like this:
// Initialize a new connection pooldb, err := ("postgres", "postgres://user:pass@localhost/db")
if err != nil {
(err)
}
// Set the number of open connections (connection in use + idle connections) to the maximum total of 3. (3)
Now, at any time you can only create up to 3 connections and the benchmark should not have any errors when running. But it needs to be noted that when the number of open connections limit is reached and all connections are in use, any new database tasks that the application needs to perform will be forced to wait until the connection is marked idle. For example, in the context of a web application, a user's HTTP request looks like it will "hang" and may even time out while waiting for the database task to run.
To mitigate this, you should always pass in one context. When calling the database, enable context methods (such as ExecContext()) to use fixed, fast timeout context objects.
Summarize
1. According to experience, the MaxOpenConns value should be explicitly set. This should be less than the hard limit on the number of connections the database and infrastructure has.
2. Generally speaking, higher MaxOpenConns and MaxIdleConns values will bring better performance. But you should notice that the effect is decreasing, and that too many idle connections in the connection pool (the connection is not reused and will eventually become bad) will actually lead to performance degradation.
3. In order to reduce the risks posed by point 2 above, you may need to set up a relatively short ConnMaxLifetime. But you also don't want it to be too short, causing the connection to be killed or rebuilt unnecessarily frequently.
4. MaxIdleConns should always be less than or equal to MaxOpenConns.
For small and medium web applications, I usually use the following settings as a starting point and then optimize based on load test results at the actual throughput level.
(25) (25) (5*)
This is the end of this article about the use of golang for high performance preparation. For more related golang for high performance preparation, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!