Optimization of database connection pool configuration

Database Connection Pool Configuration Optimization

In Koa2 applications, the configuration of the database connection pool directly impacts performance. A pool that is too small will cause requests to queue and wait, while one that is too large may exhaust system resources. Reasonable configuration requires adjustments based on specific business scenarios and server hardware conditions.

Core Parameter Analysis

Key configuration parameters include:

• max: Maximum number of connections
• min: Minimum number of maintained connections
• acquire: Connection acquisition timeout (milliseconds)
• idle: Maximum idle time for connections (milliseconds)

const pool = new Pool({
  max: 20,
  min: 5,
  acquire: 30000,
  idle: 10000
})

Connection Count Formula

Recommended formula for maximum connections:

Maximum connections = (Core count * 2) + Effective disk count

For example, a 4-core server with SSD storage:

const cpuCores = require('os').cpus().length
const maxConnections = (cpuCores * 2) + 1  // Yields 9

Connection Leak Detection

Explicitly release connections in Koa2 middleware:

app.use(async (ctx, next) => {
  const conn = await pool.getConnection()
  try {
    await next()
  } finally {
    conn.release() // Must release
  }
})

Add leak monitoring:

setInterval(() => {
  if(pool.numUsed() > maxConnections * 0.8) {
    console.warn('Connection usage exceeds 80%')
  }
}, 5000)

Optimization for Sharding Scenarios

Multi-data source configuration example:

const userPool = new Pool({
  max: 10,
  // ...User database configuration
})

const orderPool = new Pool({
  max: 15,
  // ...Order database configuration  
})

// Select connection pool based on route
app.use(async (ctx, next) => {
  if(ctx.path.startsWith('/api/user')) {
    ctx.db = userPool
  } else {
    ctx.db = orderPool 
  }
  await next()
})

Stress Testing and Tuning

Use autocannon for benchmarking:

npx autocannon -c 100 -d 60 http://localhost:3000/api

Observe metrics to adjust parameters:

1. Gradually increase max value until throughput plateaus
2. Monitor memory usage
3. Track error rate changes

Connection Pool Event Monitoring

Critical event handling:

pool.on('acquire', connection => {
  console.log(`Connection ${connection.threadId} acquired`)
})

pool.on('release', connection => {
  console.log(`Connection ${connection.threadId} released`)
})

pool.on('error', err => {
  console.error('Unexpected pool error', err)
})

Production Environment Practices

Typical e-commerce application configuration:

{
  max: 50,
  min: 10,
  acquire: 10000,
  idle: 60000,
  evict: 10000, // Idle connection check interval
  connectTimeout: 2000 // Connection timeout
}

Recommendations for canary deployment:

1. Modify configuration for a single instance first
2. Observe monitoring data for 15 minutes
3. Roll out full updates

Connection Pool Health Checks

Regularly validate connection health:

setInterval(async () => {
  const conn = await pool.getConnection()
  try {
    await conn.query('SELECT 1')
  } finally {
    conn.release()
  }
}, 300000) // Execute every 5 minutes

Connection Pool Wrapper Pattern

Create a smart connection proxy:

class SmartPool {
  constructor(pool) {
    this.pool = pool
    this.stats = {
      acquires: 0,
      releases: 0
    }
  }

  async getConnection() {
    this.stats.acquires++
    return this.pool.getConnection()
  }

  // Other proxy methods...
}