Configuring mysql2 Connection Pools in Express

This guide is part of Express.js Connection Pool Middleware. The mysql2 driver’s pool is small and quick to set up, and three of its behaviours regularly surprise teams moving from node-postgres or from the older mysql package. It has no acquire timeout: a request waiting for a connection waits for as long as it takes. Its execute() method caches a prepared statement on every connection for every distinct SQL string, and MySQL limits prepared statements server-wide. And idle connections are closed by the server’s wait_timeout without the pool noticing unless keepalives and idle limits are set. Together they produce incidents like this one:

Error: Can't create more than max_prepared_stmt_count statements (current value: 16382)
    at Packet.asError (node_modules/mysql2/lib/packets/packet.js:740:17)
  code: 'ER_MAX_PREPARED_STMT_COUNT_REACHED', errno: 1461, sqlState: '42000'
Error: Connection lost: The server closed the connection.
  code: 'PROTOCOL_CONNECTION_LOST', fatal: true

The objective is a pool configuration where waiting is bounded, prepared statements fit the server limit across the whole fleet, and idle connections are retired before MySQL or the network kills them.

Rapid incident diagnosis

Is the pool full, or is MySQL? The pool’s public API exposes little, but its events do. Count acquisitions that had to queue:

let queued = 0;
pool.pool.on("enqueue", () => { queued++; });     // the underlying callback pool emits enqueue
setInterval(() => {
  logger.info({ queued, total: pool.pool._allConnections.length, free: pool.pool._freeConnections.length },
    "mysql2 pool");
  queued = 0;
}, 10_000);

The _allConnections and _freeConnections fields are internal and may change between releases, so use them for diagnosis rather than for alerts. A steady enqueue count with free at zero means requests are waiting for connections.

Check the server side. Sessions from the service, their states and the prepared statement count:

SELECT USER, SUBSTRING_INDEX(HOST, ':', 1) AS client, COMMAND, COUNT(*) AS sessions, MAX(TIME) AS max_seconds
FROM information_schema.PROCESSLIST
WHERE USER = 'orders_app'
GROUP BY USER, client, COMMAND;

SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count';
SHOW GLOBAL VARIABLES LIKE 'max_prepared_stmt_count';

Classify.

• Pool starvation: many enqueue events, all connections busy, MySQL sessions mostly in Query. Either the pool is too small or queries are slow.
• Leaked connections: connections busy in the pool but MySQL shows them in Sleep for a long time. Code called pool.getConnection() and missed release() on an error path.
• Prepared statement exhaustion: Prepared_stmt_count at or near max_prepared_stmt_count. execute() with dynamically built SQL — an IN list whose length varies, string-interpolated identifiers — creates a new statement per variant on every connection.
• Idle disconnects: PROTOCOL_CONNECTION_LOST or ECONNRESET after quiet periods, with wait_timeout on the server or a load balancer idle timeout shorter than the pool’s idle retention.

Mathematical sizing or parameter formula

Pool size. Size connectionLimit from queries in flight per process, the same way as any Node.js pool:

connectionLimit = ceil( requests_per_second × queries_per_request × mean_query_seconds ÷ 0.7 )

A process serving 400 requests per second with 2 queries at 6 ms each has 400 × 2 × 0.006 = 4.8 queries in flight; ceil(4.8 ÷ 0.7) = 7. Ten is a sensible, round limit.

Prepared statements. execute() prepares each distinct SQL text once per connection and keeps it in an LRU cache of up to maxPreparedStatements entries (16,000 by default). MySQL’s max_prepared_stmt_count (16,382 by default) is global across all clients. The worst case the fleet can reach is:

fleet_statements = replicas × connectionLimit × distinct_sql_texts_per_connection

A service with 12 replicas and a pool of 10 has 120 connections. If the code issues 150 distinct statements — plausible for an ORM-free service with a few variable-length IN lists — the fleet can hold 120 × 150 = 18,000 prepared statements, above the server limit before any other service is counted. Cap statements per connection:

maxPreparedStatements ≤ floor( max_prepared_stmt_count × share_for_this_service ÷ (replicas × connectionLimit) )

With a 50 % share: floor(16382 × 0.5 ÷ 120) = 68. Statements beyond 68 per connection are evicted and re-prepared on demand, which costs a round trip but cannot exhaust the server.

Queue bound. Choose queueLimit from how many waiting requests can be served within the HTTP deadline:

queueLimit ≈ connectionLimit × (deadline_seconds ÷ mean_hold_seconds) × safety_factor

With 10 connections, a 2-second deadline, 12 ms mean hold and a safety factor of 0.25: 10 × (2 ÷ 0.012) × 0.25 ≈ 416. Beyond that, a request would time out upstream anyway, so rejecting it immediately is better.

Exact remediation & configuration

A pool module for an Express service, with every limit explicit:

// db.js
import mysql from "mysql2/promise";

export const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: "orders",

  connectionLimit: 10,
  maxIdle: 4,                 // keep at most 4 idle connections
  idleTimeout: 60_000,        // close idle connections after 60 s (below wait_timeout)
  queueLimit: 400,            // reject instead of queuing without bound
  waitForConnections: true,

  enableKeepAlive: true,      // TCP keepalive so NAT and load balancers keep the flow
  keepAliveInitialDelay: 30_000,
  connectTimeout: 5_000,

  maxPreparedStatements: 68,  // per connection; fleet stays under max_prepared_stmt_count
  namedPlaceholders: false,
});

mysql2 has no acquire timeout option, so add one around acquisition. The important detail is that a request that gives up must still release the connection if it arrives later; otherwise the timeout itself leaks connections:

