Too many connections mysql

Some days before, after reached my office, i tried to access my web application, its show me a error message “Too many connections“, so I immediately logged in mysql server and checked the mysql service, it shows me “Running…” and then i connect mysql using

“mysql -u root -p”

its connected successfully. And then i checked current processlist using the below command,

“Show processlist”

its shows me around 370 queries waiting in the queue which is more than max_connections. I had bad memory to remember everything, i just forget what maximum connection i set to this variable. To find this i edit the my.cnf file and it was 360. Actually this max_connections is a system variable and its default value is 151 which provides better performance. For my application, 151 is not enough, so we set to 360. But on that day we didn’t except this will go more than 360 and mysqld always support max_connection+1(super user). So that when the situation like this comes, we can easily solve the issue or kill the process which are waiting in the queue using the super user account.

When i try to find the number of users logged-in in the site using query, the last connection which i have used(super user account) get hanged and then i couldn’t able to connect mysql. SO now there is no other way for me to connect mysql. i need to wait for the queue to process. I waited for some 30 to 45 minutes but the queue is not processing…so finally i restarted the mysql service and all the queries in the queue get killed and then i able to logged into my application.

The bad thing is we couldn’t able to trace out the issue why this happened? There is no traffic to site and all the servers are very cool.┬áThis is one more very good challenge for me after i come back to office from weekend. I succeeded without knowing the root cause of the issue. But after many hours, we found its because of we took DB Dump in the morning without putting the site offline. Mysqldump will lock all the tables while running and queries that request to the server put in the queue and thats the reason why we got this too many connections.

So whenever you taking mysqldump, please put your site down and then take mysqldump. This will avoid these type of issues in feature. This is very good learning for me.

Advertisements