Improve performance of suggest labels functionality for large instances

Problem

For large instances of Confluence, the suggest labels functionality can take a long time (5+ minutes) to return results, causing heavy load to the database in that duration. To clarify, this is the functionality that displays potential results when the user starts typing into the labels box.

The two actions (especially the second) that seem to cause this are:

• /json/suggestlabelsactivity.action
• /labels/autocompletelabel.action

Offending Query

This seems to be the offending query causing the load on the DB:

select * from ( select label3_.LABELID as LABELID, label3_.NAME
as NAME, label3_.OWNER as OWNER, label3_.NAMESPACE as NAMESPACE,
 label3_.CREATIONDATE as CREATION5_, label3_.LASTMODDATE as LASTMODD6_, label3_.LABELID as x0_0_, count(label3_.LABELID) as x1_0_ 
 from CONTENT_LABEL labelling0_, CONTENT spaceconte1_, SPACES space2_, LABEL label3_, CONTENT contentent4_ where spaceconte1_.CONTENTTYPE in 
 ('com.atlassian.confluence.pages.AbstractPage', 'USERSTATUS', 'PAGE', 'BLOGPOST', 'MAIL', 'SPACEDESCRIPTION', 
 'com.atlassian.confluence.core.SpaceContentEntityObject') and labelling0_.LABELID=label3_.LABELID and ((label3_.NAMESPACE=:1  
 and labelling0_.LABELID=label3_.LABELID)and(labelling0_.CONTENTID=spaceconte1_.CONTENTID )and(spaceconte1_.SPACEID=space2_.SPACEID )
 and(lower(space2_.SPACEKEY)=:2 )and(contentent4_.CONTENT_STATUS!='deleted'  and labelling0_.CONTENTID=contentent4_.CONTENTID)) 
 group by  label3_.LABELID , label3_.NAMESPACE , label3_.OWNER , label3_.NAME , label3_.LASTMODDATE , label3_.CREATIONDATE 
 order by count(label3_.LABELID)DESC ) where rownum <= :3

Workaround

The autosuggest/autocomplete label feature can be disabled:

1. Backup confluence-install/confluence/includes/js/labels-min.js
2. Modify confluence-install/confluence/includes/js/labels-min.js - look for bindAutocomplete:g or ,bindAutocomplete:e on the very last line. Comment it out, including one of the surrounding commas:

   {return m(d(r,q,s))}/*,bindAutocomplete:g*/,isNewPage:j,routes:n,setLabelError:o,parseLabelStringToArray:c}
   

3. Go to Confluence Admin >> Plugins >> Locate "Advanced Macros" plugin (you might need to expand the "System Plugins"), and disable it
4. Once it's disabled, re-enable it again. This is to force expiry of the javascript cache to pick up the change.
5. NB: If you make a syntax mistake when editing this file, the file will not be picked up at all and you may lose functionality eg the Create button, the Admin panel and Notifications and Tasks. Simply revert your change to the JS file, go to <your-url>/admin/viewplugins.action, disable and re-enable Advanced Macros again to expire the cache.