On large instances the createmeta REST endpoint can take a very long amount of time to return results or cause JIRA to OOM

Summary

When issuing requests to /jira/rest/api/2/issue/createmeta?projects.issuetypes on larger instances, in some cases the call can take over a minute to complete and generate 115mb of content.

With fields added (createmeta?projects.issuetypes.fields), JIRA can run out of memory if its Xmx is too low.

Environment

The following data exists in the test instance:

Database Statistics
Issues          417547
Projects        1481
Custom Fields   1203
Workflows       324
Attachments     0
Comments        2921131
Users           17225
Groups          985

And 323 issue types.

Steps to Reproduce

1. Setup or use an instance with similar data sizes.
2. Query /rest/api/2/issue/createmeta?projects.issuetypes, for example https://jira.atlassian.com/rest/api/2/issue/createmeta?projects.issuetypes.

Expected Results

The query returns in a reasonable timeframe.

Actual Results

The query takes ~60 secs as per the attached screenshot, or in the case of createmeta?projects.issuetypes.fields runs out of memory entirely.

Possible cause and room for improvement

Duplicate content

We have seen that in some cases there are logical duplicates in the optionconfiguration table. This, depending on the number of affected issue types, can cause performance issues itself. To check for such, you can run the following query:

SELECT fieldid,optionid,fieldconfig, count(*) FROM optionconfiguration group by fieldid,optionid,fieldconfig having count(*)>1

If the above returns any rows, you are likely affected by the problem. In this case, please raise a ticket on https://support.atlassian.com, referring to this bug report and ask for assistance on de-duplicating the table.

OOM

The only workaround for this behaviour is to increase JIRA's maximum heap. There's no hard-and-fast rule for what to increase it to, as it's a function of the number of projects, custom fields, issue types, and contexts for each.