File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes MySQL memory leak Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL memory leak" Watch "MySQL memory leak" New topic
Author

MySQL memory leak

Mike Southgate
Ranch Hand

Joined: Jul 18, 2003
Posts: 183
I originally posted an eraly version of this question in the java intermediate forum, but investigation indicates its in the jdbc...

I'm getting a java.lang.OutOfMemoryError after calling this method around 4000 times:


As a test I created a loop that went 10000 times and gradually added lines from the above method to see when it blows up. As it turns out, the offending line is : ps.executeUpdate();

I'm using mysql. Any suggestions on how to proceed. Would unloading and reloading the mysql driver help?

Has anyone else out there used mysql with thousands of inserts?

ms


ms<br />SCJP, SCJD
Steven Bell
Ranch Hand

Joined: Dec 29, 2004
Posts: 1071
Wild guess, maybe make the PreparedStatement a class level variable so only one instance is made.

Also move the ps.close() to a finally block. (but if you haven't been seeing any exceptions this probabaly isn't a problem).

On second thought maybe the PS isn't a problem but it looks like you are getting a new connection each time and not closing it. Just guesses, I'm not a JDBC guru by any means.
[ March 09, 2005: Message edited by: Steven Bell ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30752
    
156

Mike,
A few comments:
1) Try executing the loop with a different prepared statement. For example, try with one that just does a select. This will provide more clues as to the problem.
2) If you are calling this alot of times in production, consider not making it synchronized or executing multiple inserts in a batch.
3) The "ps = null" statement doesn't do anything as the ps variable is about to go out of scope anyway. This isn't the problem, but it's good to know.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Mike Southgate
Ranch Hand

Joined: Jul 18, 2003
Posts: 183
Originally posted by Jeanne Boyarsky:
Mike,
A few comments:
1) Try executing the loop with a different prepared statement. For example, try with one that just does a select. This will provide more clues as to the problem.
2) If you are calling this alot of times in production, consider not making it synchronized or executing multiple inserts in a batch.
3) The "ps = null" statement doesn't do anything as the ps variable is about to go out of scope anyway. This isn't the problem, but it's good to know.


Thanks for the input. Re # 3, that is actually left over code from when I thought the issue might be garbage collection and was trying to force earlier cleanup.
I made it synchronized to allow for multi-threading, though that isn't an issue yet.
Batching the inserts is a good idea, that might at least reduce the issue.
I'll also try using selects instead.
One last thing is this used to work OK before I started using the JRE that came with J2EE. I think I'll give 1.5 a shot and see what happens.

ms
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL memory leak