aspose file tools*
The moose likes JDBC and the fly likes ConnectionPool causing memory leak Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ConnectionPool causing memory leak" Watch "ConnectionPool causing memory leak" New topic
Author

ConnectionPool causing memory leak

Joel Sander
Ranch Hand

Joined: Jan 09, 2009
Posts: 38
I've been trying to update my database server, which connects to a mysql database, to use connection pooling. Without pooling, it runs without any memory leak issues. When I add connection pooling it begins to memory leak. I've tried creating my own connection pool and am now using snaq's pool:

http://www.snaq.net/java/DBPool/

I've reproduced the memory leak by repeated calling the getAll and updateAll methods listed below. As far as I can tell these methods close all connections properly which makes me wonder if a single pooled connection is storing info about all past uses until it runs out of memory so that it can do a "global" rollback.

Here is the getAll method:




The following memory leaks:



Any help would be greatly appreciated. I'm really running out of ideas to solve this.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Just an idea: some connection pools cache PreparedStatement objects (for every connection in the pool) so that they can properly reuse them with the database and save parsing time. Maybe some other objects are cached for various reasons too. Is the leak proportional to the number of calls to your methods, or does it level off with growing number of calls (indicating the caches became saturated)?

This situation is probably worsened by the fact that in the updateDBObjects method you create many different versions of prepared statement, by not using bind for one of the values (line 71 in the corresponding printout). If I'm right then binding this value should greatly reduce the leak, because the cache will recognize you're creating identical statements and will reuse the cached version.

I haven't much experience with hunting memory leaks though, so please be forbearing if I'm way off the mark.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

So this isn't a resource leak, right? You don't run out of connections, as far as I can see. You're suggesting that memory is being leaked by that package. In which case, have you done any profiling to see what's going on?

Edit: I see Martin has been posting while I have been goofing off. His theory sounds not bad, and a profile should be able to test it quite readily.
Joel Sander
Ranch Hand

Joined: Jan 09, 2009
Posts: 38
Hi Martin,

Thanks for the thoughts. Please excuse my ignorance, none of my experience chasing memory leaks in java extends to database work.

Do you know of a way to test whether the connection pool is trying to cache prepared statement objects? Could you explain how caching would defeat/override the explicit ps.close()?

I'm a bit confused by the issue with mentioned on line 71, since line 71 only appends to a StringBuffer. Are you suggesting that I modify the for loop to something more like this to see if the memory leak scales with # of prepared statements created:



Joel Sander
Ranch Hand

Joined: Jan 09, 2009
Posts: 38
Paul Clapham wrote:So this isn't a resource leak, right? You don't run out of connections, as far as I can see. You're suggesting that memory is being leaked by that package. In which case, have you done any profiling to see what's going on?

Edit: I see Martin has been posting while I have been goofing off. His theory sounds not bad, and a profile should be able to test it quite readily.


I don't run out of connections. When I used my own homebrewed connection pool, I'd get the memory used:



during each call to get a pooled connection and could see the memory usage rise (and occasionally fall, but the trend was always upward) until the server started having out of memory exceptions.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Hi Joel,

I've learned about prepared statement caching from documentation to Oracle's connection pool I'm using.

I assume memory profiler should help you to determine whether the statements are cached. Run the original version of your code with it, since it creates lots of distinct prepared statements and if they are indeed cached, they should be spotted easily.

Your modified code is almost right, you just need to specify a value for the fourth variable using ps.setInt(4, dbObjects[i].id).

You should also reconsider how you are measuring the memory usage. The amount of used memory you compute will steadily rise until GC collects objects that are no longer used, and this may not happen for a long time. That the amount of used memory steadily rises at the beginning of the execution is not necessarily a sign of memory leak. If you didn't get an OutOfMemory exception with the connection pool you're using now, you need to use a memory profiler to determine whether there really is a memory leak.
Joel Sander
Ranch Hand

Joined: Jan 09, 2009
Posts: 38
Martin Vajsar wrote:Hi Joel,

I've learned about prepared statement caching from documentation to Oracle's connection pool I'm using.

