connection pool exceptions - Continuition

[srinivasp]

I posted a question couple of minutes back and am contining here since i am unable to access that post.

Hi Dieter,

Please find the threaddump attached. I am unable to attach the complete dump but i found nearly 150 threads blocked. (dump1.txt)

[Radu Dumitriu]

In the absence of the thread dump, here is a wild educated guess:

1) 300 connections are way too much. You may have messages on ORCL side that it is in a kind of limbo.

2) Remove abandoned has something to do with your deadlock (remove abandoned is called AFAIK from another thread that the one that aquired the connection). The ORCL drivers are sensitive to these kinds of manipulation.

Actions:

1) Delete these lines

<removeAbandoned>true</removeAbandoned>

<removeAbandonedTimeout>600</removeAbandonedTimeout>

2) Edit this line:

<pool-size>35</pool-size>

3) Restart Jira & retest

[srinivasp]

Hi Dumitru,

Thanks for your suggestion. As we are frequently getting connection timed out exceptions, atlassian support guys suggested this.

[JamieA]

I would also remove the validation query.

[srinivasp]

We migrated two weeks back and observed this issue. We had similar configuration except the removeAbandoned property and there was absolutely no issues with the previous jira version.

[Radu Dumitriu]

Ah, that's news. ORA-03136 ? One should check the network listener on ORCL as well as the network conditions. Keeping the number of connections at suggested levels can only help in this case.

[Radu Dumitriu]

My name is Radu. Yes, 150 threads, 35 database connections.

[srinivasp]

Hi Dumitriu,

Do you mean the pool size attribute to 35? Also what about the maxThreads? Currently we have 150 mentioned inour server.xml file. Is that ok?

[srinivasp]

Sorry Radu, I just copied your last name from this post and using it.

[Radu Dumitriu]

No probs.

Really, I do not think you have a deadlock. The behavior is just that Jira is waiting too much to create a connection.

You need to look at the following:

a) JDBC driver (oracle has released some really bad drivers lately)

b) sqlnet.log (or whatever) for the ORCL listener (maybe authentication takes a long time, maybe there are not configured enough sockets on accept, etc)

c) network between your Jira server and ORCL server. Does "telnet orclhost 1571" responds in no time ?

[srinivasp]

I am adding sql statistics observation from melody plugin.

[srinivasp]

Adding dumps..

(dump1.txt)

[srinivasp]

(dump2.txt) threaddumps

[srinivasp]

(dump4.txt) this is annoying.. no able to attach a complete dump

[srinivasp]

Hi Radu, did you get a chance to look at the dumps i attached??

-Srinivas

[Radu Dumitriu]

Yes. They still not contain the info I need. Search yourself after the string "locked <0x000000059a5bf828>" in your dump and attach that thread.

[Radu Dumitriu]

1) 150 threads and 300 connections ? This is wrong. N connections < N threads (one thread should use at most one connection).

2) From your partial thread dump, I see that the threads are blocked on the monitor

waiting to lock <0x000000059a5bf828> (a org.apache.commons.dbcp.PoolableConnectionFactory) on validate.

2.a) you need to look in your thread dump for the string: locked 0x000000059a5bf828 (same address as above). Please
 show us that thread, this is the one that is blocking everything
2.b) you need to look a bit over the configuration of the DBCP datasource. Are you using validation? testWhileIdle ? Would 
be helpful to show us the configuration, too
2.c) Disable any 3rd party plugins before doing these tests.

[JamieA]

That's a good point... I found using a validation query made things massively slower (on oracle at any rate). The validation query is run before every single normal query. As Radu says 300 connections seems way too much... I manage a jira site with 10k users and the connection pool is 30.

[srinivasp]

 HI Radu/Jamie, Please find the db configuration below.

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>oracle10g</database-type>
<jdbc-datasource>
<url>jdbc:oracle:thin:@xxxxxxxxxxxxxx:1571:JIRAPROD</url>
<driver-class>net.bull.javamelody.JdbcDriver</driver-class>
<connection-properties>driver=oracle.jdbc.OracleDriver</connection-properties>
<username>jira44app</username>
<password>jira44app</password>
<removeAbandoned>true</removeAbandoned>
<removeAbandonedTimeout>600</removeAbandonedTimeout>
<pool-size>300</pool-size>
<connection-properties>SetBigStringTryClob=true</connection-properties>
<validation-query>select 1 from dual</validation-query>
</jdbc-datasource>
</jira-database-config>

[Radu Dumitriu]

How about that thread dump ?

[srinivasp]

I guess i already attached thread dumps. May i know which dump you are asking about?

[Radu Dumitriu]

Your dumps : dump1 is partial, dump2 is useless. We need info on locked objects, as explained.

[srinivasp]

The file is huge and so unable to upload the actual one. So i uploaded the the partial one. Can you suggest a way to upload the actual dump i took at the time of system hanged?

[Radu Dumitriu]

......zip ?

[srinivasp]

Zip is not allowed to attach here.

[srinivasp]

Hi Jamie, We redced the pool size to 50 and also handled some other http session attributes. But in the melody plugin the used jdbc connections is observed like below. Maximum it is showing is 9 and Mean is 8. Will that impact in peak hours and what does it means?

[srinivasp]

Hi Jamie, One quick question... What is ideal session time out for Jira and Confluence?

[srinivasp]

i copied the threads from the pdf. Please find the attached. (dump2.txt)

[srinivasp]

Hi Jamie,

Please add your comments to this post. I am unable to open the actual post(https://answers.atlassian.com/questions/52523/connection-pool-exceptions) when i click the link on your comments email. This is another issue i posted to atlassian. Melody plugin is already installed and I am analysing it and so far no luck yet. I cant attach here because it is huge.

Hi EvaLee,

I am using Jira 4.4.5 with oracle database.

Thanks

[JamieA]

With java melody you should see two thread dumps for every connection, one is from when the connection was created, and one is what it is currently doing. The one that created it is the interesting one.

[JamieA]

I don't think you're going to get a correct answer based on the information in your question.

But, if you install the java melody plugin, it will show you all the database connections, along with stacktraces from what created the connection. Using this, you can find out which plugin (it probably is a plugin) is creating them.

[srinivasp]

Hi Jamie, I found this -XX:+HeapDumpOnOutOfMemoryError in the vm arguments information. Will it be the cause?(systeminfo.txt)