要約

The queues page of a project loads very slowly. Other queues pages load quickly and no such slowness is perceived elsewhere in Jira.

The problem is unrelated to how many queues the page has and how many issues are in each queue. Ie. a page with 2 queues and 20 total issues may load slower than a page with 10 queues and 1.000 total issues.

環境

Jira Service Management 3 or 4.

診断

The main suspect for a slow queue page is one or more slow JQL queries that compose the queues. This is how we detect them:

1) Search for slow queries

Every slow query is printed on the atlassian-jira-slow-queries.log, and we should scan it for execution times with 5 digits or more (10000 milliseconds):

$ egrep "/PRJ/.*sd-queues-nav" <jira-home>/log/atlassian-jira-slow-queries.log | grep "username:USER-NAME" | egrep -o "executionTime=[[:digit:]]{5}"

Replace on the command above (and others on this KB):

• PRJ by the project key which queue page is slow
• Jira-HOME by the Jira home directory on the server
• USER-NAME by the username loading the queues page 

We expect the output to be similar to the example below:

executionTime=13185
executionTime=12387
executionTime=11231
executionTime=56229
executionTime=14569
executionTime=12909

In which we'd have (by the example) 5 queries around 11 and 15 seconds and one almost a minute long (executionTime in milliseconds).

We may also search for queries that take between 1 and 10 seconds (exactly 4-digit execution time):

$ egrep "/PRJ/.*sd-queues-nav" <jira-home>/log/atlassian-jira-slow-queries.log | grep "username:USER-NAME" | egrep -o "executionTime=[[:digit:]]{4},"

原因

Slow JQLs impact the queue page load time because Jira waits for all queues to finish loading and bring the issue count on each.

There are some issues filed on jira.atlassian.com:

JSDSERVER-6424 - 課題情報を取得中... ステータス

JSDSERVER-6626 - 課題情報を取得中... ステータス

ソリューション

The solution to this problem is to work on the slow queries to optimize them or break a queue in two or more with faster queries.

Jira Service Management queues were designed to show issues to be worked on. For historical data  — like "issues resolved" — a Dashboard might be a better solution than a queue.

1) Identify the slow queries

We're now interested in the slow queries search terms. The commands below can be used to that purpose:

$ egrep "/PRJ/.*sd-queues-nav" <jira-home>/log/atlassian-jira-slow-queries.log | grep "username:USER-NAME" | egrep -o ".*executionTime=[[:digit:]]{5}.*" | egrep -o "{query=.*?, queryTermMetrics" | sort | uniq -c

$ egrep "/PRJ/.*sd-queues-nav" <jira-home>/log/atlassian-jira-slow-queries.log | grep "username:USER-NAME" | egrep -o ".*executionTime=[[:digit:]]{4},.*" | egrep -o "{query=.*?, queryTermMetrics" | sort | uniq -c

This will print the slow queries statements in a syntax similar to the JQL, preceded by the number of times the query appeared in the log.

2) Adjust the queries

Unfortunately, there's no link between the log and the exact queue, so we need to go through each queue JQL on the slow page.

The command output from step 1 above should make it easier to identify the slow queues, though.