I assume memory profiler should help you to determine whether the statements are cached. Run the original version of your code with it, since it creates lots of distinct prepared statements and if they are indeed cached, they should be spotted easily.

Your modified code is almost right, you just need to specify a value for the fourth variable using ps.setInt(4, dbObjects[i].id).

You should also reconsider how you are measuring the memory usage. The amount of used memory you compute will steadily rise until GC collects objects that are no longer used, and this may not happen for a long time. That the amount of used memory steadily rises at the beginning of the execution is not necessarily a sign of memory leak. If you didn't get an OutOfMemory exception with the connection pool you're using now, you need to use a memory profiler to determine whether there really is a memory leak.


Thanks again for the helpful advise. I'll try the batch prepared statements and report back.

I haven't looked at memory profilers in years. Would you have any advise for which one might work best for profiling this stand alone server (if need be I can make external calls to the getall and updateall methods when desired).
Joel Sander
Ranch Hand

Joined: Jan 09, 2009
Posts: 38
Martin Vajsar wrote:Just an idea: some connection pools cache PreparedStatement objects (for every connection in the pool) so that they can properly reuse them with the database and save parsing time. Maybe some other objects are cached for various reasons too. Is the leak proportional to the number of calls to your methods, or does it level off with growing number of calls (indicating the caches became saturated)?

This situation is probably worsened by the fact that in the updateDBObjects method you create many different versions of prepared statement, by not using bind for one of the values (line 71 in the corresponding printout). If I'm right then binding this value should greatly reduce the leak, because the cache will recognize you're creating identical statements and will reuse the cached version.

I haven't much experience with hunting memory leaks though, so please be forbearing if I'm way off the mark.


Just wanted to follow up here. I spent several hours tonight testing this theory and it seems to hold water: making the suggested change did work(!!) and further I found in the API the ability to turn off caching for the pool. I undid the suggestion and tried running with disabled caching for the connection pool and that worked as well! Thank you so much!

One small followup question: do you have any feel for if disabling the pool's caching adversely impacts performance?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Joel Sander wrote:One small followup question: do you have any feel for if disabling the pool's caching adversely impacts performance?

It might depend on the database you're using. I know Oracle best and I can describe the effects for Oracle, but for other databases it might be different.

Prepared statements have two benefits: first and foremost they limit the number of unique statements that you run in the database. This is important, because every unique statement takes some memory in the shared memory pool and if you issue lots and lots of different statements in quick succession, you'll probably force other statements out of the shared memory pool, even statements of other applications. Prepared statements have the same exact SQL text, so if you run one prepared statement with lots of different bind values, it is still just one statement. Moreover, every statement not in shared pool has to be parsed and analyzed from scratch, and when other statements that were squeezed out of the shared pool are run again by their applications, they will have to be reparsed too. So this is the most important speedup connected with prepared statements. Not using prepared statements makes your application inherently unscalable in Oracle. (Also, using binds protects you against any and all SQL injection attacks, so this reason alone should make you never use plain Statement again, but it has nothing to do with performance.)

Secondly, when you run prepared statement for the first time, it is properly parsed and analyzed, but also it is assigned a plan according to which it is executed in the database. On subsequent runs the parsing is skipped altogether and just the stored plan is executed. If the connection pool caches the statements, parsing will be done only when each of the statements is first prepared with given connection. When you prepare the statement next time, it will be retrieved from the cache and parsing will be skipped altogether.

If you disable statement caching, the first (and more important) benefit of prepared statements still holds, but this second benefit might be partially lost. You'll still see some improvement if you run the prepared statement multiple times before it is closed (as you do in the modified version of your code) - it will be parsed only once.

I'd personally make sure all statements are properly bound to minimize number of unique SQL run against the database and leave the cache on. This way your application will be most scalable, at least on Oracle. There are also some subtle details I've omitted, especially in some very rare cases it is better not to bind. If you're interested, you should read your database documentation. For Oracle that would be the Concepts users' guide, it is an invaluable read for anyone dealing with that database.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ConnectionPool causing memory leak