Large issue history takes extremely long time to load

[Błażej O_]

We are experiencing a growing problem with issue history tabs on some certain issues. They load extremely slow - ~5-10 minutes...

There are some issues in our JIRA that have thousands of history records, the biggest one is getting close to 15000 history records. Some of these have mainly linking history, and some have mostly work logging history. Both of these are quite standard JIRA actions.

In our efforts to troubleshoot the problem we have already tried:

• going into safe mode
• setting permission scheme for the whole project to "group (anyone)"
• and in the last desperate move we have injected blank spaces around dashes in the issue keys inside records in the changeitem table in the database (to avoid rendering keys as links in the issue history and rule out permission checking lag)

None of these helped more than a standard deviation of load time...

It also seems strange, because I can't find any mentions of such problem in google.

What can cause such slow loading of issue history?

[Błażej O_ · Accepted solution]

Long story short: the culprits were Message Custom Fields (to edit). For some strange reason they torment database for translation thousands of times while displaying issue history. Removing fields or narrowing their context to other projects/issuetypes caused the issue history to load 3-4 times faster!

How I have discovered it:

I have enabled SQL log and SQL server profiling. After short parsing it became obvious that slowdown is related to Message Custom Fields (to edit). Initial history retrieving takes no more than 30-45 seconds. The rest of the time is thousands of calls to the same set of ~15 field translations:

/browse/OK-XXXX 0ms "SELECT ID, pkey, issuenum, PROJECT, REPORTER, ASSIGNEE, CREATOR, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, WATCHES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT FROM dbo.jiraissue WHERE issuenum='59930' AND PROJECT='10901'"
/browse/OK-XXXX 0ms "SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM dbo.propertyentry WHERE ENTITY_NAME='CustomField' AND ENTITY_ID='11805'"

I have looked up all of the IDs of the fields and all of them where Message Custom Fields for EDIT.

I have tried troubleshooting the problem. I have uninstalled InProduct Translations, removed our native language translation pack. None helped. Last but not least I tried removing some of the MCF fields and narrowing context of the rest and this worked! JIRA stopped flooding database with queries for translations, and all of the issue history retrieval was cut down just to the basic queries for changeitem and changegroups (60-90seconds).

[Nic Brough -Adaptavist-]

It's sheer volume of data - issues really shouldn't get that large, it strongly suggests that you have a broken design or usage pattern for these issues.

You won't be able to fix it by fiddling round the edges (and you really need to undo the damage you've done with those blank spaces, unless you've made the changes to the other tables correctly).  You should probably clone the issue, stick a message like "sorry too big, this is a copy of the raw data without history" on it, delete the problem issue, and then fix your processes so that you don't get into this mess again.

[Błażej O_]

It is large amount of data, that's why query takes 30 seconds to retrieve it. Aren't you curious what does Jira do in the following 9.5 minutes? Don't you think that this is a good enough reason to try to troubleshoot it? I was curious. And I found that it was a defect with Message Custom Field for edit. But sure, it is easier to say "you're using it wrong" than look for and try to solve the problem.

What you call "fixing around the edges", I'm calling "looking under the hood". When my car makes a squeaky noise while turning right I just don't decide that this is a broken usage pattern and I don't plan my way home using only left-turns. I take the car to the mechanic or I look under the hood trying to find any obvious defect that I can fix myself.

Cloning the issues every now and then when users will make too many actions on them - that is a bushfix. And an enterprise level system that basically leaves you no option but to archive issues older than 1 year - that is a broken design. Show my any Atlassian doc that says that you should not make more than 100 links or actions/edits on an Issue?

And you really thought I was fiddling with the production database? Connections between tables (changeitem.groupid = changegroup.id and changegroup.issueid = jiraissue.id) clearly tell there is no dependency on changeitem string https://developer.atlassian.com/jiradev/files/4227160/JIRA61_db_schema.pdf

Nic, You are probably a very experienced developer/administrator, but try to step back and think about problem, not just repeat what everybody does.

When you want to speak authoritative it usually helps to be also right.

[Nic Brough -Adaptavist-]

Think about what you presented to us - a broken process with a serious consequence.  The last time I ran into a serious history, there were no message fields, just a broken process.  Thing is, you don't have a squeaky wheel problem here - you've got a squeaky wheel and you're trying to use your car as a food mixer.

Yes, your investigations found a problem that exarcerbates the problem of slow loading - well done, squeaky wheel fixed.  It doesn't change the fact that an issue with that level of history is a symptom of a broken process.

And, you explicitly said you were fiddling with the database.  That's why I jumped at that one.  If you did it some other way, via the API, fantastic, but that's not what you said.