We have a problem with our database server connections. Our database server is located on a remote server. Sometimes all connections are active. We don’t know what causes this. We know we can change the number of allowed conenction, but that’s not the solution.We can only solve this by restarting the application server. Is there another way to reset the active connections? Restarting is a problem because there are always clients connected.
Database Server Settings
URL: jdbc:jtds:sqlserver://IP/dbname
Driver: net.sourceforge.jtds.jdbc.Driver
Status
Server status: Online
Active connections: 10/10
Idle connections: 0/5
Connections can stay active when they are still in a transaction or be stuck in a query (dead-lock) or just waiting for another long running query. When you see all those active connections you should check the database server to see what those connections are actually doing.
You can check some of this from Servoy Server but to really see what’s going on you need to check this on the database server.
then if you do a stackdump from the admin pages can you show that to us?
Because if it is something robert describes then we should see that, but it could also be another problem and that is if you use a plugin that also uses connections but never closes/releases them.
Then that plugin is eating up your connections.
At this moment we have the problem. I did a stackdump: http://www.orderyourflowers.nl/stackdump.txt
We also use plugins. We don’t have the problem every day, only once a week or something on different database servers.
i see only 2 active queries and loads waiting for a new connection.
What do you see in the transaction section of the admin pages? Do you have there 8 or so transactions?
If that is not the case then you have to check if server side plugins uses connections that they don’t release.
We don’t have transactions in the transaction section. I also don’t have a connection at our database server.
So when we have the active connections problem, the sql server is not connected.
I don’t think there are transactions in use, also not by plugins. But Servoy thinks all connections are in use.
When I restart the application server, in the sql Activity Monitor I see an application with name jTDS is connected.
what do you mean with the sql server is not connected?
There are for sure connections to the database, and with the trace you gave me there are even 2 active queries to the database.
But there are 8 other connections that are there but not used, So if you don’t have any transactions then it is a plugin that’s “eats” them.
What happens if you just up the 10 max active to 30 or 50? Are they then also slowly counting to that number?
We have 22 database servers which are enabled in the application server. So I think the 2 active queries are active on another database. In sql server I can see the connections with the database. When it’s a connection from Servoy it says the application name is jDTS because of the driver/url. I think this is the same when a plugin is connected to the database. This specific database doesn’t have connections with Servoy server at the time of the problem.
What i see is that this one gets a new connection from the pool:
And many others are waiting for that one to end, but maybe somehow this one doesn’t return?
That seems to be a validation query or something. That is send to the database.
@Harjo:
We don’t use streaming plugins. We use: it2be_cryptor, it2be_ftp, it2be_tools, itech_ecommerce, mail, maintenance and WebClientUtils.
@Johan:
How do we make sure that your example is a connection to this specific database server?
In this case the database server IP was 81.149.200.201
I don’t think we get a new connection from the pool when 10 out of 10 connections are active, and that’s the problem.
Isn’t there a timeout setting or something which releases a connection from the pool? It seems like the connections are now stuck forever.
Now for some of the database servers we allow 10 active connections, and for others allow 30. I can change all to allow 30. I expect we will see the same thing but it will take some more time.
I can access the database from sql server without a problem.
some query (a custom query) is getting a connection from the pool
and that connection is then validated with the validation query, and that query was busy and because of that many others that also wanted a connection where waiting on that one
So that gives me the feeling that the validation query takes a lot of time.
But i don’t know what happens for the rest, active connections are active connections where close is not called on. There is no timeout for that because they could be used in transactions that are a bit long.
They just have to be returned to the pool, if that doesn’t happen then something goes wrong by not closing them.
Do you still have that same situation? Then dump the stack again, then we can see if they are still all blocking the same way?
(or the next time you can try this, dump the stack, wait 2 minutes dump the stack again, then we can see if there is movement or not)
But i do see i think that you also use switch server, maybe it has something to do with that, i dont know
For now up the connections limit to 30 or bigger and look what happens then
Yes maybe switch server has got something to do with it. Thanks for your help. At this moment we don’t have the problem because I restarted.
I will change the amount of allowed connections and let you know when we have the same problem again. (and send the stackdumps)
does not return after 4! minutes the validation query to get the connection!!
And that is the first call to that connection factory that wants to create a connection and validate it (the sql query)
Then many others are also coming and also wants to connect to that db server.
But those are all waiting for the first to be validated. So you have the first that sends the validation query to the db, that never seems to return
Then you have 9 others waiting for that…
So you really should look why your validation query doesn’t return.
i don’t know what it is then, it for sure sends a validation query to that db.
Are you really looking at the right server? i see a few switch servers also.