Our backend API drops connections under heavy user spikes. When researching , I found conflicting advice regarding wait_timeout and max_connections settings. What are the safe industrial thresholds to prevent 'Too many connections' errors without crashing server resources?
3 answers
The 'Too many connections' error happens because applications open connections and don't close them, or your timeout threshold is too high, keeping dead connections alive. Reduce wait_timeout and interactive_timeout from the default 28800 seconds down to around 300 or 600 seconds so idle connections close quickly. Simultaneously, calculate your max connections based on available memory: (Available RAM - Buffer Pool Size) / Thread Stack Size. Ensure your application-level connection pool max size is strictly capped below this database limit.
Have you checked if your application code is leaking connections by forgetting to close database sessions in the exception handling catch blocks?
Use a proxy layer like ProxySQL between your app and MySQL. This handles connection routing and pooling efficiently without overloading the core database instance.
ProxySQL is highly effective. It adds a smart layer that decouples application connections from database threads, keeping production response times highly stable under pressure.
Vincent, that's exactly what happened to us. A missing finally block in our API was leaving unclosed connections hanging every time an error occurred. Fixing the code structure was far more effective than just changing the database configuration limits.