Test database performance for Jira Server

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

This KB article assumes that you already have a JIRA instance running, using a supported database. For more information on the installation and configuration of the database, as well as any information related to the driver location and other database-side configurations, you can check the documentation Connecting JIRA applications to a database.

Additionally, it is important to note that this tool was created for JIRA 6.x and older, and it is unsupported.

Problem

JIRA applications appear to be experiencing performance issues. This will be particularly noticeable in the following areas:

  • Reindexing
  • Display of issue details and comments

Diagnosis

This tool only measures retrieval latency of key JIRA applications database objects. It does not simulate real-life activities such as permission checking that may significantly increase processing time. Thus it is not recommended to use this tool as an overall instance performance estimator.

  1. Download atlassian-log-analysis-0.1.1.jar.

    • (warning)This is a file with a .jar extension. Sometimes Internet Explorer renames these to .zip. If this happens, rename the file to .jar; do not unzip the file.

  2. Open a terminal window.
  3. Run this command after replacing all the variables with the correct values for your environment from JIRA_HOME_DIR/dbconfig.xml. The '\' and '^' in the examples are used to break the command across multiple lines.
    (info) The JDBC driver jar should be in the directory <Jira-install>/lib

    Unix
    java -cp PATH_TO_THE/atlassian-log-analysis-0.1.1.jar:PATH_TO_YOUR_JDBC_DRIVER_JAR \
    com.atlassian.util.benchmark.JIRASQLPerformance \
    YOUR_DB_USERNAME YOUR_DB_PASSWORD \
    JDBC_CONNECTION_STRING JDBC_DRIVER_CLASS \
    > db-perf-test.txt
    Windows
    java -cp PATH_TO_THE\atlassian-log-analysis-0.1.1.jar;PATH_TO_YOUR_JDBC_DRIVER_JAR ^
    com.atlassian.util.benchmark.JIRASQLPerformance ^
    YOUR_DB_USERNAME YOUR_DB_PASSWORD ^
    JDBC_CONNECTION_STRING JDBC_DRIVER_CLASS ^
    > db-perf-test.txt

    Here is an example of running the tool with a MySQL database:

    java -cp ./atlassian-log-analysis-0.1.1.jar:/home/idaniel/tools/jdbc_drivers/mysql-connector-java-3.1.12-bin.jar \    
        com.atlassian.util.benchmark.JIRASQLPerformance \
        jirauser jirauser 'jdbc:mysql://localhost:3306/icfi?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8' \
        com.mysql.jdbc.Driver \
        > out.txt

The results are placed in the file "db-perf-test.txt". You may see some errors when you run it but you can ignore those unless the output file is empty.

This is what the summary at the end of the output looks like:

TOTALS
----    ----    ----    ----    ----
stat    mean    median  min max
----    ----    ----    ----    ----
retrieve-issue  5,338,000   979,000 213,000 46,007,000
get-issue   174,775 93,000  62,000  11,621,000
retrieve-workflow   5,117,153   607,000 341,000 47,738,000
get-workflow    98,996  64,000  40,000  2,962,000
retrieve-custom-field-value 601,093 495,000 316,000 23,082,000
get-custom-field-value  91,246  52,000  37,000  3,453,000
----    ----    ----    ----    ----
All times are in nanoseconds.

We do not have clearly defined "excellent, good, bad" ranges but in a large installation we look for mean values below  20,000,000 nanoseconds for all metrics and below 10,000,000 nanoseconds is ideal. The faster the better. Remember to keep in mind that the performance testing tool here is outputting values in nanoseconds, not milliseconds. 

Test stages

Please note: multiple databases might have different behaviors due to driver implementation. Do not compare the different database types with each other using this test.

As noted above, the tests do not attempt to fully simulate user activity. Below are relevant SQL snippets from the source code. It may also be helpful to log SQL queries from the DB side while running the utility for better insight into what it is measuring.


 Test nameSQL statement Action type

Init

SELECT count(*) FROM jiraissue
SELECT id FROM jiraissue

Preloading test
metadata
  
Issue test
retrieve-issue 
SELECT * FROM jiraissue
WHERE id = <random issue id>

Executing call against
random issue

get-issue
-Caching the result


Workflow test

retrieve-workflow
SELECT * FROM OS_CURRENTSTEP
WHERE entry_id = <random workflow id>
Executing call against
random workflow
get-workflow
-Caching the result 



Custom field test



retrieve-custom-field-value
SELECT 
ID, ISSUE, CUSTOMFIELD, PARENTKEY,
STRINGVALUE, NUMBERVALUE, TEXTVALUE,
DATEVALUE, VALUETYPE
FROM customfieldvalue
WHERE ISSUE = <random issue id>


Executing call against
random custom field
get-custom-field-value
Caching the result


Cause

There are many potential causes for poor database performance. First, check that your DB configuration is correct as per the JIRA documentation. To successfully address DB performance problems it is essential to consult with your database administrator.

Other things to check

  • DB latency. If no specific tool is available, use ping between the app server and the DB server and average the time of 20 pings.
  • Driver version. Make sure it is the latest recommended for your DB version.
  • In some environments, it may be helpful to host the DB locally. This mainly applies to installations where the underlying server has plenty of RAM, disk space, and CPU without cutting into JIRA application resources.






Last modified on Dec 9, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.