aspose file tools*
The moose likes Tomcat and the fly likes Need to check no of connections used by datasource Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Tomcat
Reply locked New topic
Author

Need to check no of connections used by datasource

Ajay Kamble
Ranch Hand

Joined: Jan 23, 2009
Posts: 51
Hi All,

We are using Tomcat 5.5.7. We have configured datasources in server.xml.

Recently, we have observed increase in user activity/no of transaction and we frequently get, Error is: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool exhausted

We incrased no of maxActive connections available but still this error appears.

We need to check how many connections are being used at any time by application. If really all connections are being used then we can consider to increase no of connections further or any other strategy.

Anyone knows how to get these numbers (no of connections being used at any moment) in real time?

Regards,
Ajay


Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8927
    
    9

netstat should tell you if you are using plain sockets.
Make sure you are closing all your database connections in a finally block. see here.
What database are you using?


"blabbing like a narcissistic fool with a superiority complex" ~ N.A.
[How To Ask Questions On JavaRanch]
Ajay Kamble
Ranch Hand

Joined: Jan 23, 2009
Posts: 51
Hi Joe,

We are using MySQL 4.1.7.

Joe, thanks for your tip but we do not have issues with finally.

We are experiencing this because of high user activity recently. The number of max connections are easily consumed because of this and we face problems.

But we need to confirm it with some utility. It will be better if we can verify that all connections are consumed, so that we can provide a proper fix.

Regards,
Ajay

Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8927
    
    9

MySql Administrator can track connections, but I don't know if a version is available for 4.1.7 (the version I linked to is for 5).
How many DB connections do you have configured?
How many concurrent users do you have?
Did you try netstat?
Ajay Kamble
Ranch Hand

Joined: Jan 23, 2009
Posts: 51
Joe,

We are using 30 max connections.

It is difficult for me to give no of concurrent users.

I tried netstat, am I supposed to count all mysqld processes? Will that give me no of connections?

Regards,
Ajay
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8927
    
    9

Tomcat uses DBCP for a connection pool. It gives you a couple of options as far as configuring pool size, for example:
initialSize = The initial number of connections that are created when the pool is started.
maxActive = The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
maxIdle = The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.
minIdle = The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none.

You could make maxActive very large (or even no limit) to allow for spikes in traffic, but make maxIdle some reasonable number to handle normal traffic efficiently. The exact values depend on your particular application.

netstat lists network connections, not processes. Run it (netstat -ab) and count the number of connections that are "established" with your database server.
Kees Jan Koster
JavaMonitor Support
Rancher

Joined: Mar 31, 2009
Posts: 251
    
    5
Dear Ajay,

To track the number of open connections over time you need some form of monitoring tool. If you have such a tool already, you can enable JMX in Tomcat and hook up your monitoring tool. If you don't have a monitoring tool yet, check out Java-monitor. It can give you a graph of the actual open connections over the past two days.

If you go to Java-monitor's live demo, you can see what that looks like. Just click on "another server @ java-monitor.com" and scroll down to "Database Connection Pools" (About halfway down the page). Here are Java-monitor's installation instructions. Should take you no more than 5 minutes to get going.

Again, if you already have something like Nagios, Zabbix or another monitoring tool, hook your Tomcat up to that system.

Java-monitor, JVM monitoring made easy <- right here on Java Ranch
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8927
    
    9

Please use only one thread per topic. Posting more than one topic leads to confusion and duplication of effort.
UseOneThreadPerQuestion
 
Consider Paul's rocket mass heater.
 
subject: Need to check no of connections used by datasource