• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

connection pooling - sort by most recently used?

 
Stephen Huey
Ranch Hand
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In our web app's connection pool, we recently changed the code that retrieves a connection from the pool for use by the app to sort by least recently used...however, since we regularly try to reap connections (close them--give them back to the database) that haven't been used in a while, wouldn't it make sense that the getConnection() method sort by most recently used to give those connections that we really don't need open a chance to be closed?

I'm just thinking that if our app is usually fine with approximately 100 connections open, but a spike sent it up to 120 for some reason, then while it's okay for those extra 20 to remain open for a while since there could be more surges, we really probably don't need to keep them open if the activity level has been down for a while.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stephen,
I'm not quite sure why you are writing your own connection pool. In any event, aren't all the connections the same? A pool doesn't close connections currently in use anyway.
 
Stephen Huey
Ranch Hand
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It wasn't my choice. I think they originally tweaked some of the Apache Commons stuff a couple of years ago when they were seeing some issues going over ODBC with our old database (there was no type 4 JDBC driver or anything like that). Now, we're converting to Oracle, but because we're on a time crunch, they're reluctant to allow my app code rewriting, so we have these custom connection pooling classes.

Anyway, if your connection pool opens connections from the database and your connection pool never returns them to the db, then they'll stay in there until you restart Tomcat. I suppose there's actually nothing really wrong with this. However, if it's best to generally just keep open the number your app needs at that time, wouldn't it make more sense to sort by most recently used when trying to get a connection from the pool for your app to use? That way, if the least recently used stays the least recently used, it'll eventually be returned to the db (fine, since it's obviously not been needed for some time).

I suppose this really isn't that important a part of our connection pool anyway...
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the pool closes free connections not based on last use of the connections but rather on how many free connections there are, then age doesn't matter. How is your pool deciding to close connections?

Typically, you set a "max-free-connections" parameter on the pool, and the pool's release() method uses it.Since the number of free (not in use) connections only increases when one is returned to the pool, there's no need for a timer to check it periodically for "old" connections.

Some pools have a "time-to-live" setting that will close any connection that has been open (not just in use) for a certain length of time. To truly enforce this accurately would require a timer; less accurately while avoiding using an "old" connection requires checking in acquire(); and least accurate but easiest requires checking only in release().
 
Stephen Huey
Ranch Hand
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Interesting--that sounds familiar, as if I might have read of that somewhere. I wish we weren't using custom classes, but oh well. Thanks--maybe I'll be able to change it to that.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic