Table of Contents
MySQL Connection Pool
Connection Pool is a mechanism that:
keeps database connections ready reuses existing connections instead of creating new ones repeatedly
Goals:
improve performance reduce connection overhead prevent too many DB connections protect MySQL from overload
Why Need Connection Pool?
Without connection pool:
Request A → open DB connection → query → close connection Request B → open DB connection → query → close connection
Problems:
opening TCP connections is expensive MySQL authentication takes time high traffic can create thousands of connections
⇒ slow and can overload MySQL
With connection pool:
Request A → get existing connection from pool → query → return connection to pool Request B → reuse same connection
⇒ much faster
Connection Pool Flow
Application Starts ↓ Create connection pool ↓ Request arrives ↓ Get connection from pool ↓ Query MySQL ↓ Return connection to pool ↓ Connection reused by future requests
SetMaxOpenConns
What is it?
Limits the maximum number of database connections the application can open.
Example:
db.SetMaxOpenConns(30)
Meaning:
application can use at most 30 DB connections the 31st request must wait
Example
Suppose:
100 concurrent requests
But:
db.SetMaxOpenConns(30)
⇒ only:
30 requests can query DB simultaneously
Remaining:
70 requests wait for available connections
Why Important?
Without limit:
db.SetMaxOpenConns(0)
⇒ unlimited connections
Traffic spike:
1000 requests → app opens 1000 DB connections
⇒ MySQL may suffer:
overload high memory usage crashes “Too many connections” errors
Real Production Example
8 pods SetMaxOpenConns = 30
Total possible connections:
8 × 30 = 240 connections
MySQL should have:
max_connections >= 300
SetMaxIdleConns
What is it?
Number of idle connections kept in the pool.
Example:
db.SetMaxIdleConns(10)
Meaning:
keep 10 unused connections ready future requests can reuse them immediately
Example
After request finishes:
connection is NOT closed
Instead:
returned to idle pool
Next request:
reuse existing connection
⇒ much faster
Why Important?
If:
db.SetMaxIdleConns(0)
Every request must:
open TCP connection authenticate MySQL query close TCP connection
⇒ slower response time
Recommended Values
| Traffic Level | Recommended |
| Small app | 2–5 |
| Medium app | 5–10 |
| High traffic | 10–20 |
SetConnMaxLifetime
What is it?
Maximum lifetime of a database connection.
Example:
db.SetConnMaxLifetime(5 * time.Minute)
Meaning:
connection lives for at most 5 minutes after that, it is closed and recreated
Why Important?
Long-lived connections may become:
stale broken timed out invalid after DB restart killed by load balancer/network
Without refreshing connections:
broken pipe connection reset EOF invalid connection
errors may appear randomly.
Example
Connection created at 10:00
At:
10:05
pool automatically destroys and recreates it.
Production Sizing
Goal
Choose pool sizes safely for production traffic.
Example
Suppose:
8 backend pods
Each pod:
db.SetMaxOpenConns(30) db.SetMaxIdleConns(10)
Total maximum DB connections:
8 × 30 = 240
MySQL config:
max_connections = 300~500
Extra connections reserved for:
admin tools migrations monitoring cron jobs backups replicas
Recommended Production Config
db.SetMaxOpenConns(30) db.SetMaxIdleConns(10) db.SetConnMaxLifetime(5 * time.Minute)
Summary
| Setting | Purpose |
|---|---|
| SetMaxOpenConns | Limit total DB connections |
| SetMaxIdleConns | Keep reusable idle connections |
| SetConnMaxLifetime | Refresh old connections periodically |
| max_connections | Total MySQL connection limit |