read

This post is to help server administrators who are managing MySQL servers. If you run into users complaining about error regarding too many connections to MySQL, do check the script that runs and make sure that connections are closed.

For example if you open a connection in php using:

$mysqli = new MySQLI(‘localhost’,’root’,’password’,’dBName’);

At the end of the script, do remember to close the connection by calling:

*mysqli_close($mysqli); *

To verify that things are working, SSH into your system,

1) go into MySQL command line by typing something like mysqld -u root -p
2) type in “show processlist;
3) take note of the processes already running
4) Run the php (or .net or whichever script)
5) type in “show processlist;” again a while after the script has finished executing
6) If you see new processes being in Sleep status then the script is not closing the connections properly (unless this is a deliberate action by the developer to re-use a connection, but this usually should not happen)
7) You can manually kill the process by typing “kill ;” (You can see the process id in the process list table

So if the script is not closing connections, the script should be checked to ensure that they are closed and run through the test above again. This will assist in the server’s performance as well.

Note: The default maxconnections, which govern the maximum number of connections to the MySQL server instance is 100, you can increase this, but it is not a good solution compared to the above which addresses the root of the issue. MySQL has a suggestion that dynamically increases the maxconnections which works as well but it has to be dependent on the scenario you are facing.

Blog Logo

alvin

0


Published

Image

1998 Nineteen-Ninety-Eight

Thoughts, stories and ideas.

Back to Overview