DEV Community

Cover image for MySQL Too Many Connections (Error 1040): Causes and Fixes
devTalk
devTalk

Posted on Originally published at dev-talk.com

MySQL Too Many Connections (Error 1040): Causes and Fixes

#mysql #mariadb #maxconnections #troubleshooting

Your site suddenly shows a database error, and the log says this:

ERROR 1040 (08004): Too many connections
Enter fullscreen mode Exit fullscreen mode

MySQL accepts only a fixed number of simultaneous connections. Once that number is reached, the next client is refused until someone disconnects. Raising the number fixes the symptom in one command. Finding out why you reached it fixes the cause. This article shows both.

What the error means

The limit is the max_connections setting. Its default is 151, which I confirmed from the MySQL 8.0.46 server's own help output. Every open connection counts, whether it is running a query or sitting idle waiting for the next one. A PHP-FPM worker, a queue worker, a cron job, a database GUI left open on someone's laptop: each one holds a slot until it disconnects. MySQL also keeps one extra slot for administrators. A user with the CONNECTION_ADMIN or SUPER privilege can connect even when the limit has been reached, so you can still log in to investigate. Do not give that privilege to application accounts, or you lose this safety valve.

Reproduce it in two minutes

On a test server with max_connections=5 and an application user, I opened five connections that each ran SELECT SLEEP(40) and then tried a sixth:

$ mysql -uapp -ppw -e "select 1"
ERROR 1040 (08004): Too many connections
Enter fullscreen mode Exit fullscreen mode

An admin account still got in, and showed what was using the slots:

mysql> show global status where Variable_name in
    -> ('Threads_connected','Max_used_connections','Connection_errors_max_connections');
Connection_errors_max_connections   1
Max_used_connections                6
Threads_connected                   6
Enter fullscreen mode Exit fullscreen mode

Six connections for a limit of five: the five application connections plus the one reserved for the administrator.

Step 1: See who is connected 

SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

SELECT user, COUNT(*) AS connections
FROM information_schema.processlist
GROUP BY user
ORDER BY connections DESC;
Enter fullscreen mode Exit fullscreen mode

In my test, the grouped query returned app 5, root 1 and the internal event_scheduler 1. In your case, one account or one host will usually stand out. To see what each connection is doing, run SHOW FULL PROCESSLIST;, which is the same data with the query text.

What you see What it usually means
Many connections in state Sleep Clients opened a connection and are not using it: leaks, no connection reuse, or a pool bigger than needed
Many connections running the same slow query One query is slow and requests are piling up behind it. More connections will not help.
Connections from a host you do not expect A forgotten script, a staging server pointed at production, or a GUI left open
Max_used_connections equals max_connections + 1 You hit the limit at some point since the server started

Step 2: Raise the limit (the quick fix)

You can change it on the running server without a restart, using the admin account:

SET GLOBAL max_connections = 200;
Enter fullscreen mode Exit fullscreen mode

In my test the application user could connect again immediately. This change is lost when MySQL restarts. In MySQL 8.0 you can make it permanent from SQL:

SET PERSIST max_connections = 300;
Enter fullscreen mode Exit fullscreen mode

That wrote the value into mysqld-auto.cnf in the data directory in my test. The other option, which works on every version and is easier to review in version control, is the config file:

[mysqld]
max_connections = 300
Enter fullscreen mode Exit fullscreen mode

Restart MySQL after editing the file, or use SET GLOBAL as well so you do not need a restart right now. On MariaDB the same variable and statements work, but SET PERSIST is a MySQL 8 feature.

Do not set a huge number "to be safe". Each connection uses memory for its buffers and a thread, and a very high limit lets a traffic spike or a leak overload the whole server instead of failing early. Raise it in steps, and watch memory and Max_used_connections. Also check that the operating system allows enough open files for the number you choose, or MySQL may lower it at startup.

Step 3: Fix why it happens

Applications that open connections and never close them

Long-running scripts, workers and loops that create a new database connection per task are the usual cause. Reuse one connection per process. In PHP, PDO connections close when the object is destroyed or the script ends, so a leak means the object is being kept alive, for example in a static property or a long-running worker.

Too many workers for the database

Count the maximum number of connections your application can open at once: PHP-FPM pm.max_children across all servers, plus queue workers, plus scheduled jobs, plus every other service. If that total is above max_connections, you will hit the limit under load by design. Either lower the worker counts or raise the limit with the memory to match.

Idle connections kept too long

wait_timeout decides how long MySQL keeps an idle connection open before closing it. The default is 28800 seconds, which is 8 hours (confirmed on the 8.0.46 server). Lowering it, for example to a few minutes, frees slots held by abandoned clients. Make sure your applications can reconnect, because a client that holds on to a connection the server has closed gets "MySQL server has gone away". I did not test that setting.

Slow queries that hold connections

If most connections run the same query, the problem is the query. Add the missing index, or fix it, before you add connections. More connections to an overloaded database usually make it slower.

One account taking everything

You can cap a single account so it cannot starve the others:

ALTER USER 'reports'@'%' WITH MAX_USER_CONNECTIONS 20;
Enter fullscreen mode Exit fullscreen mode

The global default max_user_connections is 0, which means no per-account limit. I did not run the per-account limit in my test.

Connection pooling

If you really do need thousands of client connections, put a connection pooler or proxy in front of MySQL, such as ProxySQL, so many clients share fewer database connections. That is a bigger change than the steps above, so use it when raising the limit stops being sensible.

If you cannot even log in

If an administrator account is also locked out, it is probably because an application account or script is configured with admin privileges and used the spare slot. Connect as root through the local socket (sudo mysql on many Linux distributions), run SHOW PROCESSLIST;, and end the offending connections with KILL <id>;. Then fix the privileges.

Frequently asked questions

Is the limit per user or for the whole server?

max_connections is for the whole server. max_user_connections is the optional limit per account.

Why does Max_used_connections show one more than my limit?

Because of the extra connection reserved for administrators, as in the test above.

Will restarting MySQL fix it?

It clears the connections for a while, and the cause will fill them up again. Use it only as an emergency step, after you have looked at the process list.

Does this error appear in Laravel?

Yes. It shows up as a database connection exception carrying error code 1040 (SQLSTATE 08004, the same pair the mysql client printed above). Laravel is not special here: the cause is how many workers and processes connect at once.

Sources

Originally published on DEV Talk.

Top comments (0)