How to close Two many connection error mysql

Posted on January 16, 2024

DigitalOcean Managed PostgreSQL Database

I’m encountering a “too many connections” error in MySQL, causing the database to disconnect, and sometimes PM2 freezing.

I have tried to solve the issue of increased max_connection size and also upgraded server space. please suggest me any solution.

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Bobby

January 17, 2024

Hey,

It sounds like you’re dealing with a classic case of the “Too many connections” error in MySQL. It’s great that you’ve already tried increasing max_connections and upgrading your server space. These are solid first steps, but if you’re still facing issues, here are some additional strategies you might consider:

Sometimes, the issue isn’t just about the number of connections, but how they’re being used. Ensure your application is efficiently opening and closing database connections. Long-running or idle connections can unnecessarily consume resources. You can check your current open connections using the SHOW PROCESSLIST; SQL statement and it can show you what queries are being executed and consuming your connections.
If you haven’t already, implement connection pooling. This allows a pool of connections to be reused when future requests to the database are required, reducing the overhead of establishing new connections.
Poorly optimized queries can lead to performance bottlenecks, causing connections to stay open longer than necessary. Ensure your queries are optimized and indexes are properly used, again the SHOW PROCESSLIST; SQL statement can help you identify those queries.
Check your wait_timeout and interactive_timeout settings. Lowering these values will help close idle connections more quickly, freeing up resources.
Besides max_connections, other server parameters like thread_cache_size, table_open_cache, and innodb_buffer_pool_size might need tuning based on your workload. You can use the MySQL tuner script to do a quick analysis of your MySQL configuration:

https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability

The “Too many connections” issue is often a symptom of broader performance challenges. It’s crucial to look at both the database and application holistically to find the best solution.

Hope this helps, and let me know how it goes!

Best,

Bobby