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.

[Andy Heinzer · Accepted solution]

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?

[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