Tuning MySQL wait_timeout and Pool Idle Settings

This guide is part of ProxySQL & MySQL Connection Multiplexing. Idle connections are closed by more components than most teams realise: MySQL’s wait_timeout, a proxy’s idle settings, a load balancer, a NAT gateway, and the pool’s own reaper. When the pool is not the first to act, it keeps a socket that the other end has already closed, and the next borrow hands that socket to a request:

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
  The last packet successfully received from the server was 28,801,003 milliseconds ago.
  The last packet sent successfully to the server was 0 milliseconds ago.

Error: Connection lost: The server closed the connection.   # node mysql2
pymysql.err.OperationalError: (2006, "MySQL server has gone away")

The number in the Java message is the giveaway: 28,801 seconds is MySQL’s default wait_timeout of eight hours, to the millisecond. The objective is an ordering where the pool always retires a connection before anything else closes it, with validation as a backstop rather than the primary mechanism.

Rapid incident diagnosis

Identify who closed the connection. The elapsed time in the error, compared with the timeouts in the path, names the culprit:

SHOW GLOBAL VARIABLES WHERE Variable_name IN ('wait_timeout','interactive_timeout','net_read_timeout','net_write_timeout');

-- ProxySQL, if present
SELECT variable_name, variable_value FROM runtime_global_variables
WHERE variable_name IN ('mysql-wait_timeout','mysql-connection_max_age_ms','mysql-ping_interval_server_msec','mysql-server_idle_timeout');

An error after roughly eight hours points at MySQL’s default; after 350 seconds, at an AWS NAT gateway; after 60 or 300 seconds, at a load balancer or the proxy.

Check the aborted-client counters. MySQL counts connections that vanished without a proper close:

SHOW GLOBAL STATUS WHERE Variable_name IN ('Aborted_clients','Aborted_connects','Connections','Threads_connected');

Aborted_clients rising steadily during quiet hours is the shape of idle reaping; Aborted_connects rising instead points at authentication or network failures during connect.

Confirm the pool’s own settings. Each driver names them differently, which is why the ordering is so often wrong:

Look for the pattern in time. Errors clustered at the start of the working day, after a quiet night, are almost always idle-timeout related. Errors spread evenly through the day are more likely network resets or a server-side kill.

Mathematical sizing or parameter formula

The rule is an ordering, not a single value:

pool_idle_timeout + reaper_interval < min(nat_idle, lb_idle, proxy_idle, wait_timeout)
pool_max_lifetime                   < min(proxy_max_age, wait_timeout)
keepalive_interval                  < min(nat_idle, lb_idle) ÷ 2

Worked example. A service runs behind a NAT gateway (350 s), through ProxySQL (mysql-server_idle_timeout 600 s) to MySQL (wait_timeout 28,800 s). The smallest is 350 seconds, so:

idleTimeout      = 120_000 ms     (2 minutes, plus reaper interval, well inside 350 s)
maxLifetime      = 1_500_000 ms   (25 minutes: recycles connections for rebalancing)
keepaliveTime    = 120_000 ms     (half of 350 s, minus margin)

Lowering wait_timeout deliberately. The eight-hour default is far longer than any pool should hold an idle connection, and it lets abandoned sessions from crashed clients accumulate. Setting it to something like 600 seconds bounds that, provided every pool’s idle timeout is already lower:

wait_timeout = max(pool_idle_timeouts) × 2 + margin

With pools closing idle connections after 120 seconds, wait_timeout = 600 is comfortable. Remember that interactive_timeout governs clients that set the interactive flag — the mysql CLI, most GUI tools — and should stay longer so an operator’s session is not dropped mid-investigation.

The cost of churn. A pool idle timeout that is very short trades dead connections for reconnections:

reconnects_per_second ≈ idle_connections × (1 ÷ idle_timeout_seconds)

Twenty idle connections with a 30-second idle timeout is 0.67 reconnects per second per process — noticeable on a fleet, since each one is a TCP handshake, a TLS handshake and an authentication round trip. Two minutes is usually the sweet spot: far inside typical network timeouts, far above the churn threshold.

Exact remediation & configuration

HikariCP with Connector/J:

spring:
  datasource:
    url: jdbc:mysql://proxysql.internal:6033/orders?connectTimeout=5000&socketTimeout=30000
    hikari:
      minimum-idle: 4
      maximum-pool-size: 16
      idle-timeout: 120000         # 2 minutes
      keepalive-time: 120000       # keeps minimum-idle connections alive
      max-lifetime: 1500000        # 25 minutes
      connection-timeout: 3000
      connection-test-query: ""    # leave unset: JDBC4 isValid() is used

