Testing database access speed

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.Thusit 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.

    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&useUnicode=true&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 20ms for all metrics and below 10ms is ideal. The faster the better.

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.

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.

-- JIRA issue tests
SELECT count(*) FROM jiraissue
SELECT id FROM jiraissue
SELECT * FROM jiraissue WHERE id = ?
-- workflow tests
SELECT * FROM OS_CURRENTSTEP WHERE entry_id = ?
-- custom field value test
SELECT ID, ISSUE, CUSTOMFIELD, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue WHERE ISSUE=?

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.

Was this helpful?

Thanks for your feedback!

Why was this unhelpful?

Have a question about this article?

See questions about this article

Powered by Confluence and Scroll Viewport