JIRA inefficiently populates fieldLayoutCache due to slow loading of FieldLayoutItems

Summary

JIRA inefficiently populates fieldLayoutCache due to slow loading of FieldLayoutItems. JIRA loads elements into fieldLayoutCache one by one and at the same time load all related rows from FieldLayoutItem table. FieldLayoutItem table doesn't have index for fieldlayout column, so loading many row will cause Full-Scan and will be slow.
Since fieldLayoutCache is used in many places, slow population will cause performance problems, see for example: JRASERVER-29310 and JRASERVER-61166.

Environment

• Large number of rows in fieldlayoutitem table - 200k+:

   select count(1) from fieldlayoutitem; 
  1355666
  

Expected Results

Population of fieldLayoutCache is fast

Actual Results

Population of fieldLayoutCache is slow.

Notes

• Thread dumps generated during that time will have following common part:

  "http-nio-8080-exec-359 " #68120 daemon prio=5 os_prio=0 tid=0x00007f7bc03ab800 nid=0x6263 runnable [0x00007f93cc3a3000]
     java.lang.Thread.State: RUNNABLE
  	at sun.nio.ch.FileDispatcherImpl.read0(Native Method)
  ...
  	at org.apache.commons.dbcp2.DelegatingResultSet.next(DelegatingResultSet.java:191)
  	at org.ofbiz.core.entity.EntityListIterator.next(EntityListIterator.java:245)
  	at org.ofbiz.core.entity.EntityListIterator.getCompleteList(EntityListIterator.java:308)
  	at org.ofbiz.core.entity.GenericDAO.selectByAnd(GenericDAO.java:731)
  	at org.ofbiz.core.entity.GenericHelperDAO.findByAnd(GenericHelperDAO.java:150)
  	at org.ofbiz.core.entity.GenericDelegator.findByAnd(GenericDelegator.java:903)
  	at org.ofbiz.core.entity.GenericDelegator.findByAnd(GenericDelegator.java:881)
  	at org.ofbiz.core.entity.GenericDelegator.getRelated(GenericDelegator.java:1499)
  	at org.ofbiz.core.entity.GenericDelegator.getRelated(GenericDelegator.java:1429)
  	at org.ofbiz.core.entity.GenericValue.getRelated(GenericValue.java:136)
  	at com.atlassian.jira.issue.fields.layout.field.AbstractFieldLayoutManager.loadFieldLayoutItems(AbstractFieldLayoutManager.java:302)
  	at com.atlassian.jira.issue.fields.layout.field.AbstractFieldLayoutManager.loadFieldLayout(AbstractFieldLayoutManager.java:334)
  	at com.atlassian.jira.issue.fields.layout.field.AbstractFieldLayoutManager.access$100(AbstractFieldLayoutManager.java:46)
  	at com.atlassian.jira.issue.fields.layout.field.AbstractFieldLayoutManager$FieldLayoutCacheLoader.load(AbstractFieldLayoutManager.java:426)
  	at com.atlassian.jira.issue.fields.layout.field.AbstractFieldLayoutManager$FieldLayoutCacheLoader.load(AbstractFieldLayoutManager.java:423)
  	at com.atlassian.cache.ehcache.LoadingCache.getFromLoader(LoadingCache.java:145)
  	at com.atlassian.cache.ehcache.LoadingCache.loadValueAndReleaseLock(LoadingCache.java:100)
  	at com.atlassian.cache.ehcache.LoadingCache.get(LoadingCache.java:76)
  	at com.atlassian.cache.ehcache.DelegatingCache.get(DelegatingCache.java:99)
  	at com.atlassian.jira.issue.fields.layout.field.AbstractFieldLayoutManager.getRelevantFieldLayout(AbstractFieldLayoutManager.java:264)
  	at com.atlassian.jira.issue.fields.layout.field.DefaultFieldLayoutManager.getFieldLayout(DefaultFieldLayoutManager.java:702)
  	at com.atlassian.jira.issue.fields.layout.field.DefaultFieldLayoutManager.getUniqueFieldLayouts(DefaultFieldLayoutManager.java:586)
  	at com.atlassian.jira.issue.fields.layout.field.DefaultFieldLayoutManager.getUniqueFieldLayouts(DefaultFieldLayoutManager.java:562)
  ....
  

• SQL logs will have the following:

  SELECT ID, FIELDLAYOUT, FIELDIDENTIFIER, DESCRIPTION, VERTICALPOSITION, ISHIDDEN, ISREQUIRED, RENDERERTYPE FROM public.fieldlayoutitem WHERE FIELDLAYOUT='10100'"
  

Note on fix

Fix in Jira is done by adding the index from the workaround section below.

Workaround

Important: Please validate these changes in a test environment first to ensure that they perform well.
Adding index for fieldlayout column to the fieldlayoutitem table. Credit to Dieter Greiner for suggesting a fix

• Modify entitymodel.xml, so JIRA will create index during next restart:
  1. Edit entitymodel.xml file (<JIRA_INSTALL>/atlassian-jira/WEB-INF/classes/entitydefs/entitymodel.xml) and add section with idx_fli_fieldlayout, result should be like this:

     <entity entity-name="FieldLayoutItem" table-name="fieldlayoutitem" package-name="">
     ...
         <relation type="one" title="Parent" rel-entity-name="FieldLayout">
             <key-map field-name="fieldlayout" rel-field-name="id"/>
         </relation>
     
         <index name="idx_fli_fieldidentifier">
             <index-field name="fieldidentifier"/>
         </index>
     
         <!-- added to workaround JRASERVER-66013: -->
         <index name="idx_fli_fieldlayout">
             <index-field name="fieldlayout"/>
         </index>
     </entity>
     

     1. Restart JIRA.
     2. Verify the indexes have been added, for example with MySQL:

        show create table fieldlayoutitem;
        

        This should show:

          KEY `idx_fli_fieldlayout` (`FIELDLAYOUT`)
        

• Alternatively you could add the index onto the table manually.
• Please note that as this is a customisation it's not fully tested, so may cause unintended side-effects. If the modification is applied, it will need to be done after each upgrade as the entitymodel.xml file will change.
  • Please ensure to copy just the modification across during upgrades, not the entire file, as that can cause significant problems (you may overwrite changes in future versions).