-
Suggestion
-
Resolution: Unresolved
-
None
-
3
-
Summary
1) When moving an issue between projects or bulk moving multiples issues, we can observe that the query below consumes much of the active connection pool and db cpu.
- Report from DB:
"SELECT OLD_ISSUE_KEY FROM moved_issue_key WHERE ISSUE_ID=:1 ORDER BY ID"
Id Operation Name Rows Bytes Cost (%CPU) Time 0 SELECT STATEMENT 84 (100) 1 SORT ORDER BY 1 21 84 (3) 00:00:01 2 TABLE ACCESS FULL MOVED_ISSUE_KEY 1 21 83 (2) 00:00:01
Query is doing full table scans- moved_issue_key table structure:
Table "public.moved_issue_key" Column | Type | Modifiers ---------------+------------------------+----------- id | numeric(18,0) | not null old_issue_key | character varying(255) | issue_id | numeric(18,0) | Indexes: "pk_moved_issue_key" PRIMARY KEY, btree (id) "idx_old_issue_key" UNIQUE, btree (old_issue_key)
2) Customers facing issue JSWSERVER-20612 Dev.Status can get stuck paging over issue updates from Bitbucket Server, Bamboo or Fecru and cause excessive load on database can minimize the DB impact by adding this index:
CREATE INDEX idx_issue_id ON moved_issue_key (issue_id);
Suggested Solution
Add an index on the issue_id column for the table moved_issue_key
Workaround
Add index manually
CREATE INDEX CONCURRENTLY idx_issue_id ON moved_issue_key (issue_id);
[JRASERVER-70678] As a Jira Admin, I would like to add an index by ISSUE_ID column on the table moved_issue_key
Hi!
On the basis of JavaMelody in our Jira production instance sql selects SELECT OLD_ISSUE_KEY FROM moved_issue_key WHERE ISSUE_ID=? ORDER BY ID count for 14% of cumulative execution time for all selects.
I bet it's 400,000 (https://en.wikipedia.org/wiki/Lakh)
We have 283,000 rows in moved_issue_key right now
x-post from
JRASERVER-38598Hi, we recently found out a missing index in the database, which caused some searches to take a huge amount of time during the full reindexing task. Full reindex ran then 1,5h instead of the previous 3h.
Gathered on side of the database:
SELECT OLD_ISSUE_KEY FROM public.moved_issue_key WHERE ISSUE_ID=$1 ORDER BY ID
we added index:
CREATE INDEX CONCURRENTLY idx_issue_id ON moved_issue_key (issue_id);
Pavel