mysql2 in Node.js, which has no maximum lifetime and therefore relies on idle handling and keepalives:

export const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: "orders_app",
  database: "orders",
  connectionLimit: 10,
  maxIdle: 4,
  idleTimeout: 120_000,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30_000,
  connectTimeout: 5_000,
});

SQLAlchemy, where pool_recycle is an age limit rather than an idle limit, so it must be below the shortest timeout on the path:

engine = create_engine(
    "mysql+pymysql://orders_app@proxysql.internal:6033/orders",
    pool_size=8, max_overflow=4,
    pool_recycle=300,        # below the 350 s NAT idle timeout
    pool_pre_ping=True,      # backstop for anything that still slips through
)

ProxySQL’s own backend connections, when a proxy is in the path:

UPDATE global_variables SET variable_value='300000'  WHERE variable_name='mysql-connection_max_age_ms';
UPDATE global_variables SET variable_value='10000'   WHERE variable_name='mysql-ping_interval_server_msec';
UPDATE global_variables SET variable_value='600'     WHERE variable_name='mysql-server_idle_timeout';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

MySQL, once every pool is correct:

SET PERSIST wait_timeout = 600;
SET PERSIST interactive_timeout = 28800;   -- leave human sessions alone

Apply the server change last. Lowering wait_timeout before the pools are fixed converts a rare eight-hour problem into a frequent ten-minute one.

Validation & verification

No connection outlives the pool’s limits. On MySQL, the oldest session from the service should be younger than maxLifetime, and idle sessions should disappear after the idle timeout:

SELECT USER, COMMAND, MAX(TIME) AS longest_seconds, COUNT(*) AS sessions
FROM information_schema.PROCESSLIST
WHERE USER = 'orders_app'
GROUP BY USER, COMMAND;

With a 25-minute maxLifetime, longest_seconds for Sleep rows should stay under about 130 seconds and for all rows under about 1,500.

Idle survival test. Leave a staging service idle for longer than every timeout in the path — an hour is enough — then send one request. It must succeed on the first attempt, with no CommunicationsException in the logs. Repeat with the keepalive disabled to confirm the test actually exercises the mechanism.

Churn stays low. Count new connections over a quiet period; the rate should match the formula rather than exceed it:

SHOW GLOBAL STATUS LIKE 'Connections';   -- sample twice, a minute apart

Aborted clients stop growing. After the change, Aborted_clients should be flat during idle periods. Continued growth means some component in the path still closes first — often a load balancer nobody accounted for.

Keeping the ordering correct as infrastructure changes

The ordering breaks silently whenever something in the path changes, and nothing in the application will look different until the first quiet period.

Write the path down. A short table in the service’s runbook, listing every component between the pool and MySQL with its idle timeout and where that value is configured, turns a debugging session into a lookup. It should be revisited whenever the service moves network, gains a proxy or changes cloud account.

Re-check after any infrastructure change. Moving behind a service mesh, adding ProxySQL, switching from a public endpoint to a private one through a NAT gateway — each introduces a new timeout that may be shorter than the pool’s.

Alert on the symptom, not just the cause. Communications failures are rare enough that any occurrence is worth a warning:

- alert: MySQLCommunicationsFailures
  expr: increase(app_db_errors_total{code=~"08S01|2006|PROTOCOL_CONNECTION_LOST"}[15m]) > 0
  labels: { severity: warning }
  annotations:
    summary: "{{ $labels.service }}: connections closed by the other end — check idle timeout ordering"

Prefer retirement to validation. Validation on borrow — pool_pre_ping, testOnBorrow, an explicit SELECT 1 — costs a round trip on the request path and only hides the problem. Correct ordering costs nothing at all. Keep validation enabled as a backstop, but treat any validation failure as a signal that the ordering is wrong somewhere.

Frequently Asked Questions

Related

• ProxySQL & MySQL Connection Multiplexing — the parent guide, including the proxy’s own backend settings.
• Configuring mysql2 Connection Pools in Express — the Node.js pool settings used above.
• Tuning HikariCP maxLifetime and idleTimeout — the same ordering problem on the JVM.
• Configuring PostgreSQL Session Timeouts Without Breaking Connection Pools — the PostgreSQL equivalent.