Summary

Accessing Admin (Cog) > User Management > Groups can be slow for large JIRA instances (page url: secure/admin/user/GroupBrowser.jspa). In JIRA 7 new functionality was added and JIRA checks if group can be deleted, see example:

Related code from groupbrowser.jsp:

<ww:property id="prospectiveDeleteError" value="groupDeletable(name)" />
            <ww:if test="@prospectiveDeleteError != ''">
                <li><a id="del_<ww:property value="name" />" class="aui-button aui-button-link" href="javascript:void(0)" title="<ww:property value="@prospectiveDeleteError" />" aria-disabled="true" disabled><ww:text name="'common.words.delete'"/></a></li>

For each group from the list, method GroupBrowser.isGroupDeletable will be called. It does number of checks and as part of getCommentsAndWorklogsGuardedByGroupCount method it will generate following SQL query:

SELECT COUNT(A.ID) FROM public.jiraaction A WHERE A.actiontype='comment' AND A.actionlevel='<GROUP NAME>'}}

An example in the SQL log would be ( time 1s):

2016-04-27 13:51:22,470 http-nio-18714-exec-14 admin 831x1484x1 jcaucx /secure/admin/user/GroupBrowser.jspa 1083ms "SELECT COUNT(A.ID) FROM public.jiraaction A WHERE A.actiontype='comment' AND A.actionlevel='developers_9'"

There are no indexes that will enhance the performance of this query and as a result this query can take sometime to complete.

Environment

Any database + JIRA 7

Steps to Reproduce

1. Create a JIRA instance with a large number of comments or jira 'actions'. You can use the JIRA Data Generator to help replicate this issue.
2. Enable SQL Logging from: Admin (Cog) > System > Logging and Profiling
3. Access the Groups page.
4. Observe the slow responsiveness of this page.

Expected Results

JIRA should be responsive when accessing this page.

Actual Results

The page can take some time to respond due to these queries being executed for each group that is displayed.

Notes

Tracing the thread dumps shows a long-running thread such as:

		...
        at com.atlassian.jira.issue.comments.DefaultCommentManager.getCountForCommentsRestrictedByField(DefaultCommentManager.java:443)
        at com.atlassian.jira.issue.comments.DefaultCommentManager.getCountForCommentsRestrictedByGroup(DefaultCommentManager.java:416)
        at com.atlassian.jira.bc.group.DefaultGroupService.getCommentsAndWorklogsGuardedByGroupCount(DefaultGroupService.java:824)
        at com.atlassian.jira.web.action.admin.user.GroupBrowser.isGroupDeletable(GroupBrowser.java:233)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at webwork.util.InjectionUtils$DefaultInjectionImpl.invoke(InjectionUtils.java:70)
        at webwork.util.InjectionUtils.invoke(InjectionUtils.java:56)
        at webwork.util.ValueStack.findValue(ValueStack.java:517)
        at webwork.util.ValueStack.findValue(ValueStack.java:216)
        at webwork.view.taglib.WebWorkBodyTagSupport.findValue(WebWorkBodyTagSupport.java:62)
        at webwork.view.taglib.BasicPropertyTag.doStartTag(BasicPropertyTag.java:54)
        at jsp.secure.admin.user.views.groupbrowser_jsp._jspx_meth_ww_005fproperty_005f12(groupbrowser_jsp.java:3167)

Workaround

• Reduce the total number of Groups being returned by using a filter query.
  • This can be achieved by accessing the Group Browser with the following max argument appended to the URL:
    <JIRA-BASE-URL>/secure/admin/user/GroupBrowser.jspa?max=5