nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

What is a good benchmark for the DB access speed test?

Kamil Beer

Hello,

the title says it all. I have been checking out the page https://confluence.atlassian.com/jirakb/testing-database-access-speed-54362302.html and while I was able to run the test, the numbers don't really tell me much, as I do not have a baseline to compare it to.

On the page, there is "We do not have clearly defined "excellent, good, bad" ranges but in a large installation we look for mean values below 20ms for all metrics and below 10ms is ideal. The faster the better." The paragraph is about miliseconds and the results are defined in nanoseconds... is that a typo or not?

There is also an increase by an order of magnitude of the numbers in the lines beginning in "retrieve", ex.

----   ----   ----   ----   ----
stat   mean   median   min   max
----   ----   ----   ----   ----
retrieve-issue   220,973   192,981   169,149   3,970,467
get-issue   50,676   33,986   17,001   2,845,513
retrieve-workflow   208,766   182,322   159,930   4,280,980
get-workflow   26,421   17,896   13,099   322,557
retrieve-custom-field-value   201,820   176,722   157,086   6,113,428
get-custom-field-value   26,985   17,667   13,047   1,791,059

Why is that? That is quite a large discrepancy.

Accepted answers

Andy Heinzer

The test utility generates values in nanoseconds. Yes the guide mentions milliseconds. This is not a typo. Just understand that 1,000,000 nanoseconds = 1 millisecond.

You can actually run the same SQL queries yourself against your own database. Depending on your version of SQL, your database might return a total time to run a query in seconds, milliseconds, or even nanoseconds.

The 'retrieve' calls are pulling more data than these previous simple selects. The other selects are either just doing a simple count or returning a single field. But the others are doing a select * from those tables, which will return all the fields for the records(s) that match. The more data/records there are to retrieve the longer this will take for any connection to the database.

Are you seeing performance problems in regards to using Jira with your database? Can you tell us more about your environment? Type/version of database? Hardware setup of the database server? network connection etc?

All comments

Andy Heinzer

The test utility generates values in nanoseconds. Yes the guide mentions milliseconds. This is not a typo. Just understand that 1,000,000 nanoseconds = 1 millisecond.

Kamil Beer

Hello Andrew,

thanks for the reply and clarification. I do not have any current DB problems, I would just like to know an appropriate benchmark to compare the results to if I decide to troubleshoot DB problems in the future. If said benchmark is that every mean should be lesser than 20ms, or ideally 10 ms (on large or small instances), I can use that. Is that true?

Andy Heinzer

I believe that is correct. For the Large scale Jira instance in the Sizing Guide, there is an expectation that you would be using 10,000 rpm hard drives, or solid state disks, dual quad core processors, and more than 16GB of system memory.

But that said, each environment can be different in terms of resources. And since different databases can have different ways of optimizing certain calls like this, there can be a lot of variance in these results.

Kamil Beer

Thanks!

Stefan Mohr

Hi,

I think the test is good.
What I don't understand is why 14,166s are displayed even though the whole test only took 2s.

# retrieve-issue get-issue retrieve-workflow get-workflow retrieve-custom-field-value get-custom-field-value
0 14.166.818 934.456 2.176.708 257.141 345.702 215.232

TOTALS
---- ---- ---- ---- ----
stat mean median min max
---- ---- ---- ---- ----
retrieve-issue 215.128 133.513 104.536 14.166.818

Best regards

Stefan

Stefan Mohr

I think the error is in the above conversion of "1,000 nanoseconds = 1 millisecond".

1.000.000ns = 1ms
14,166,818ns are therefore 14.166818ms

Maybe it would be better to specify everything from the documentation to the output of the program in ms.

Best regards

Stefan

Andy Heinzer

Hi Stefan,

Thanks for this. Just looked it up to be sure and my previous answer was incorrect here. Yes there are actually 1 million nano seconds in a millisecond, and not the 1,000 my answer previous indicated. I updated my previous answer to clarify this. Although updating that .jar file isn't something I have access to manage.

But I agree that having the KB using ms and the jar file using nano seconds is a source of confusion over the results.

Andy

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server

performance

database