Closed Access service projects, slow user picker search

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.



Problem

Customer is facing a problem if a ServiceDesk customer shares a request in the Service Management Portal.

The ServiceDesk project has every Jira user as customer in the portal and it takes a long time (20s+), until a typed in person is recognized by Jira user picker.

Also sometimes the connection pool is 100% full and the application isn't usable for some time.

The details to replicate this:

  • Enable Jira SQL logging
  • Create a ServiceDesk issue from customer portal
  • Click Share
  • Start typing first letters from a Portal user name

The following appears in the atlassian-jira-sql.log

2017-08-01 15:03:37,246 http-nio-1737-exec-17 admin 899x153x8 hw5e6e /rest/servicedesk/1/customer/participants/ISSUES-111/search 1509ms
 "SELECT user_name FROM cwd_user WHERE ((lower_user_name IN ('1aaaaa', '2aaaaa', '2aaaaaa', '4bbbbb', '4bbbbbb', '1bbbbbbb', '2hhhhhhhh', '1aaaaaaa', '3qqqqqq', '3qqqqqq', '4eeeeee', '6aaaaaa', '3aaaaa', '2aaaaaa', '6aaaaaa', '8aaaaaa', '9aaaaaa', 'aaaaaaaa', 'aaaaaaa', 'aaaaaa', 'aaaaaaa', 'aaaaaaa', 'aaaaaa', 'aaaaa', 'aaaaaa', 'ssssss', 'ssssss', 'sssssss', 'sssssss', 'ssssss', 'zzzzzz', 'zzzzz', 'zzzzzz', 'aaaaaaa', 'aaaaaa', 'zzzzzz', 'aaaaaa', 'aaaaaa', 'ggggg', 'gggggg', 'gggggg', 'ggggg', 'gggggg', 'gggggg', 'gggggg', 'gggggggg', 'gggggggg', 'gggggg', '2ggggggg', 'ggggggg', 'aaaaa', 'aaaaa', 'aaaaaa', 
'bbbbb', 'bbbbbb', 'bbbbbbb', 'hhhhhhhh', 'aaaaaaa', 'qqqqqq', 'qqqqqq', 'eeeeee', 'aaaaaa', 'aaaaa', 'aaaaaa', 'aaaaaa', 'aaaaaa', 'aaaaaa', 'aaaaaaaa', 'aaaaaaa', 'aaaaaa', 'aaaaaaa', 'aaaaaaa', 'aaaaaa', 'aaaaa', 'aaaaaa', 'ssssss', 'ssssss', 'sssssss', 'sssssss', 'ssssss', 'zzzzzz', 'zzzzz', 'zzzzzz', 'aaaaaaa', 'aaaaaa', 'zzzzzz', 'aaaaaa', 'aaaaaa', 'ggggg', 'gggggg', 'gggggg', 'ggggg', 'gggggg', 'gggggg', 'gggggg', 'gggggggg', 'gggggggg', 'gggggg', 'ggggggg', 'ggggggg', 'aaaaa', 'aaaaa', 'aaaaaa', 'bbbbb', 'bbbbbb', 'bbbbbbb', 'hhhhhhhh', 'aaaaaaa', 'qqqqqq', 'qqqqqq', 'eeeeee',
 'aaaaaa', 'aaaaa', 'aaaaaa', 'aaaaaa', 'aaaaaa', 'aaaaaa', 'aaaaaaaa', 'aaaaaaa', 'aaaaaa', 'aaaaaaa', 'aaaaaaa', 'aaaaaa', 'aaaaa', 'aaaaaa', 'ssssss', 'ssssss', '4sssssss', '2sssssss', 'ssssss', 'zzzzzz', 'zzzzz', 'zzzzzz', 'aaaaaaa', 'aaaaaa', 'zzzzzz', 'aaaaaa', 'aaaaaa', 'ggggg', 'gggggg', 'gggggg', 'ggggg', 'gggggg', 'gggggg', 'gggggg', '4gggggggg', 
