The number of simultaneously handled connections on a MySQL server isn't infinite. There's a setting that defines this limit. By default it's 100 concurrent sessions.
Once this maximum is reached, the MySQL server simply starts throwing an error like this:
too many connections
And no one else can connect to it (until a free "slot" opens up) - even the administrator logging in as root will get the same error.
1)
Why?There are two options:
- Bugs in the code of programs using the MySQL database. In this case, the programs (whether it's poorly written websites on a web server or regular applications using MySQL as a DBMS) create a huge number of connections, often tending toward infinity. The solution here is to fix the code of the misbehaving site, or update the programs to the latest versions and let the developer know about the situation. Spotting this kind of glitch is easy - after restarting the MySQL server, connections run out again fairly quickly.
- Genuine load on the server that exceeds its capacity. As a rule, in this case, the error occurs rarely and goes away on its own - the load doesn't stay constant, and literally within a few seconds you again have a server available for connections.
2)
What to do?Right here, right now, to get the server working again immediately - just restart the MySQL service.
for Debian/ubuntu:
$ sudo /etc/init.d/mysql restart
for CentOS/RedHat:
$ sudo /etc/init.d/mysqld restart
for FreeBSD:
$ sudo /usr/local/etc/rc.d/mysql-server restart
And next:
In the first case everything's clear - you need to fix the software problem.
In the second case - you can raise the maximum number of connections to the server that MySQL will serve. You can't raise it indefinitely - you need to keep an eye on the machine's physical resources, but if there are enough resources, let's show where that setting is located.
3)
Increasing the maximum number of connectionsOpen the my.cnf file for editing
Find and uncomment it, and if you didn't find it - just create the setting under the [mysqld] section:
max_connections = 150
where instead of 150 enter the number you need.
Save the file and restart the MySQL server.
The same method can be used to decrease the maximum number of connections.
Applies to: MySQL 5.x
Comments