Running out of sessions in SQL, looking at config information on the app server

[bgrant]

We are running a healthcare application which does not use stored procedures, commands are passed from the application server. We occasionally get complaints of dropped connections, and at those times we believe that there are no connections left in the pool. We have appealed to the vendor to look at the following Apache Tomcat 6.0 information on their app server, but have not had a response. Can anyone help us by determining whether there are inappropriate settings for any of the values, InitialSize, macActive, etc ? Does Tomcat allow you to define a connection pool, or do we need to look elsewhere (JDBC) for the pool information? Is there a default location\name for JDBC connection pool information?

<Context path="/mobiledoc" docBase="mobiledoc" debug="4" reloadable="true">
<Resource name="jdbc/mobiledocDb" auth="Container" type="javax.sql.DataSource" initialSize ="45" maxActive="-2" maxIdle="45" maxWait="10000" validationQuery="SELECT 1" testWhileIdle="true" timeBetweenEvictionRunsMillis="1800000" minEvictableIdleTimeMillis="25" username="" password="" driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://Lifepwpsql02/mobiledoc;maxStatements=0" removeAbandoned="true" removeAbandonedTimeout="120"/>
</Context>

[Tolls]

First off, I suspect you'd get a better spread of answers at OTN, the Oracle forums for Java. They are likely to ask you how you know it a case of no connections, as you don't seem to be certain of that ("we believe"). Do you have exceptions in your logs showing this (I can't remember the exception offhand, but the pool should log the fact it's run out)?

Neither us here not the lot at OTN (which sometimes includes me) will be able to say how large the pool should be, since that's entirely down to your system and load. If it is running out that does not necessarily mean the pool is too small. It could mean that something is holding onto connections, or some process is taking longer than it should.

Oh, also, what does "dropped connections" mean? In something like this, which is presumably a webapp, connections are generally picked up on a per-request basis, so each request to the server grabs a connection (if it needs it) and hands it back when it finishes with it (which can happen several times in a single request, depending on the task). So the connections aren't dropped...they just aren't available.

[bgrant]

You may want to check out SQL Connection getting dropped for an explanation of dropped connections. And never mind, I'll try to get an answer elsewhere, and hope for a less pompous approach to my question.

[JosAH]

What is supposed to be pompous about Tolls' reply?

Jos

[Tolls]

You may want to check out SQL Connection getting dropped for an explanation of dropped connections. And never mind, I'll try to get an answer elsewhere, and hope for a less pompous approach to my question.

Yes, which can happen on long lasting connections (I get it with SQL Developer and Oracle all the time).
You are using a connection pool, hence my attempt in the last paragraph to get some idea of what you mean by "dropped connection" as you are talking about pooled connections. Webapps usually do not suffer from that. They may be unable to get a new connection, but that's not the same thing. If the connection dies in the middle of a transaction (which are rarely long) then that has nothing to do with pooling and everything to do with networks. But the logs (which I asked about) will tell you that.

Everything in my post was to try and get some more information to try and answer your question, either here or at OTN. There are bigger guns over there (or at least more of them), but they will ask for the same information I have asked for here. To be honest, anywhere will need this information.

I thought I was saving you some time...no condescension or pomposity intended at all.
(Usually I can see where someone might misinterpret my posts, but I really can't see it this time...)

[b.hedge]

I am curious about the settings in the original post and am trying develop a "best configuration". When I use the settings:

<Context path="/mypath" docBase="mydocbase" debug="4" reloadable="true">
<Resource name="jdbc/MyDb" auth="Container" type="javax.sql.DataSource" initialSize ="45" maxActive="-2" maxIdle="45" maxWait="10000" validationQuery="SELECT 1" testWhileIdle="true" timeBetweenEvictionRunsMillis="1800000" minEvictableIdleTimeMillis="25" username="" password="" driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://SQLServer/MyDatabase;maxStatements=0" removeAbandoned="true" removeAbandonedTimeout="120"/>
</Context>

I am getting a very large pool from my application server to the SQL server. I am seeing ~200 connections per app server! When I check the connections in SQL there only only 1-4 sessions active at a time. The rest of the connections / sessions from the Application server are SLEEPING?!? Even when I look every few hours no connections die off (i.e. removeAbandoned).

I need to understand what these setting really mean and what I need to set them to to get ~10 sessions per app server (and to spin up more connections if all 10 are occupied). Does anyone have a good reference for these settings? I am a complete N00B here but willing to learn!

Please point me in the right direction for configuring these items:

initialSize ="45"
maxActive="-2"
maxWait="10000"
validationQuery="SELECT 1"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="1800000"
minEvictableIdleTimeMillis="25"
driverClassName="net.sourceforge.jtds.jdbc.Driver"
url="jdbc:jtds:sqlserver://SQLServer/MyDatabase;maxStatements=0"
removeAbandoned="true"
removeAbandonedTimeout="120"

Are any of these setting in conflict from the original example?

[Tolls]

And since you're a complete noob my points are probably more important, since your analysis of the problem is not necessarily correct.

But hey ho.

maxActive -2 I'm not sure about. -1 means "as many as required", so it's quite possible any negative means the same. Which would explain why you are getting any number of connections being grabbed.

Next, your timeBetweenEvictionRunsMillis is saying "sweep up connections every 30 minutes". This means a connection has to be idle for 30 minutes before being considered for closing. Which could well mean your connections are timing out on the database.

I could suggest dropping that to zero to remove the sweeping entirely, so connections are closed on return to the pool if they aren't needed. Again, depends on your system, of which I know nothing.

[b.hedge]

And since you're a complete noob my points are probably more important, since your analysis of the problem is not necessarily correct.

maxActive -2 I'm not sure about. -1 means "as many as required", so it's quite possible any negative means the same. Which would explain why you are getting any number of connections being grabbed.

Next, your timeBetweenEvictionRunsMillis is saying "sweep up connections every 30 minutes". This means a connection has to be idle for 30 minutes before being considered for closing. Which could well mean your connections are timing out on the database.

Hey TOLLS... thanks for the reply. I will set maxActive to -1. My connections to the SQL server are not timing out and I am not seeing anything "dropped". I (b.hedge) was hijacking this post (I did not start the post...) I was just using his settings and had some questions about what I was seeing. What I am seeing on the SQL server is that the Connections are rarely closed and there are 200 of them per application server sleeping. Would the SELECT 1 that is running be messing up the Every 30 minute eviction? I will go back over this stuff and find a good summary of the settings. I welcome any additional feedback. Thank you again for the reply!

[Tolls]

Oops.
I clearly didn't read past the 'b' in your usernames...:)

That SELECT statement is used as a keep alive. It ensure that the minActive, or is that minIdle(?) anyway the one that maintains a minimum size for the pool, it ensures that those connections don't time out. When the pool does it's timeout loop it'll fire off that query to the db for any connections it's trying to maintain alive.

It is odd not to see a minimum defined (and I didn't notice that), so I wonder if that's part of the reason you are seeing the connections simply rack up.

[b.hedge]

Oops.
I clearly didn't read past the 'b' in your usernames...:)

No problem...

It is odd not to see a minimum defined (and I didn't notice that), so I wonder if that's part of the reason you are seeing the connections simply rack up.

That sounds like something i need to look at... do you have a link to a reference to better explain each of these settings? I can look at MinActive and see if that will help clear it up. Something is going on to keep them open!?! If you think of anyting else please pass it on. I am trying to figure this out and do it right!

[Tolls]

You could have a look around on the Apache Tomcat site?
I can never find these things when I need them...