I did Googling a bit and I found that most common problem is because MySQL has too many open client being connected to it at the same time. MySQL has a system variable called max_connections. If you have it set up at 100, MySQL will have 101 connections. The last one is reserved for super user. That makes sense because you’ll need to connect as super user to do maintenance on these kinds of problems. Here’s how to find out what’s max_connections variable is set to and how to see connected clients. Off course, you have to login as super user first.
-- This command will return you the value of max_connections variable SHOW VARIABLES LIKE '%max_connections%'; -- This command will show you all processes and connections. SHOW FULL PROCESSLIST;
So if you run the first command and it returned you 100, then you run the 2nd command and it returns 100 rows, then you really got some problems. There are 2 ways, I think to fix this:
- Increase max_connections variable to like 200 or some other number higher than 100. However, I like to use this as last resource. I would re-asses my usage and see if I really need more than 100 connections. If I do, then there’s no other ways around it. Increase the number and perhaps add more power to your hardware as well.
- Rework your application. Try to use connection pooling. Or maybe caching is a solution to lessen the load on database.
Now, given the statement in MySQL website that “If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.”, naturally, I tried to login as super user. Alas … I still could not login as super user. So immediately my question is to see if there’s a super user connection already open somewhere, perhaps from the local host.
So I logged in to the box and apparently, the problem was that I’m out of disk space. Clearing disk space immediately gives me access to MySQL and I don’t get Too many connections error anymore. I guess MySQL statement on their documentation is not 100% truthful. You can also get Too many connections error if you have full disk space.
Lastly, if you really need to increase your maximum connections, you can execute the following command while logged in as super user.
-- Change 200 to some other number you desire. SET GLOBAL max_connections = 200;
Another option is to add the following line in your my.cnf configuration file under [mysqld] section.
max_connections = 200
I hope this article helps you. As always, I welcome comments / questions / critics that will help me and other readers understand better.