Managing golang connection pool [Practical Examples]


Written by - Tuan Nguyen
Reviewed by - Deepak Prasad

In the previous article, I introduced to you the ways to work with SQL in Golang. You don't need to change the defaults for the sql.DB connection pool for the vast majority of projects. However, for more advanced tools, you might have to adjust the connection pool's specifications or actively interact with connections. We will cover this subject in today's section.

type DB struct {
	// contains filtered or unexported fields
}

DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.

The sql package creates and frees connections automatically; it also maintains a free pool of idle connections. If the database has a concept of per-connection state, such state can be reliably observed within a transaction (Tx) or connection (Conn). Once DB.Begin is called, the returned Tx is bound to a single connection. Once Commit or Rollback is called on the transaction, that transaction's connection is returned to DB's idle connection pool. The pool size can be controlled with SetMaxIdleConns.

 

Setting connection pool properties

Open and idle connections

A pool of several database connections known as a sql.DB object includes both "in-use" and "idle" connections. When you are using a connection to work with a database, such as running a SQL statement or querying rows, that connection is indicated as being in use. The connection is tagged as idle when the task is finished.

When given a database task to complete, sql.DB will first see if there are any idle connections currently present in the pool. Go will reuse an existing connection and mark it as in-use for the duration of the task if one is available. When you need a connection and there are none available in the pool, Go will make a new connection.

 

Setting the maximum number of open connections

func (db *DB) SetMaxOpenConns(n int): SetMaxOpenConns sets the maximum number of open connections to the database. If MaxIdleConns is greater than 0 and the new MaxOpenConns is less than MaxIdleConns, then MaxIdleConns will be reduced to match the new MaxOpenConns limit. If n <= 0, then there is no limit on the number of open connections. The default is 0 (unlimited).

The maximum number of concurrently active and idle connections is unbounded by default. However, you can use the SetMaxOpenConns() method to implement version:

func BenchmarkMaxOpenConns5(b *testing.B) {
	var db *sql.DB
	config := mysql.Config{
		User:   "root",
		Passwd: "1234",
		Net:    "tcp",
		Addr:   "127.0.0.1:3306",
		DBName: "students",
	}

	// get a database handle.
	var err error

	db, err = sql.Open("mysql", config.FormatDSN())
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxOpenConns(5)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

Function to add a new student:

func insertRecord(b *testing.B, db *sql.DB) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	_, err := db.ExecContext(ctx, "INSERT INTO student (name, email, age) VALUES ('Daniel', 'daniel@gmail.com', 20)")
	if err != nil {
		b.Fatal(err)
	}
}

The pool's maximum capacity in this sample code is now set at 5 concurrently open connections. The program will be compelled to wait until one of the five connections becomes available and idle if all five connections are already marked as in use and another new connection is required.

Output:

BenchmarkMaxOpenConns10
BenchmarkMaxOpenConns10-12
    7995            217186 ns/op             497 B/op          9 allocs/op
PASS
ok      _/c_/Users/nguye/OneDrive/Desktop/golang/main/test      1.973s

BenchmarkMaxOpenConns1
BenchmarkMaxOpenConns1-12
    2137            753218 ns/op             487 B/op          9 allocs/op
PASS
ok      _/c_/Users/nguye/OneDrive/Desktop/golang/main/test      1.893s

BenchmarkMaxOpenConns5
BenchmarkMaxOpenConns5-12
    4369            258526 ns/op             485 B/op          9 allocs/op
PASS
ok      _/c_/Users/nguye/OneDrive/Desktop/golang/main/test      1.387s

For this benchmark, we can see that the more open connections that are allowed, the less time is taken to perform the INSERT on the database. The more database queries can be run simultaneously the more open connections that are allowed.

 

Setting the maximum number of idle connections

When an SQL operation finishes on a given database connection, it is not typically shut down immediately: the application may need one again soon, and keeping the open connection around avoids having to reconnect to the database for the next operation. Raising the limit can avoid frequent reconnects in programs with significant parallelism. By default sql.DB allows a maximum of 2 idle connections to be retained in the connection pool. You can change this via the SetMaxIdleConns() function:

func BenchmarkMaxIde10(b *testing.B) {
	var db *sql.DB
	config := mysql.Config{
		User:   "root",
		Passwd: "1234",
		Net:    "tcp",
		Addr:   "127.0.0.1:3306",
		DBName: "students",
	}

	// get a database handle.
	var err error

	db, err = sql.Open("mysql", config.FormatDSN())
	if err != nil {
		b.Fatal(err)
	}
	db.SetMaxIdleConns(10)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

Benchmark for max ide connections (and the number of open connections is unlimited):

BenchmarkMaxIde5-12    	    8982	    189748 ns/op	     366 B/op	       7 allocs/op
BenchmarkMaxIde1-12    	    8790	    177398 ns/op	    2318 B/op	      17 allocs/op
BenchmarkMaxIde10-12    	    7705	    276059 ns/op	     365 B/op	       7 allocs/op

 

Setting the maximum lifetime of connections

Now let's examine the SetConnMaxLifetime() method, which determines the longest period of time a connection may be reused. This can be beneficial, for example, if you want to enable graceful database switching when using a load balancer or if your SQL database also provides a maximum connection lifetime:

Theoretically, the shorter the ConnMaxLifetime value, the more frequently connections will expire and, as a result, the more frequently new connections will need to be formed.

To demonstrate this, I performed the benchmarks with the default settings of 2 idle connections and an infinite number of open connections with ConnMaxLifetime set to 100ms, 500ms, and 1000ms. Although these time frames are obviously much, much shorter than those used in the majority of applications, they serve to effectively show the behavior:

func BenchmarkConnMaxLifetime1000(b *testing.B) {
	var db *sql.DB
	config := mysql.Config{
		User:   "root",
		Passwd: "1234",
		Net:    "tcp",
		Addr:   "127.0.0.1:3306",
		DBName: "students",
	}

	// get a database handle.
	var err error

	db, err = sql.Open("mysql", config.FormatDSN())
	if err != nil {
		b.Fatal(err)
	}
	db.SetConnMaxLifetime(1000)
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

Output:

BenchmarkConnMaxLifetime1000-12    	    6643	    305411 ns/op	    7027 B/op	      43 allocs/op
BenchmarkConnMaxLifetime500-12    	    6177	    239359 ns/op	    7033 B/op	      43 allocs/op
BenchmarkConnMaxLifetime100-12    	    5714	    255148 ns/op	    7031 B/op	      43 allocs/op

 

Exceeding connection limits

What happens if you use more database connections than are allowed by a strict limit? 'll modify SQL config file as an example to allow only 5 connections:

SET GLOBAL max_connections = 5;

Benchmark code for exceeding the connection limits:

func BenchmarkMaxOpenConnsUnlimited(b *testing.B) {
	var db *sql.DB
	config := mysql.Config{
		User:   "root",
		Passwd: "1234",
		Net:    "tcp",
		Addr:   "127.0.0.1:3306",
		DBName: "students",
	}

	// get a database handle.
	var err error

	db, err = sql.Open("mysql", config.FormatDSN())
	if err != nil {
		b.Fatal(err)
	}
	defer db.Close()

	b.RunParallel(func(pb *testing.PB) {
		for pb.Next() {
			insertRecord(b, db)
		}
	})
}

Output:

Managing golang connection pool [Practical Examples]

To prevent this error we need to set the total maximum of open connections (in-use + idle) in sql.DB to comfortably below 5 like the below example.

 

Using dedicated connections

The database/sql package includes functions you can use when a database may assign implicit meaning to a sequence of operations executed on a particular connection.

The most common example is transactions, which typically start with a BEGIN command, end with a COMMIT or ROLLBACK command, and include all the commands issued on the connection between those commands in the overall transaction. For this use case, use the sql package’s transaction support. See Executing transactions .

For other use cases where a sequence of individual operations must all execute on the same connection, the sql package provides dedicated connections. DB.Conn obtains a dedicated connection, an sql.Conn. The sql.Conn has methods BeginTx, ExecContext, PingContext, PrepareContext, QueryContext, and QueryRowContext that behave like the equivalent methods on DB but only use the dedicated connection. When finished with the dedicated connection, your code must release it using Conn.Close.

 

Summary

When working with SQL in Go, you must consciously choose a MaxOpenConns value. This should comfortably fall below any strict limitations on the number of connections set by your infrastructure and database. MaxOpenConns and MaxIdleConns values that are higher will typically result in better performance. However, the benefits are declining, and you should be mindful that a sizable idle connection pool (containing connections that are not reused and eventually fail) might actually result in decreased performance. You may want to set a relatively short ConnMaxLifetime to avoid the mentioned problems. But you don't want this to be so short that leads to connections being killed and recreated unnecessarily often.

 

References

Managing connections
https://pkg.go.dev/database/

 

Views: 23

Tuan Nguyen

He is proficient in Golang, Python, Java, MongoDB, Selenium, Spring Boot, Kubernetes, Scrapy, API development, Docker, Data Scraping, PrimeFaces, Linux, Data Structures, and Data Mining. With expertise spanning these technologies, he develops robust solutions and implements efficient data processing and management strategies across various projects and platforms. You can connect with him on LinkedIn.

Categories GO

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment