Why Do Stale MySQL Sleep Connections Appear After HAProxy Timeout Mismatch?

Background

The database reported max_connection errors, preventing clients from connecting. One application had historically increased the max_connections setting to cope with a legacy issue. Meanwhile, another service observed many long‑running sleep connections even after all its processes were restarted.

Scenario

After a restart, roughly 10 seconds of new connections were normal, but about 3,000 connections remained abnormal. No reproducible test environment existed.

Initial Analysis

The first hypothesis was that leftover processes on the application server kept connections open. After confirming no processes were accessing the database, the investigation moved to the network layer.

Network Investigation

All application processes were stopped, yet MySQL still showed several thousand‑second sleep connections. TCP ESTABLISHED connections were visible on the DB server, indicating they persisted.

Checking the HAProxy front‑end revealed matching ports and connections. Reverse‑looking up HAProxy connection details identified the source IP and port of the application servers (see screenshot).

Root Cause Explanation

The client connects to the database via LVS, which forwards traffic to HAProxy. LVS’s timeout is shorter than HAProxy’s. When the client remains idle, LVS forces the client to re‑establish a new connection to HAProxy, but the old HAProxy connection remains open because its timeout has not yet expired. This creates “orphaned” sleep connections that have no visible source on the application server.

Resolution

The HAProxy instance’s timeout was increased to be slightly higher than LVS’s timeout. After this change, the orphaned sleep connections disappeared within days, and the max_connection errors ceased.

MySQL error logs later showed NOTE entries indicating that HAProxy had closed some connections, confirming the timeout adjustment worked.

Lessons Learned

Even when application code correctly closes database connections, mismatched timeout settings between load‑balancing layers can generate persistent sleep connections that exhaust the connection pool. Adjusting timeout hierarchies provides a defensive layer, while developers should still ensure proper DB connection handling.