Details
-
Bug
-
Resolution: Fixed
-
Medium
-
6.4.3, 6.4.5, 6.5.0
-
None
-
6.04
-
-
Description
LexoRank queries performance is really slow.
This query:
public LexoRankRow[] getMaximumMarkerRowAndPreviousRow(long fieldId, LexoRankDaoContext ctx) { SqlSelectStatement selectStatement = SqlSelectStatement.builder(ctx.getEntityDetails(), ctx.getDatabaseType()) .select("*") .where(fieldIdColumnName + " = ?", fieldId) .orderBy(rankColumnName, DESC) .orderBy(idColumnName, ASC) .limit(2) .build(); .... }
performs with no index.
Options:
Option 1:
I tried to add a composite index in the table like this :
create index index_ao_60db71_field_rank on AO_60DB71_LEXORANK(FIELD_ID , RANK DESC, ID ASC);
And this improved the performance in 2x.
The problem with the composite index is everytime JIRA starts AO realises that there is an index unmapped and fails to start.
Option 2:
The other way would be to cache the query result by FIELD_ID to keep those for a certain amount of time and invalidate if necessary. It would be good
That query is made 4 times in a create issue action so the impact is high.
Option 3:
The big problem in this query is the order by id asc.
Removing that part of the query improved the performance from 2sec to 0.37 sec. So trying to fix that will give the most significant performance without doing any mess in the code
I think Option 3 is the wisest choice here.
Samples of the queries:
mysql> select * from AO_60DB71_LEXORANK where FIELD_ID = 15182 ORDER BY RANK DESC, ID ASC LIMIT 2; +----------+---------+---------------------+--------------------------------------+---------------+-----------+------+ | FIELD_ID | ID | ISSUE_ID | LOCK_HASH | LOCK_TIME | RANK | TYPE | +----------+---------+---------------------+--------------------------------------+---------------+-----------+------+ | 15182 | 7 | 9223372036854775807 | a4eb31f2-52d6-43cd-a8e3-1e6054aa5310 | 1409728825802 | 0|zzzzzz: | 2 | | 15182 | 2046534 | 746652 | a4eb31f2-52d6-43cd-a8e3-1e6054aa5310 | 1409728825802 | 0|101rkz: | 1 | +----------+---------+---------------------+--------------------------------------+---------------+-----------+------+ 2 rows in set (1.96 sec) mysql> select * from AO_60DB71_LEXORANK where FIELD_ID = 15182 ORDER BY RANK DESC LIMIT 2; +----------+---------+---------------------+--------------------------------------+---------------+-----------+------+ | FIELD_ID | ID | ISSUE_ID | LOCK_HASH | LOCK_TIME | RANK | TYPE | +----------+---------+---------------------+--------------------------------------+---------------+-----------+------+ | 15182 | 7 | 9223372036854775807 | a4eb31f2-52d6-43cd-a8e3-1e6054aa5310 | 1409728825802 | 0|zzzzzz: | 2 | | 15182 | 2046534 | 746652 | a4eb31f2-52d6-43cd-a8e3-1e6054aa5310 | 1409728825802 | 0|101rkz: | 1 | +----------+---------+---------------------+--------------------------------------+---------------+-----------+------+ 2 rows in set (0.37 sec)
Diagnosis
- Run the profiling, as in enabling profiling documentation
- Create a JIRA issue to reproduce the problem
- Disable profiling log to prevent spamming application log file
- In the atlassian-jira.log, you should see something like the following
2014-09-01 20:29:05,992 http-bio-2969-exec-5 DEBUG jiraadmin 1228x9018x1 3t9mem 172.22.236.xxx /secure/QuickCreateIssue.jspa [atlassian.util.profiling.UtilTimerStack] [21376ms] - /ghs-12926/secure/QuickCreateIssue.jspa [0ms] - PermissionManager.hasPermission() [21373ms] - QuickCreateIssue.execute() [28ms] - PermissionManager.getProjects() [0ms] - List.contains() ..... [17057ms] - IssueIndexManager.reIndexIssueObjects() .....See the IssueIndexManager.reIndexIssueObjects() spiking to 17s