Name: EVE AI Core
Author: Jamaurice Holt

MySQL Connection Pool Configuration

Connection pooling is essential for building scalable database applications. Instead of creating a new connection for every request, connection pools maintain a set of reusable connections, dramatically reducing overhead and improving response times.

🎯 Why Connection Pooling Matters

Without Pooling	With Pooling
New connection per request	Reuse existing connections
TCP handshake + authentication overhead	Near-zero connection overhead
~50-100ms per connection	~1ms per connection acquisition
Server overload with connections	Controlled connection count
Resource exhaustion under load	Stable performance under load

💡 Key Insight: A single MySQL connection establishment involves TCP handshake, SSL negotiation (if enabled), authentication, and session setup - all of which are eliminated with pooling.

🔧 MySQL Server Connection Settings

Essential Configuration

# /etc/mysql/mysql.conf.d/connections.cnf

[mysqld]
# Maximum simultaneous connections
max_connections = 500

# Reserved connections for SUPER users
max_user_connections = 0  # 0 = unlimited per user (use account limits instead)

# Connection timeout settings
wait_timeout = 28800           # Idle connection timeout (8 hours default)
interactive_timeout = 28800    # Interactive client timeout
connect_timeout = 10           # Connection establishment timeout

# Thread handling
thread_cache_size = 50         # Cache threads for reuse
thread_stack = 256K            # Stack size per thread

# Connection handling (MySQL 8.0+)
# thread_handling = pool-of-threads  # Enterprise feature

Calculating max_connections

-- Rule of thumb formula:
-- max_connections = (Available RAM - OS/Buffer Pool) / per_connection_memory

-- Check current settings
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE '%timeout%';
SHOW VARIABLES LIKE 'thread%';

-- Check memory per connection
SHOW VARIABLES LIKE 'sort_buffer_size';       -- Per-connection sort buffer
SHOW VARIABLES LIKE 'join_buffer_size';       -- Per-connection join buffer
SHOW VARIABLES LIKE 'read_buffer_size';       -- Per-connection read buffer
SHOW VARIABLES LIKE 'read_rnd_buffer_size';   -- Per-connection random read buffer

-- Estimate per-connection memory (conservative)
-- ~10-20MB per active connection with default settings
-- = (sort_buffer + join_buffer + read_buffer + read_rnd_buffer + thread_stack + net_buffer) * active_queries

Dynamic Connection Limit Adjustment

-- Increase max_connections at runtime (temporary until restart)
SET GLOBAL max_connections = 1000;

-- Check current connection usage
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Aborted_connects';

-- Calculate connection headroom
SELECT
    @@max_connections AS max_conn,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Threads_connected') AS current_conn,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Max_used_connections') AS peak_conn,
    ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'Threads_connected') / @@max_connections * 100, 2) AS usage_pct;

📦 Application-Side Connection Pooling

Java HikariCP (Recommended)

// HikariCP Configuration
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("app_user");
config.setPassword("password");

// Pool sizing
config.setMinimumIdle(10);           // Minimum idle connections
config.setMaximumPoolSize(50);       // Maximum pool size
config.setIdleTimeout(300000);       // 5 minutes idle timeout
config.setMaxLifetime(1800000);      // 30 minutes max connection lifetime
config.setConnectionTimeout(30000);  // 30 seconds connection timeout

// Connection validation
config.setConnectionTestQuery("SELECT 1");
config.setValidationTimeout(5000);   // 5 seconds validation timeout

// MySQL-specific optimizations
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("useServerPrepStmts", "true");
config.addDataSourceProperty("rewriteBatchedStatements", "true");

HikariDataSource ds = new HikariDataSource(config);

Python SQLAlchemy

from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

# Create engine with connection pool
engine = create_engine(
    'mysql+pymysql://user:password@localhost/mydb',
    poolclass=QueuePool,
    pool_size=10,           # Number of connections to keep
    max_overflow=20,        # Extra connections when pool exhausted
    pool_timeout=30,        # Seconds to wait for available connection
    pool_recycle=1800,      # Recycle connections after 30 minutes
    pool_pre_ping=True,     # Test connections before use
    echo_pool='debug'       # Log pool activity (development only)
)

# Monitor pool status
from sqlalchemy import event

@event.listens_for(engine, "checkout")
def receive_checkout(dbapi_connection, connection_record, connection_proxy):
    print(f"Connection checked out: {connection_record}")

@event.listens_for(engine, "checkin")
def receive_checkin(dbapi_connection, connection_record):
    print(f"Connection returned: {connection_record}")

Node.js mysql2/promise

const mysql = require('mysql2/promise');

