Intermittent slow operations in Jira

Question

We're seeing some issues with our Jira instance running in a Docker container and connecting to an Oracle database, where some operations intermittently take 103-105 seconds. The rest of the time the operations are fast.

We've used the com.atlassian.util.benchmark.JIRASQLPerformance tool mentioned on this page  https://confluence.atlassian.com/jirakb/testing-database-access-speed-54362302.html

It always seems to pause after 241 - 243 iterations for around 103-105 seconds before continuing on for the remainder of the 1000 iterations. Apart from the one isolated operation that takes the significant amount of time the rest are all fast enough.

It's not always the same operation the takes the time, we've seen it for these ones retrieve-custom-field-value, retrieve-issue, retrieve-workflow

I've turned on HTTP Access and SQL logging in JIRA itself and we see the same 103-105 seconds duration for different operations on the application itself.

Anyone got any ideas for where to look further for the cause of this issue?

Former user · Answer

Following on from this observation I wrote a very simple test using java to open a JDBC connection and repeatedly run a simple sql query (select 1 from dual;) on the same connection and it exhibits a similar duration of delay (i.e. 103-5 seconds) consistently after 700 executions of the query. Does this sound familiar with anyone connecting JIRA server to a remote Oracle instance?

The details for our Oracle are:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options

Former user · Answer

Yes, we are using reverse proxy.

We're using Jira 7.5.0 so if I read that issue correctly the use of NIO or BIO shouldn't be affecting us as it was fixed in 7.3.0, which was the version we were using before I upgraded to 7.5.0 to see if that resolved the problem we are seeing, i.e. we were seeing the intermittent slow request in both version 7.3.0 and 7.5.0

I see the same duration of delay in one iteration (242 in the fragment below) when running the database benchmark tool, which runs standalone outside of Tomcat

Benchmark: JIRA SQL: jdbc:oracle:thin:@//......

# retrieve-issue get-issue retrieve-workflow get-workflow retrieve-custom-field-value get-custom-field-value
0 31,745,845 8,418,640 4,543,946 204,349 2,084,790 186,066
1 6,287,975 351,819 15,988,177 177,309 2,084,152 172,793
2 2,196,395 345,121 3,918,294 173,620 2,465,126 155,978
3 2,221,105 557,835 6,336,782 159,062 1,832,083 170,599
4 2,224,824 418,687 2,358,519 156,552 1,943,315 154,165
5 2,023,657 377,336 2,660,768 175,233 2,687,176 166,361

241 1,959,677 168,769 2,008,795 73,850 1,852,165 73,863
242 1,961,429 192,916 103,740,341,579 162,992 2,164,068 78,474
243 2,286,128 178,090 2,926,292 77,721 3,884,616 79,882

995 1,769,611 79,567 1,907,528 36,813 2,034,270 35,538
996 2,115,322 78,201 1,946,795 37,127 2,055,872 46,200
997 2,557,614 68,817 2,224,808 39,541 1,907,588 35,506
998 1,961,407 80,540 1,893,375 48,496 2,040,883 50,024
999 2,210,110 188,335 1,897,955 38,000 4,126,431 47,407

TOTALS
---- ---- ---- ---- ----
stat mean median min max
---- ---- ---- ---- ----
retrieve-issue 2,289,091 2,063,893 1,708,606 31,745,845
get-issue 194,404 124,400 64,019 13,625,407
retrieve-workflow 105,857,590 2,006,735 1,648,273 103,740,341,579
get-workflow 77,035 63,552 34,801 2,184,998
retrieve-custom-field-value 2,067,199 1,969,682 1,679,869 10,960,532
get-custom-field-value 94,841 60,835 33,861 17,919,877
---- ---- ---- ---- ----
All times are in nanoseconds.

Mizan · Answer

So this may not be a memory issue.

100 seconds is a lot of time for these requests. What were the recent changes done on JIRA . Did you install any add-on.

Are you using any reverse proxy ?

Can you ensure you are not impacted by this , I suspect you are not using NOI and BIO is causing this

https://jira.atlassian.com/browse/JRASERVER-62745