Problem with MySQL database connections.

[Ramesh Udari1]

Hi,

We are facing some issues related to database connection that is when we checked the number of connections made by JiRA application were around 100 and application becomes unresponsive and not able to make connection with database automatically.
However,restarting of application solves the problem.this is happening frequently.

Here my question is that how can we find out which service,listener,plug-in,queries and etc are consuming more connection and causing problem?

if anybody knows solution about this,kindly help on this issue.

[Dieter]

Using a professional Java profiler like JProfiler or Wiley will easily show you the portions of code that make heavy use of JDBC calls.

In reallity i almost never need these tools since a stacktrace produced by the command jstack (included in the Oracle JDK) shows you were the Tomcat process spends most of it time.

Taken several traces in intervals of some seconds usually should show you were JIRA "spends" it's time.

[Radu Dumitriu]

First, limit the number of connections to your MySQL database, or configure MySQL to accept a greater number of connections.

On Jira side, you will change this from tomcat configuration file (JIRA_INSTALL/conf/server.xml)

//server.xml

<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
username="..............."
password=".........."
driverClassName="................"
url="......................."
maxActive="50"
/>

On MySQL side, in my.cnf

[mysqld]
max_connections = 1000

[Ramesh Udari1]

Hi Dumitriu,

Thanks for your solultion on this iisue.

Definetly we ill try as you suggested above.But one thing i want to know that how do we find out which service,listener,plug-in,queries and etc are consuming more connection and causing problem?.

[Radu Dumitriu]

Here's how:

bool list_plugins_done = false;

while(!list_plugins_done) {

if(lots of connections) {

dump java memory

fire up hat (Heap Memory Analizer) - or some tool of your choice

go to the DBCP container

check the first plugin class that borrowed a connection

compile the list of pluginsa with number of connections borrowed

if(satisfied()) {

list_plugins_done = true;

}

}

}

The problem is that it is very complicated to know exactly who is database intensive. Some plugins may go directly on the database, some may have even custom pools, some will mix up with Jira connection pool. I would check each plugin configuration (even go like disable - enable cycles if you suspect that one plugin is gone wild)

[Ramesh Udari1]

Below is the error in the log file:

2011-08-24 15:35:18,802 TP-Processor403 ERROR [500ErrorPage.jsp] Exception caught in 500 page org.ofbiz.core.entity.GenericDataSourceException: Unable to esablish a connection with the database. (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: Unable to esablish a connection with the database. (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
Caused by: org.ofbiz.core.entity.GenericDataSourceException: Unable to esablish a connection with the database. (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
2011-08-24 15:35:27,154 TP-Processor466 ERROR [atlassian.jira.upgrade.UpgradeManagerImpl] org.ofbiz.core.entity.GenericDataSourceException: Unable to esablish a connection with the database. (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)