Understanding and Optimizing Database Connection Pools with Go and MySQL

Recently I was working on a small project and discovered severe service interface timeouts caused by improper use of a connection pool. This article walks through the problem, explains the concept of connection pools, and provides a practical demonstration.

1. Database Connection Pool

Using MySQL as an example, the article describes how MySQL connections are based on TCP, which requires a three‑way handshake and a four‑way termination, adding latency. To reduce this overhead, connections can be reused by keeping them alive in a pool.

Reusing connections means that once a TCP connection is established, it is stored in the pool and later requests can borrow the same connection instead of creating a new one.

2. Actual Code Verification

First we create a local MySQL instance and use the Go database/sql package with its built‑in connection pool. The default parameters are overridden to set a maximum of 5 open connections and 3 idle connections.

DB.SetMaxOpenConns(5)  // maximum open connections
DB.SetMaxIdleConns(3)  // maximum idle connections (pool size)

The following Go program launches ten goroutines that each query SELECT CONNECTION_ID() and print the connection ID.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

var DB *sql.DB
var dataBase = "root:pw123456@tcp(127.0.0.1:3306)/"

func init() {
    var err error
    DB, err = sql.Open("mysql", dataBase)
    if err != nil {
        fmt.Println("open db error:", err)
        panic("open db error xxx")
    }
    DB.SetMaxOpenConns(5)
    DB.SetMaxIdleConns(3)
    if err = DB.Ping(); err != nil {
        fmt.Println("ping db error:", err)
        panic("ping db error xxx")
    }
}

func worker(i int) {
    var connection_id int
    err := DB.QueryRow("select CONNECTION_ID()").Scan(&connection_id)
    if err != nil {
        fmt.Println("query connection id error:", err)
        return
    }
    fmt.Println("worker:", i, ", connection id:", connection_id)
}

func main() {
    for i := 0; i < 10; i++ {
        go worker(i)
    }
    for {}
}

Running the program produces output similar to:

worker: 0 , connection id: 67
worker: 9 , connection id: 67
worker: 1 , connection id: 67
worker: 7 , connection id: 67
worker: 8 , connection id: 67
worker: 2 , connection id: 68
worker: 6 , connection id: 69
worker: 5 , connection id: 67
worker: 4 , connection id: 71
worker: 3 , connection id: 70

Checking the system with netstat shows five established connections, matching the pool size.

tcp4 0 0 127.0.0.1:3306 127.0.0.1:62730 ESTABLISHED
tcp4 0 0 127.0.0.1:3306 127.0.0.1:62728 ESTABLISHED
tcp4 0 0 127.0.0.1:3306 127.0.0.1:62726 ESTABLISHED

When a time.Sleep(1 * time.Second) is added inside each goroutine, the output changes to a single connection ID for all workers, demonstrating that the pool reuses the same connection after the previous request finishes.

worker: 0 , connection id: 72
worker: 1 , connection id: 72
worker: 2 , connection id: 72
worker: 3 , connection id: 72
worker: 4 , connection id: 72
worker: 5 , connection id: 72
worker: 6 , connection id: 72
worker: 7 , connection id: 72
worker: 8 , connection id: 72
worker: 9 , connection id: 72

Corresponding netstat output shows only one established connection.

tcp4 0 0 127.0.0.1:3306 127.0.0.1:62835 ESTABLISHED

3. Optimization Results

Increasing the pool size in a real project reduced service latency dramatically and made response times more stable. The performance chart below illustrates the improvement.

Connection pooling is a crucial technique for boosting service performance and also a good way to sharpen practical engineering skills. Readers are encouraged to implement their own pool and run similar tests to deepen their understanding.