'gggggggg', '2gggggg', 'ggggggg', 'ggggggg') ) OR (lower_user_name IN ('2aaaaa', '4aaaaa', '6aaaaaa', '9bbbbb', '6bbbbbb', '5bbbbbbb', 'hhhhhhhh', 'aaaaaaa', 'qqqqqq', 'qqqqqq', 'eeeeee', 'aaaaaa', 'aaaaa', 'aaaaaa', 'aaaaaa', 'aaaaaa', 'aaaaaa', 'aaaaaaaa', 'aaaaaaa', 'aaaaaa', 'aaaaaaa', 'aaaaaaa', 'aaaaaa', 'aaaaa', 'aaaaaa', 'ssssss', 'ssssss', 'sssssss', 'sssssss', 'ssssss', 'zzzzzz', 'zzzzz', 'zzzzzz', 'aaaaaaa', 'aaaaaa', 'zzzzzz', 'aaaaaa', 'aaaaaa', 'ggggg', 'gggggg', 'gggggg', 'ggggg', 'gggggg', 'gggggg', 'gggggg', 'gggggggg', 'gggggggg', 'gggggg', 'ggggggg', 'ggggggg', 'aaaaa', 'aaaaa', 'aaaaaa', 'bbbbb', 'bbbbbb', 'bbbbbbb', 'hhhhhhhh', 'aaaaaaa',
.
.
.
.
) )) AND (directory_id =  '1' ) ORDER BY lower_user_name"

The query above is huge, it might take around 60 seconds to execute if number of users is large.

Below are some statistics from customer logs:

$ egrep "2017-07-13 12:16:54,893 http-nio-8080-exec-22 e030141 735x5271x1 199z2yl" atlassian-jira-sql.log |sed -e "s/lower_user_name/lower_user_name\n/g" |wc -l 
37                                                                                                                                                                                      
$ egrep "2017-07-13 12:16:54,893 http-nio-8080-exec-22 e030141 735x5271x1 199z2yl" atlassian-jira-sql.log |sed -e "s/,/,\n/g" |wc -l 34463

The query puts 34k users in the where statment above, split into 37 in statments, on customer instance it took 70 seconds to execute.

Diagnosis

Environment

  • Jira 7.3.8 and later.
  • Jira ServiceDesk 3.5.1
  • LDAP Directory (Read Only, with Local Groups), 35k users.
  • The dark feature sd.use.search.by.permissions.disabled is not activated.
  • Closed Access ServiceDesks
    • Who can raise requests?  set to Customers who are added to the project.


Diagnostic Steps

  • Verify the customer SQL logs to contain the above long query and that it is slow.
  • Check the ServiceDesk project permissions and verify CloseAccess using:

    http(s)://<Jira_BASEURL>/rest/servicedesk/1/servicedesk/<PROJECT_KEY>/settings/requestsecurity

Cause

In some cases it seems ServiceDesk is using old code that is not efficent in looking up users, and the code is slow in case the number of users is huge.

The contions to trigger this is an OR conditon that evaluates to true if:

Project is Closed Access (isOpenAccess() is false

OR

USE_SEARCH_BY_PERMISSIONS IS enabled (sd.use.search.by.permissions.disabled is not Active)

The code that controls this is found at: CustomerSearchManagerImpl.java

See also  JSDSERVER-5959 - Getting issue details... STATUS  


Workaround

There is a couple of things that can be done to fix this issue in this case:

  • One is to enable the sd.use.search.by.permissions.disabled dark feature:
    • Navigate to following address: http(s)://<jira_base_url>/secure/SiteDarkFeatures!default.jspa
    • Add the following flag and Enable:
      sd.use.search.by.permissions.disabled

      Enabling this will flip the condition in the code for Close Access service projects.
      This will allow more efficient search code to run and wouldn't generate the slow query.

  • Another is to change the permissions on the project to make it an Open Access Desk, this will also flip the condition and will divert the execution to run faster more efficient code.
    The Open Access Desk is set from customer permission under project settings; mainly by setting Who can raise requests? to second option instead of first.


Last modified on Nov 23, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.