I am having a problem with tomcat opening up up a number of connections to an oracle server that never get closed. This causes the number of open connections to build up over time and, eventually, causes the oracle server to use all of its swap.
That's not good :(
Restarting tomcat clers this up.
That's good! :)
I think there is a problem with some jsp's opening connections and then not closig them but the developers claim (surprise) their code is clean.
It's tough to make sure that database connections (and statements, and result sets) get cleaned up in JSPs, unless you have a talented JSP author. (Most JSP authors aren't that talented, unless they are also good Java developers, in which case they would have implemented the DB access in a servlet and just used the JSP for display. Anywho...)
If the number of connections keeps going up and never tapers off or stops altogether, then something is misconfigured with your connection pools. Even if the engineers say that the pages are clean, you should protect the app server (and the DB server) from being swamped by capping the number of DB connections allowed. Ever. Any decent DB connection pool lets you specify this kind of thing. You should set that value to something reasonable. You can get away with a suprisingly low number of these.
(I was consulting on a big project that was a somewhat DB intensive, web-based app. They had the app server configured to accept 75 simultaneous connections. They also set the db connection pool size to 75. I asked why and they basically said "so that every HTTP connection can get a db connection". Duh. I talked to management and make them put in debugging information to find out how many connections were ever in use simultaneously. Seven. (Suckers). They also didn't realize that Oracle takes like 10MB per connection on the backend, and they had six physical app servers running two separate copies of the application. That's 75 * 6 * 2 * 10MB = 900MB. Good thing the DB server had 3.5GB of RAM, but still...)
The explanation they give is:
"The increase in number of connections beyond the âCACHE_MAX_SIZEâ setting in the app1.properties file is due to the private labeled sites. For each virtual host (private labeled site), there will be a separate JVM running the Tomcat web server space. For each of these JVMs, there will be a separate database connection cache pool to serve the user requests. This is the designed functionality of a web server that will support virtual hosts."
I don't know tomcat near as well as I do Apache but this sounds like someone is blowing smoke.
This isn't too outrageous, actually. If each webapp has its own connection pool, and they are configured to have at maximum, say, 10 connections, then you'll probably end up with 10 * webapp_count connections to the database server, regardless of the number of Tomcats/JVMs are running.
If Tomcat is configured to handle the connection to the database (say, through a Realm and a JNDI-configured connection pool), you might be able to share connections between all of the webapps. If you solve the private-labelling problem by using multiple webapps, but through the same database, this approach seems like an excellent idea; configure Tomcat to provide a JNDI-based connection pool, and then configure the separate applications to use that pool. That way, you can control the total number of connections across all private labels, instead of having them be independent.
If I run ps on the server it looks to me like there is only one instance and if I restart tomcat, _all_ virtual hosts are restarted.
Yeah, then it's definately separate webapps running on a single instance of Tomcat. Try to pitch the above idea to your engineers and see what they say (probably something like "it's fine the way it is!").
I don't care who is right or wrong but I do want to clear up this problem. Any ideas? If you need any more information, just ask.
I think I'd need to know if the connections were really never going away. Use netstat to find out what state they're in. If they all say ESTABLISHED, then you've got a connection leak. If many of them say TIME_WAIT or something like that, then you might have a problem with either the client or the server not properly hanging up the phone. If it's the former, then yell at your engineers. Cap that connection pool size at something reasonable, like ten connections. After that, the application starves. That's good for the app server and the database, while bad for your application. You can use Jakarta Commons' DBCP as your connections pool. It has some wonderful debug options, like giving you a stack trace for the code that obtained the connection if that connection isn't returned within a certain amount of time. That can save days or weeks of code reviews. If your connections are in TIME_WAIT, see how long they stay that way. Waiting 5-10 minutes for a connection like that to get cleaned up is not unheard of. If they're piling up on top of one anothor and /never/ going away, it's time to talk to a system administrator. If the syadmin is you, it's time to talk to the guy you go to when you don't know things. Everyone needs a guy (or girl!) like that. :)
Good luck!
Let me know if I can offer any more help.
-chris
signature.asc
Description: OpenPGP digital signature