// Create connection pool
const pool = mysql.createPool({
    host: 'localhost',
    user: 'app_user',
    password: 'password',
    database: 'mydb',

    // Pool configuration
    waitForConnections: true,
    connectionLimit: 50,        // Maximum connections
    queueLimit: 0,              // Unlimited queue
    enableKeepAlive: true,
    keepAliveInitialDelay: 30000,

    // Connection options
    connectTimeout: 10000,      // 10 seconds
    acquireTimeout: 60000,      // 60 seconds to acquire
    timeout: 60000,             // Query timeout

    // MySQL settings
    charset: 'utf8mb4',
    timezone: 'Z',
    supportBigNumbers: true,
    bigNumberStrings: true
});

// Use pool
async function query(sql, params) {
    const connection = await pool.getConnection();
    try {
        const [results] = await connection.query(sql, params);
        return results;
    } finally {
        connection.release();
    }
}

// Monitor pool
pool.on('acquire', (connection) => {
    console.log('Connection %d acquired', connection.threadId);
});

pool.on('release', (connection) => {
    console.log('Connection %d released', connection.threadId);
});

pool.on('enqueue', () => {
    console.log('Waiting for available connection slot');
});

PHP PDO with Persistent Connections

<?php
// PHP PDO persistent connections
$dsn = 'mysql:host=localhost;dbname=mydb;charset=utf8mb4';
$options = [
    PDO::ATTR_PERSISTENT => true,  // Enable connection pooling
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::MYSQL_ATTR_FOUND_ROWS => true,
];

try {
    $pdo = new PDO($dsn, 'user', 'password', $options);
} catch (PDOException $e) {
    throw new Exception('Connection failed: ' . $e->getMessage());
}

// For production, use a connection manager class
class ConnectionPool {
    private static $instances = [];

    public static function getConnection(string $name = 'default'): PDO {
        if (!isset(self::$instances[$name])) {
            self::$instances[$name] = self::createConnection();
        }
        return self::$instances[$name];
    }

    private static function createConnection(): PDO {
        // Connection creation logic
    }
}

📊 Pool Sizing Guidelines

Optimal Pool Size Formula

Optimal Pool Size = ((core_count * 2) + effective_spindle_count)

For SSD:
  Pool Size = (CPU cores * 2) + 1

For HDD:
  Pool Size = (CPU cores * 2) + spindle_count

Example (8-core server with SSD):
  Pool Size = (8 * 2) + 1 = 17 connections per application instance

Sizing by Application Type

Application Type	Pool Size	Rationale
Web API (low latency)	10-20	Short queries, high concurrency
Batch Processing	5-10	Long transactions, fewer concurrent
Background Jobs	2-5	Periodic tasks, low concurrency
Real-time Analytics	20-50	Complex queries, read-heavy
Microservices	5-10 per service	Distributed load

Multi-Instance Calculation

-- Calculate total connections needed
-- Formula: (pool_size * instances) + admin_buffer + replication

-- Example:
-- 4 app servers * 50 connections = 200
-- Admin/monitoring = 10
-- Replication = 5 (if applicable)
-- Buffer (20%) = 43
-- Total max_connections = 258 (round to 300)

-- Verify current distribution
SELECT
    USER,
    HOST,
    COUNT(*) AS connection_count
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY connection_count DESC;

🔍 Monitoring and Troubleshooting

Connection Monitoring Queries

-- Current connection status
SELECT
    'Current Connections' AS metric,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Threads_connected') AS value
UNION ALL
SELECT
    'Peak Connections',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Max_used_connections')
UNION ALL
SELECT
    'Max Allowed',
    @@max_connections
UNION ALL
SELECT
    'Available',
    @@max_connections - (SELECT VARIABLE_VALUE FROM performance_schema.global_status
                         WHERE VARIABLE_NAME = 'Threads_connected')
UNION ALL
SELECT
    'Usage %',
    ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'Threads_connected') / @@max_connections * 100, 2);

-- Connections by state
SELECT
    COMMAND,
    COUNT(*) AS count,
    GROUP_CONCAT(DISTINCT USER) AS users
FROM information_schema.PROCESSLIST
GROUP BY COMMAND
ORDER BY count DESC;

-- Connections by user/host
SELECT
    USER,
    HOST,
    DB,
    COUNT(*) AS connections,
    SUM(CASE WHEN COMMAND = 'Sleep' THEN 1 ELSE 0 END) AS sleeping,
    SUM(CASE WHEN COMMAND != 'Sleep' THEN 1 ELSE 0 END) AS active
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST, DB
ORDER BY connections DESC;

-- Long-running connections
SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME AS seconds,
    STATE,
    LEFT(INFO, 100) AS query
FROM information_schema.PROCESSLIST
WHERE TIME > 300  -- Connections older than 5 minutes
ORDER BY TIME DESC;

Connection Issues and Solutions

