Win a copy of Functional Reactive Programming this week in the Other Languages forum!
    Bookmark Topic Watch Topic
  • New Topic

Need to check no of connections used by datasource

 
Ajay Kamble
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 9319
10
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Report post to moderator
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?
 
Ajay Kamble
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 9319
10
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 9319
10
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Posts: 251
5
  • Mark post as helpful
  • send pies
  • Report post to moderator
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.
 
Joe Ess
Bartender
Posts: 9319
10
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Report post to moderator
Please use only one thread per topic. Posting more than one topic leads to confusion and duplication of effort.
UseOneThreadPerQuestion
 
    Bookmark Topic Watch Topic
  • New Topic