MySQL Connection Pooling

-- See how many connections are currently active
SHOW STATUS LIKE 'Threads_connected';

-- See the server's connection limit
SHOW VARIABLES LIKE 'max_connections';

-- See peak connections since last restart
SHOW STATUS LIKE 'Max_used_connections';

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'app',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10,       // max simultaneous connections
  queueLimit: 0              // unlimited queue (0 = no limit)
});

// connection is returned to the pool automatically
const [rows] = await pool.execute('SELECT * FROM orders WHERE id = ?', [42]);

from sqlalchemy import create_engine

engine = create_engine(
    'mysql+mysqlconnector://user:pass@localhost/mydb',
    pool_size=10,        # persistent connections to keep open
    max_overflow=5,      # extra connections allowed under peak load
    pool_timeout=30,     # seconds to wait for a connection from the pool
    pool_recycle=3600    # recycle connections after 1 hour (avoids stale connections)
)

-- Check the server's idle connection timeout
SHOW VARIABLES LIKE 'wait_timeout';

-- In ProxySQL admin interface
-- Route SELECT queries to read hostgroup (replicas)
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup)
VALUES (1, 1, '^SELECT', 2);

-- All other queries go to write hostgroup (primary)
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup)
VALUES (2, 1, '.*', 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- Increase max connections (requires SUPER privilege)
SET GLOBAL max_connections = 500;