-- Issue: "Too many connections" error
-- Check current vs max
SELECT @@max_connections,
       (SELECT VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Threads_connected') AS current;

-- Solution 1: Increase max_connections (temporary)
SET GLOBAL max_connections = 500;

-- Solution 2: Kill idle connections
SELECT CONCAT('KILL ', ID, ';')
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep'
    AND TIME > 3600  -- Idle > 1 hour
    AND USER != 'system user';

-- Solution 3: Reduce application pool size

-- Issue: Connection refused
-- Check for connection errors
SHOW STATUS LIKE 'Aborted_connects';
SHOW STATUS LIKE 'Connection_errors%';

-- Issue: Connections timing out
-- Adjust wait_timeout for your workload
SET GLOBAL wait_timeout = 300;  -- 5 minutes for web apps
SET GLOBAL interactive_timeout = 28800;  -- 8 hours for admin tools

Automated Monitoring Script

-- Create monitoring procedure
DELIMITER //

CREATE PROCEDURE sp_monitor_connections()
BEGIN
    DECLARE v_current INT;
    DECLARE v_max INT;
    DECLARE v_threshold DECIMAL(5,2) DEFAULT 80.00;

    SELECT @@max_connections INTO v_max;

    SELECT VARIABLE_VALUE INTO v_current
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Threads_connected';

    IF (v_current / v_max * 100) > v_threshold THEN
        -- Log alert
        INSERT INTO connection_alerts (
            alert_time,
            current_connections,
            max_connections,
            usage_percent,
            alert_type
        ) VALUES (
            NOW(),
            v_current,
            v_max,
            ROUND(v_current / v_max * 100, 2),
            'HIGH_CONNECTION_USAGE'
        );

        -- Optionally kill old idle connections
        -- Be careful with this in production!
    END IF;

    -- Return current status
    SELECT
        v_current AS current_connections,
        v_max AS max_connections,
        ROUND(v_current / v_max * 100, 2) AS usage_percent,
        CASE
            WHEN (v_current / v_max * 100) > 90 THEN 'CRITICAL'
            WHEN (v_current / v_max * 100) > 80 THEN 'WARNING'
            ELSE 'OK'
        END AS status;
END //

DELIMITER ;

-- Schedule monitoring
CREATE EVENT evt_connection_monitor
    ON SCHEDULE EVERY 1 MINUTE
    ON COMPLETION PRESERVE
DO
    CALL sp_monitor_connections();

🚀 Advanced Configuration

Per-User Connection Limits

-- Limit connections per user
ALTER USER 'app_user'@'%'
    WITH MAX_USER_CONNECTIONS 100;

-- Limit queries per hour
ALTER USER 'reporting_user'@'%'
    WITH MAX_QUERIES_PER_HOUR 10000
         MAX_UPDATES_PER_HOUR 100
         MAX_USER_CONNECTIONS 10;

-- View user resource limits
SELECT
    USER,
    HOST,
    MAX_USER_CONNECTIONS,
    MAX_QUESTIONS,
    MAX_UPDATES,
    MAX_CONNECTIONS
FROM mysql.user
WHERE MAX_USER_CONNECTIONS > 0 OR MAX_QUESTIONS > 0;

Connection Pooling with ProxySQL

-- ProxySQL provides server-side connection pooling
-- Connect to ProxySQL admin interface

-- Configure backend servers
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES (10, '192.168.1.101', 3306, 100);

-- Configure connection multiplexing
UPDATE global_variables
SET variable_value = 'true'
WHERE variable_name = 'mysql-multiplexing';

UPDATE global_variables
SET variable_value = '1000'
WHERE variable_name = 'mysql-max_connections';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

✅ Best Practices Checklist

Server Configuration

[ ] Set max_connections based on available memory
[ ] Configure appropriate timeout values
[ ] Enable thread caching
[ ] Monitor connection usage regularly
[ ] Set per-user connection limits

Application Configuration

[ ] Use connection pooling in all applications
[ ] Size pools based on workload characteristics
[ ] Enable connection validation/health checks
[ ] Configure connection lifetime/recycling
[ ] Handle connection errors gracefully

Operations

[ ] Monitor connection metrics continuously
[ ] Alert on high connection usage
[ ] Review and optimize pool sizes periodically
[ ] Document connection requirements per application
[ ] Test failover with connection pooling

🎓 Conclusion

Proper connection pool configuration is crucial for building scalable MySQL applications. By understanding both server-side settings and application-side pooling, you can optimize resource usage and ensure consistent performance under varying loads.

💡 Pro Tip: Start with conservative pool sizes and increase based on monitoring data. Over-provisioning pools wastes server resources, while under-provisioning causes connection wait timeouts.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.

Need help with MySQL connection pooling? Contact us for expert database consulting.