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?
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?
Joined: Jan 23, 2009
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?
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.
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.