MySQL: ERROR 1040: Too many connections

If you run a fairly busy and/or badly configured MySQL server, you may receive something like this when attempting to connect:

# mysql
ERROR 1040: Too many connections

MySQL is telling you that it is handling the maximum connections that you have configured it to handle. By default, MySQL will handle 100 connections simultaneously. This is very similar to the situation when Apache reaches the MaxClients setting. You won’t even be able to connect to MySQL to find out what is causing the connections to be used up, so you will be forced to restart the MySQL daemon to troubleshoot the issue.

What causes MySQL to run out of connections? Here’s a list of reasons that may cause MySQL to run out of available connections, listed in order of what you should check:

Bad MySQL configuration
Verify that you have set MySQL’s buffers and caches to appropriate levels for the type of data you’re storing and the types of queries that you are running. One quick way to check this information is via MySQLTuner. The script will tell you how well your server is performing along with the corrections you should make. Running the script only takes a few moments and it doesn’t require a DBA to decipher the results.

Data storage techniques
Remember that MySQL works best when moving vertically, not horizontally. If you have a table with 20 columns, breaking it into two tables with 10 columns each will improve performance. Even if you need to join the two tables together to get your data, it will still perform at a higher level. Also, use the right data types for the right data. If you’re storing an integer only, don’t use a CHAR or VARCHAR data type. If your integer will be small, then use something like a TINYINT or SMALLINT rather than INT. This means MySQL will use less memory, pull less data from the disk, and have higher performing joins.

Slow queries
These are generally pretty easy to fix. If you have queries that don’t use indexes, or if queries run slowly with indexes in place, you need to rethink how you’re pulling your data. Should your data be split into multiple tables? Are you pulling more data than you need? Keep these questions in mind, enable the slow query log, and re-work your queries to find where the bottlenecks occur.

Division of labor
Most people who use MySQL have a dynamic site written in a scripting language, like PHP, Perl or Python. It’s obvious that your server will need to do some work to parse the scripts, send data back to the client, and communicate with MySQL. If you find that your server is overworked, consider moving MySQL to its own dedicated hardware. Among many other things, this will reduce your disk I/O, allow you to better utilize memory, and it will help you when you need to scale even further. Be sure to keep your MySQL server close to your web servers, however, as increased latency will only make your performance problem first.

Right hardware
Do you have the right hardware for the job? Depending on your budget, you may need to make the move for hardware that gives you better I/O throughput and more useable cores. MySQL is a multi-threaded application, so it can utilize multiple cores to serve data quickly. Also, writing logs, reading tables, and adjusting indexes are disk-intensive tasks that need fast drives to perform well. When you look for a dedicated server for MySQL, be sure to choose multiple-core machines with low latency RAM, fast drives (SCSI/SAS), and a reliable network interface.

By reviewing these bottlenecks, you can reduce the load on your MySQL server without increasing your maximum connections. Simply increasing the maximum connections is a very bad idea. This can cause MySQL to consume unnecessary resources on your server and it may lead to an unstable system (crash!).

Comments

  1. says

    “You won’t even be able to connect to MySQL to find out what is causing the connections to be used up, so you will be forced to restart the MySQL daemon to troubleshoot the issue.”

    According to the documentation MySQL always reserves one extra connection so a user with the SUPER privilege can login and see what’s going on. See here:

    mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 12.5.4.21, “SHOW PROCESSLIST Syntax”.

  2. says

    @breun
    Yes – you are totally correct. However this issue comes about if you have a user that has a grant like:

    GRANT ALL ON *.* to XXX..

    And you are silly enough to use that user within your application.

    If you create a user for a database and as a result have security in mind, you can still get in with your ‘admin’ account as you point out.

    But as major points out, if you are seeing max_connnections errors, chances are your schema, buffers or queries are at fault.

    What everyone should really do is create user accounts and set max_connections for that specific user to a value, say 10. And then set global max_connections to be upto the sum of every users max_connections.

    Setting max_connections on a per user basis will prevent (or at very least reduce) a single site/database on a shared hosting system from hogging all of the database resources.

    You should set global max_connections to a value that allows your sever to handle the connections – not increase global max_connections when your server cannot handle the current amount of connections.

  3. matt says

    Major, I’m hoping you can help me out here.

    Today the database I mange gave me the 1040 error which was the first time I’ve ever come across it in a year. It is strange though because I would have expected it a year ago. I’ve moved the DB onto a PowerEdge 1950 and have done a fair amount of query optimization during the year so that our site is very responsive. Also, I’m about 95% sure that our traffic didn’t skyrocket (will have to check analytics tomorrow).

    I guess my question is, how can the things you mention above affect the max_connections issue? Is it that the suggestions you make are intended to connect/disconnect as quickly as possible in order to avoid the max_connections limit? (That seems to be the only logical thing in my mind.)

    Also, what are your thoughts on setting the max_connections, of a dedicated mysql, server to 0?

  4. says

    In theory, having n+1 max_connections is a good idea for the root user.

    That is, of course, assuming that MySQL hasn’t started swapping to disk to satisfy the configuration (i.e. opening as many threads/connections as the config allows). This is where things become wonky — high load and iowait, etc. You will be waiting a long time to SSH in and wrangle the box back to normalcy, or connect via MySQL remotely if you have that configured.

    @thornibr was dead on with this:
    “You should set global max_connections to a value that allows your sever to handle the connections – not increase global max_connections when your server cannot handle the current amount of connections.” <— exactly.

    It should be noted that MySQLTuner is very helpful in preventing these problems with its memory usage calculations vs installed RAM.

    Good post, Major. =]

  5. says

    If everything is configured correctly and you still get this error, it means one of two things: Your server got really busy or you got cracked.

  6. Zulian says

    Hi Major, I have this connection problem. I have php application on 10 server and 1 mysql server. All 10 php connect to the mysql server (master). And I got the Gateway Timeout (after firefox spinning about 1 minute) on the browser when the client reaches for about 1000. Thread connected is above 1000.

    But when I place the php application and the mysql server together on 1 server (connect to localhost) I never got the problem. Even its just 1 server running with the same amount of client (1000 connected client). Thread connected is 2 or 3.

    How this could happen?
    Connect to other server : Thread connected is above 1000.
    Connect to localhost : Thread connected is 2 or 3.

  7. says

    Hi Major,
    My VPS always slow down, I’ve use mysqltuner to check mysql config already, followed its recommends as well but it still isn’t worked well. Can you show me another techniques?
    Here is my VPS info:
    RAM: 1GB
    CPU: 1GHz
    Thanks in advance.

  8. says

    I’ve just had this problem in a server of mine, and problem was /var/log was full

    (As I had /home in a separate partition with lots of space, I fixed it by moving all logs to /home/log, deleting /Var/log directory and creating a softlink /var/log -> /home/log, then restart mysql)

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *