Issue Summary

User mentions and assignee lookup are slow with Jira 8.14+/JSM 4.14+. Performance regression started after Jira upgrade from the previous LTS 8.13 to 8.14 release and this was caused by the internal upgrade of the Embedded Crowd to 4.2.0+ version.
EmbCrowd requests create an additional load on DB since UserDao method doesn't have a specific cache.

Environments Affected

Jira Software - all versions between 8.14 and 8.19.0
Jira Service Management - all versions between 4.14 and 4.19.0

Steps to Reproduce

1. Setup Jira with large user base (50k+)
2. Created new Jira project and issue
3. @ mention user in the issue comment

Expected Results

@ mention user lookup result returns instantaneously, especially the one that user have searched recently/repeatedly

Actual Results

@ mention user lookup takes a substantial amount of time (few minutes) to return the list of matching users

Investigation

No error from logs.

Capturing the SQL logs in local testing, we noticed that Jira is querying every user:

• every internal user against internal directory once e.g:

  2021-01-22 21:16:57,072+1100 http-nio-48140-exec-9 admin 1276x1590x1 12f88sa /rest/internal/2/user/mention/search 1ms "SELECT user_name FROM public.cwd_user WHERE (lower_user_name =  'admin' ) AND (directory_id =  '1' ) ORDER BY lower_user_name"
  2021-01-22 21:16:57,072+1100 http-nio-48140-exec-9 admin 1276x1590x1 12f88sa /rest/internal/2/user/mention/search 1ms Connection returned. borrowed : 0
  2021-01-22 21:16:57,072+1100 http-nio-48140-exec-9 admin 1276x1590x1 12f88sa /rest/internal/2/user/mention/search 0ms Connection taken. borrowed : 1

• every LDAP user against the internal directory and every LDAP integrated with Jira e.g:

  2021-01-22 21:16:57,097+1100 http-nio-48140-exec-9 admin 1276x1590x1 12f88sa /rest/internal/2/user/mention/search 1ms "SELECT user_name FROM public.cwd_user WHERE (lower_user_name =  'jack' ) AND (directory_id =  '1' ) ORDER BY lower_user_name"
  2021-01-22 21:16:57,097+1100 http-nio-48140-exec-9 admin 1276x1590x1 12f88sa /rest/internal/2/user/mention/search 1ms Connection returned. borrowed : 0
  2021-01-22 21:16:57,097+1100 http-nio-48140-exec-9 admin 1276x1590x1 12f88sa /rest/internal/2/user/mention/search 0ms Connection taken. borrowed : 1
  2021-01-22 21:16:57,098+1100 http-nio-48140-exec-9 admin 1276x1590x1 12f88sa /rest/internal/2/user/mention/search 1ms "SELECT user_name FROM public.cwd_user WHERE (lower_user_name =  'jack' ) AND (directory_id =  '10000' ) ORDER BY lower_user_name"
  2021-01-22 21:16:57,098+1100 http-nio-48140-exec-9 admin 1276x1590x1 12f88sa /rest/internal/2/user/mention/search 1ms Connection returned. borrowed : 0
  2021-01-22 21:16:57,099+1100 http-nio-48140-exec-9 admin 1276x1590x1 12f88sa /rest/internal/2/user/mention/search 0ms Connection taken. borrowed : 1

This same is not observed with similar setup in Jira 8.13.

With the above findings, a lot of queries are hitting the Jira database especially if many users are sync from LDAP and/or there are multiple LDAPs integrated with Jira.

The same amount of queries are incurred, despite repeatedly @ mentioning the same user in the same issue.

Workaround

Reduce the users synchronized from the external directory as Jira may scan the whole database table in some corner cases. Doing this will also help with performance improvements in other areas.
Reducing the number of users synchronized from LDAP to JIRA applications