Finding & Resolving Mysql Connection Leaks
As the title imply; we need to know why Mysql runs out of connections even if it may be configured to handle much more connections. First, most leaks come from the code, check for unclosed connections before anything. If you are using JPA/Hibernate - which is my case - you use a data source either managed by the container as a JNDI resource or a simple component used by the application.
In my application I had a lot of time debugging why mysql runs out of connections and finally I did the following to know the cause:
1- Limit the running features of your app: for example; I ran only the scheduled jobs of the system to maintain a clear log and also monitor the DBMS health slowly. Let your app run for a while till the problem happen or a suitable time has passed.
2- Mysql Admin: see the health of the DBMS from the "mysql admin" tool, if you find the connection usage rising up then we have a connection leak problem for sure.
3- show processlist: in the mysql command line tool, type "show processlist;". Notice the "Command" column of the results in front of you if you find a lot of connections marked as "Sleep" then we are on the right track for the disaster as sooner or later the connection pool will run out of connections.
4- wait_timeout: The "wait_timeout" variable tells Mysql when to consider an unused connection available for termination. for me, that was the problem, a default setting for the DB server not suitable for production or heavily loaded environments. Open the system variables tab and find the "wait_timeout" variable and check its value - default is 28880 seconds -, change this to a much lower value say 15 for example.
What caused the sleeping connections?
A data source may not close the connection after communicating with the DBMS for future use purposes, so, it puts the connection in a sleeping mode and it is the responsibility of the DBMS to terminate it as needed. What we have done in the last step is making sure the DBMS does its part in the play.
