====== 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:

<code> Request A → open DB connection → query → close connection

Request B
→ open DB connection
→ query
→ close connection
</code>

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:

<code> Request A → get existing connection from pool → query → return connection to pool

Request B
→ reuse same connection
</code>

=> much faster

===== Connection Pool Flow =====

<code> Application Starts ↓ Create connection pool ↓ Request arrives ↓ Get connection from pool ↓ Query MySQL ↓ Return connection to pool ↓ Connection reused by future requests </code>

====== SetMaxOpenConns ======

===== What is it? =====

Limits the maximum number of database connections the application can open.

Example:

<code go> db.SetMaxOpenConns(30) </code>

Meaning:

application can use at most 30 DB connections
the 31st request must wait

===== Example =====

Suppose:

<code> 100 concurrent requests </code>

But:

<code go> db.SetMaxOpenConns(30) </code>

=> only:

<code> 30 requests can query DB simultaneously </code>

Remaining:

<code> 70 requests wait for available connections </code>

===== Why Important? =====

Without limit:

<code go> db.SetMaxOpenConns(0) </code>

=> unlimited connections

Traffic spike:

<code> 1000 requests → app opens 1000 DB connections </code>

=> MySQL may suffer:

overload
high memory usage
crashes
"Too many connections" errors

===== Real Production Example =====

<code> 8 pods SetMaxOpenConns = 30 </code>

Total possible connections:

<code> 8 × 30 = 240 connections </code>

MySQL should have:

<code> max_connections >= 300 </code>

====== SetMaxIdleConns ======

===== What is it? =====

Number of idle connections kept in the pool.

Example:

<code go> db.SetMaxIdleConns(10) </code>

Meaning:

keep 10 unused connections ready
future requests can reuse them immediately

===== Example =====

After request finishes:

<code> connection is NOT closed </code>

Instead:

<code> returned to idle pool </code>

Next request:

<code> reuse existing connection </code>

=> much faster

===== Why Important? =====

If:

<code go> db.SetMaxIdleConns(0) </code>

Every request must:

<code> open TCP connection authenticate MySQL query close TCP connection </code>

=> 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:

<code go> db.SetConnMaxLifetime(5 * time.Minute) </code>

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:

<code> broken pipe connection reset EOF invalid connection </code>

errors may appear randomly.

===== Example =====

<code> Connection created at 10:00 </code>

At:

<code> 10:05 </code>

pool automatically destroys and recreates it.

====== Production Sizing ======

===== Goal =====

Choose pool sizes safely for production traffic.

===== Example =====

Suppose:

<code> 8 backend pods </code>

Each pod:

<code go> db.SetMaxOpenConns(30) db.SetMaxIdleConns(10) </code>

Total maximum DB connections:

<code> 8 × 30 = 240 </code>

MySQL config:

<code> max_connections = 300~500 </code>

Extra connections reserved for:

admin tools
migrations
monitoring
cron jobs
backups
replicas

===== Recommended Production Config =====

<code go> db.SetMaxOpenConns(30) db.SetMaxIdleConns(10) db.SetConnMaxLifetime(5 * time.Minute) </code>

====== Summary ======

^ Setting ^ Purpose ^
| SetMaxOpenConns | Limit total DB connections |
| SetMaxIdleConns | Keep reusable idle connections |
| SetConnMaxLifetime | Refresh old connections periodically |
| max_connections | Total MySQL connection limit |