The Real Problem: Go Microservice MySQL Connections Running Slow and Timing Out
My microservices system ran smoothly until traffic spiked unexpectedly. Every request was opening a new connection to MySQL, causing latency to shoot up and triggering too many connections errors. My production database runs MySQL 8.0 with around 50GB of data — and it wasn’t until I optimized the connection pool that P99 latency dropped from 800ms down to ~120ms at peak load.
It took me nearly half a day of log tracing to find the root cause. Neither Go nor MySQL was at fault — the issue was purely that the code was opening a new connection for every single request.
Root Cause Analysis
1. Each Request Opens a New Connection
The simplest Go code to connect to MySQL looks like this:
db, err := sql.Open("mysql", dsn)
defer db.Close()
// Execute query then closeCalling sql.Open() inside each handler function means every request creates a new TCP connection to MySQL. 100 concurrent requests = 100 new connections all at once — while MySQL’s default only allows 151 total connections.
2. No Pool Size Configuration
Using the singleton pattern is the right idea, but forgetting to set limits means Go will happily open connections on demand with no ceiling. The consequences:
- MySQL gets overloaded when traffic spikes quickly
- High connection overhead — each TCP handshake + MySQL auth costs roughly 5–10ms
- MySQL-side memory grows out of control
3. Raw database/sql Is Too Verbose
With plain database/sql, scanning query results into a struct is painfully boilerplate:
rows, err := db.Query("SELECT id, name, email FROM users WHERE id = ?", id)
var user User
if rows.Next() {
err = rows.Scan(&user.ID, &user.Name, &user.Email)
}A struct with 10–15 columns means listing every field in Scan(). Easy to miss one, and a nightmare to maintain when the schema changes.
Solutions
Option 1: Use database/sql Correctly (Singleton Pattern)
Install the MySQL driver for Go:
go get github.com/go-sql-driver/mysqlCreate *sql.DB once and reuse it for the entire application lifetime:
package database
import (
"database/sql"
"fmt"
"time"
_ "github.com/go-sql-driver/mysql"
)
var DB *sql.DB
func Init(dsn string) error {
var err error
DB, err = sql.Open("mysql", dsn)
if err != nil {
return fmt.Errorf("open mysql: %w", err)
}
DB.SetMaxOpenConns(25)
DB.SetMaxIdleConns(10)
DB.SetConnMaxLifetime(5 * time.Minute)
return DB.Ping()
}In main.go, call this once and pass database.DB into your handlers:
dsn := "user:password@tcp(localhost:3306)/mydb?parseTime=true&charset=utf8mb4"
if err := database.Init(dsn); err != nil {
log.Fatal(err)
}Connection leaks are no longer a problem — but query code is still verbose with multi-field structs.
Option 2: Use SQLX — database/sql but More Practical
SQLX is a thin wrapper around database/sql. The entire interface stays the same, but you gain StructScan, NamedQuery, and Select — conveniences that database/sql deliberately omits to keep the standard library lean.
go get github.com/jmoiron/sqlxSwap *sql.DB for *sqlx.DB:
package database
import (
"fmt"
"time"
"github.com/jmoiron/sqlx"
_ "github.com/go-sql-driver/mysql"
)
var DB *sqlx.DB
func Init(dsn string) error {
var err error
DB, err = sqlx.Connect("mysql", dsn)
if err != nil {
return fmt.Errorf("connect mysql: %w", err)
}
DB.SetMaxOpenConns(25)
DB.SetMaxIdleConns(10)
DB.SetConnMaxLifetime(5 * time.Minute)
DB.SetConnMaxIdleTime(2 * time.Minute)
return nil
}Querying with SQLX is much cleaner — scan directly into a struct using db tags:
type User struct {
ID int `db:"id"`
Name string `db:"name"`
Email string `db:"email"`
CreatedAt time.Time `db:"created_at"`
}
// Fetch a single record
var user User
err := database.DB.Get(&user, "SELECT * FROM users WHERE id = ?", userID)
// Fetch multiple records
var users []User
err = database.DB.Select(&users, "SELECT * FROM users WHERE active = 1 LIMIT 100")NamedExec lets you use field names instead of positional placeholders. An INSERT with 8–10 columns stays perfectly readable:
user := User{Name: "Nguyen Van A", Email: "[email protected]"}
_, err := database.DB.NamedExec(`
INSERT INTO users (name, email) VALUES (:name, :email)
`, user)Option 3: Transactions Done Right with SQLX
Microservices often need transactions when operating on multiple tables at once. The standard pattern uses defer tx.Rollback():
func TransferBalance(fromID, toID int, amount float64) error {
tx, err := database.DB.Beginx()
if err != nil {
return err
}
defer tx.Rollback() // no-op if Commit already succeeded
if _, err = tx.Exec(
"UPDATE accounts SET balance = balance - ? WHERE id = ?",
amount, fromID,
); err != nil {
return err
}
if _, err = tx.Exec(
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
amount, toID,
); err != nil {
return err
}
return tx.Commit()
}defer tx.Rollback() acts as a safety net: if the function returns an error before Commit(), the transaction rolls back automatically. Once Commit() succeeds, calling Rollback() is a no-op — it has no effect.
Optimizing the Connection Pool for Production
Understanding the 4 Pool Parameters
Go’s connection pool has 4 parameters that need to be configured correctly. Leaving them at their defaults means letting Go decide for you — and Go will choose “unlimited”:
DB.SetMaxOpenConns(n) // Maximum total connections (idle + active)
DB.SetMaxIdleConns(n) // Connections held in pool when idle
DB.SetConnMaxLifetime(d) // Maximum lifetime of a single connection
DB.SetConnMaxIdleTime(d) // Maximum time a connection can remain idlePool Size Formula for Microservices
After running this in production and watching the metrics, I’ve arrived at this simple formula:
- MaxOpenConns = number of goroutines handling concurrent requests × 1.5 (for buffer)
- MaxIdleConns = 40% of MaxOpenConns (enough for reuse, without waste)
- ConnMaxLifetime = 5–10 minutes (prevents stale connections after MySQL restart/failover)
- ConnMaxIdleTime = 2–3 minutes (releases unused connections, reduces MySQL load)
For a service handling 50 concurrent requests, here’s the config I’m running in production:
func InitProductionDB(dsn string) (*sqlx.DB, error) {
fullDSN := dsn + "?parseTime=true&charset=utf8mb4&collation=utf8mb4_unicode_ci"
db, err := sqlx.Connect("mysql", fullDSN)
if err != nil {
return nil, err
}
db.SetMaxOpenConns(75) // 50 × 1.5
db.SetMaxIdleConns(30) // 40% of 75
db.SetConnMaxLifetime(8 * time.Minute)
db.SetConnMaxIdleTime(3 * time.Minute)
return db, nil
}Monitoring Pool Health with db.Stats()
Go provides db.Stats() to inspect pool state in real time — no APM required, no external tooling:
func LogDBStats(db *sqlx.DB) {
s := db.Stats()
log.Printf(
"DB Pool — open: %d, idle: %d, in-use: %d, wait: %d, waitDuration: %s",
s.OpenConnections, s.Idle, s.InUse, s.WaitCount, s.WaitDuration,
)
}If WaitCount keeps climbing, MaxOpenConns is too low and requests are queuing for a connection. If Idle is always pinned at MaxIdleConns, you’re holding too many idle connections — each MySQL connection uses roughly 4–8MB of RAM, and that adds up fast across dozens of them.
Context Timeout on Every Query
A single query without a timeout can hold a connection in the pool for 30 seconds, causing downstream requests to queue — and then the services calling yours start timing out in a cascade. Always use GetContext/SelectContext:
func GetUserWithTimeout(ctx context.Context, db *sqlx.DB, id int) (*User, error) {
ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
defer cancel()
var user User
err := db.GetContext(ctx, &user, "SELECT * FROM users WHERE id = ?", id)
if err != nil {
return nil, fmt.Errorf("get user %d: %w", id, err)
}
return &user, nil
}Pre-Deployment Checklist for Production
- Create
*sqlx.DBonce and pass it into handlers (never instantiate it inside a handler) - Configure all 4 pool parameters — do not leave them at defaults
- DSN must include
parseTime=trueandcharset=utf8mb4 - Use
GetContext/SelectContextwith a timeout on every query - Transactions: call
defer tx.Rollback()immediately afterBeginx() - Monitor
db.Stats()periodically, especially during traffic spikes