export async function withConnection(work, { timeoutMs = 1_500 } = {}) {
  let timedOut = false;
  const acquiring = pool.getConnection();

  const timer = new Promise((_, reject) =>
    setTimeout(() => { timedOut = true; reject(Object.assign(new Error("pool acquire timeout"), { status: 503 })); }, timeoutMs),
  );

  let conn;
  try {
    conn = await Promise.race([acquiring, timer]);
  } catch (err) {
    // The connection may still be handed over after we gave up: release it when it arrives
    acquiring.then((late) => late.release()).catch(() => {});
    throw err;
  }

  try {
    return await work(conn);
  } finally {
    conn.release();
  }
}

Use pool.query() or pool.execute() directly for single statements — they acquire and release internally — and withConnection only when several statements must share a connection, such as a transaction:

app.post("/orders/:id/pay", async (req, res, next) => {
  try {
    const receipt = await payments.charge(req.params.id);            // outside the transaction
    await withConnection(async (conn) => {
      await conn.beginTransaction();
      try {
        await conn.execute("UPDATE orders SET status = 'paid', receipt = ? WHERE id = ?", [receipt.id, req.params.id]);
        await conn.execute("INSERT INTO ledger (order_id, amount) VALUES (?, ?)", [req.params.id, receipt.amount]);
        await conn.commit();
      } catch (err) {
        await conn.rollback();
        throw err;
      }
    });
    res.status(204).end();
  } catch (err) {
    next(err);
  }
});

For variable-length IN lists, avoid generating a new SQL text per length with execute(). Either use query(), which escapes values client-side and prepares nothing, or pad the list to a few fixed sizes (10, 50, 200) so the number of distinct statements stays small.

On the server, align timeouts so MySQL is never the first to close an idle connection:

SET PERSIST wait_timeout = 600;             -- 10 minutes, well above idleTimeout (60 s)
SET PERSIST max_prepared_stmt_count = 32764; -- only if memory allows and other services need headroom

Validation & verification

Queue bound. In staging, set connectionLimit: 2 and queueLimit: 5, then send 50 concurrent requests to an endpoint that sleeps 2 seconds in SQL. Most must fail fast — Queue limit reached. from the pool or 503 from the acquire timeout — rather than hang:

hey -n 50 -c 50 http://staging-orders:3000/slow | grep -E "\[503\]|\[200\]|Slowest"

No leak from timeouts. After that test, wait for the slow queries to finish and confirm the pool returned to full capacity. Server-side, the session count must drop back to at most connectionLimit per process, and a follow-up request must succeed immediately:

SELECT COUNT(*) FROM information_schema.PROCESSLIST WHERE USER = 'orders_app';

Prepared statement ceiling. Under a load test that exercises every endpoint, sample the server counter; it must plateau well below the limit:

for i in $(seq 1 30); do
  mysql -h orders-db -uops -p"$OPS_PW" -Nse "SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count'"; sleep 10
done | awk '{print $2}' | sort -n | tail -1

Idle survival. Let a staging instance sit idle for longer than the load balancer’s idle timeout, then issue a request. It must succeed on the first attempt with no PROTOCOL_CONNECTION_LOST in the logs, which shows idleTimeout and keepalives retired or preserved connections correctly.

mysql2 behind ProxySQL and on managed MySQL

The pool settings above assume a direct connection to MySQL. Two common deployments change some of them.

Behind ProxySQL. The Express pool’s connections go to ProxySQL, which multiplexes them onto backend connections. Client connections to ProxySQL are cheap, so connectionLimit can stay at the value computed from in-flight queries, and maxIdle can be closer to it to avoid reconnect churn. Two mysql2 behaviours matter:

• execute() prepares statements with the server-side protocol. ProxySQL supports prepared statements and re-prepares them on each backend connection it uses, which costs extra round trips. For high-volume statements with stable SQL this is acceptable; for varied SQL, prefer query().
• Session state set with SET @var or used through LAST_INSERT_ID() in a separate statement pins the ProxySQL session. Read result.insertId from the INSERT result instead of running SELECT LAST_INSERT_ID().

const [result] = await pool.execute("INSERT INTO orders (customer_id, total) VALUES (?, ?)", [customerId, total]);
const orderId = result.insertId;          // no second statement, no pinning

On Amazon RDS and Aurora MySQL. max_connections is derived from instance memory by the parameter group, and wait_timeout defaults to eight hours. Keep idleTimeout well below the NAT gateway or load balancer idle timeout in the network path, which is usually far shorter than the database’s. With IAM authentication, pass an authPlugins handler that fetches a fresh token when a new connection is created, and keep maxIdle modest so the pool does not hold connections whose authentication will need refreshing at once after a long idle period:

import { Signer } from "@aws-sdk/rds-signer";

const signer = new Signer({ hostname: process.env.DB_HOST, port: 3306, username: "orders_app", region: "eu-west-1" });

export const pool = mysql.createPool({
  host: process.env.DB_HOST, user: "orders_app", database: "orders",
  ssl: { rejectUnauthorized: true },
  authPlugins: { mysql_clear_password: () => () => signer.getAuthToken() },
  connectionLimit: 10, maxIdle: 4, idleTimeout: 60_000, queueLimit: 400,
  enableKeepAlive: true, keepAliveInitialDelay: 30_000,
});

Frequently Asked Questions

Related

• Express.js Connection Pool Middleware — the parent guide on request-scoped connections in Express.
• Implementing Graceful Connection Pool Shutdown in Express — calling pool.end() without dropping in-flight queries.
• Diagnosing MySQL “Too many connections” Errors — when the fleet total exceeds the server’s slots.
• Fixing Async Connection Pool Exhaustion in Node.js — back-pressure for unbounded async concurrency.
• Configuring Knex Connection Pools in Express — the tarn pool Knex uses in place of the driver’s.