Ok, so I have this program with many (~300) threads, each of which communicates with a central database. I create a global connection to the DB, and then each thread goes about its business creating statements and executing them.
Somewhere along the way, I have a massive memory leak. After analyzing the heap dump, I see that the com.mysql.jdbc.JDBC4Connection object is 70 MB, because it has 800,000 items in "openStatements" (a hash map). Somewhere it's not properly closing the statements that I create, but I cannot for the life of me figure out where (every single time I open one, I close it as well). Any ideas why this might be occurring?
You know unless MySQL says so, JDBC Connections are NOT thread safe. You CANNOT share them across threads, unless you use a connection pool. In addition as pointed out you should be try/finally guaranteeing all statements, result sets, and connections are closed.
I had exactly the same problem. I needed to keep 1 connection active for 3 threads and at the same time every thread had to execute a lot of statements (the order of 100k). I was very careful and I closed every statement and every resultset using a try....finally... algorithm. This way, even if the code failed in some way, the statement and the resultset were always closed. After running the code for 8 hours I was suprised to find that the necessary memory went from the initial 35MB to 500MB. I generated a dump of the memory and I analyzed it with Mat Analyzer from Eclipse. It turned out that one com.mysql.jdbc.JDBC4Connection object was taking 445MB of memory keeping alive some openStatements objects wich in turn kept alive aroun 135k hashmap entries, probably from all the resultsets. So it seems that even if you close all you statements and resultsets, if you do not close the connection, it keeps references to them and the GarbageCollector can't free the resources.
My solution: after a long search I found this statement from the guys at MySQL:
"A quick test is to add "dontTrackOpenResources=true" to your JDBC URL. If the memory leak
goes away, some code path in your application isn't closing statements and result sets."
Here is the link: http://bugs.mysql.com/bug.php?id=5022. So I tried that and guess what? After 8 hours I was around 40MB of memory required, for the same database operations.
Maybe a connection pool would be advisible, but if that's not an option, this is the next best thing I came around.
Once upon a time, whenever my code saw "server went away," it opened a new DB connection. If the error happened in the right (wrong!) place, I was left with some non-free()d orphan memory hanging around. Could something like this account for what you are seeing? How are you handling errors?
Without seeing your code (which I'm sure is massive), you should really consider some sort of more formal thread pooling mechanism, such as Apache Commons pool framework, Spring's JDBC framework, and others. IMHO, this is a much simpler approach, since someone else has already figured out how to effectively manage these types of situations.
