Bookmark and Share

MySQL : Too many connection errors.

Posted: Friday, January 23rd, 2009 at 12:50 amUpdated: Sunday, July 19th, 2009 at 5:50 pm

I was trying to connect to a MySQL server at work and I got connection fail “Too many connections”. Here’s the screen shot that I took using Navicat Lite client and Sequel Pro.

picture-1

picture-2

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.

11 Responses to “MySQL : Too many connection errors.”

  1. drew Says:

    hi.. can i just ask how can i limit a database size to a specific MB? because we are trying to make like a demo version thanx!

  2. Maresa Says:

    I don’t think you can limit a database size to a specific MB. You can, however, try to have the database on a specific directory on the server and have that directory size limited. In that case, it’s more like the OS file restriction (or quota) rather than database-imposed limitation.

  3. fauzy Says:

    May i know how to increase the max_connection?
    is it by using command? how can i get it?..
    many web said "Increase max_connections variable" but not tell exactly the step to fix it.

  4. Maresa Says:

    You need to login as super user. Then execute this code:

    SET GLOBAL max_connections = 200;

    If you want to change it permanently, you can add the following like under mysqld section.

    max_connections = 500

    Thanks for the question. I’ll modify the post to include this information.

  5. jeephnx Says:

    Thanks. I can confirm from experience that a full disk creates this error. I guess it needs temp space to create the connection but fails and returns the message. Interestingly I also found the server was under pressure from googlebot which Ive read may also cause this error.

  6. Maresa Says:

    Hmmm … getting this error when server was under pressure from Google bot seems a legitimate too many connections error, especially if you don’t have connection pooling.

  7. sundar Says:

    hai how to clear the disk space. Because i still face the “too many connection error”

  8. Maresa Says:

    If it is due to disk being full, login to the server (through SSH or equivalent) and delete unnecessary files (perhaps old logs, etc).

  9. Ranjith Says:

    My mysql server was on windows platform and I had to restart the service to connect, then i changed the max users to 200. Thanks for your help.

  10. Raf Says:

    Thank you !

  11. Dinesh Says:

    Thanks a lot !!!!!! Its working fine. Keep it up this kind of services. !

Leave a Reply


six + 9 =