Question

How to close Two many connection error mysql

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.


Submit an answer


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!

Sign In or Sign Up to Answer

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 Iliev
Site Moderator
Site Moderator badge
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:

  1. 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.

  2. 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.

  3. 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.

  4. Check your wait_timeout and interactive_timeout settings. Lowering these values will help close idle connections more quickly, freeing up resources.

  5. 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

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel