Closed Access service projects, slow user picker search
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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 34463The 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.