Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-22726

Improve performance of suggest labels functionality for large instances





      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


      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.


        Issue Links



              drizzuto David Rizzuto
              rchang Robert Chang
              14 Vote for this issue
              20 Start watching this issue