Uploaded image for project: 'Jira Server and Data Center'
  1. Jira Server and Data Center
  2. JRASERVER-72101

Performance regression in user lookup

    XMLWordPrintable

Details

    Description

      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

      Attachments

        1. screen-813.png
          screen-813.png
          71 kB
        2. screen-814.png
          screen-814.png
          106 kB

        Issue Links

          Activity

            People

              jreczycki Jakub Reczycki
              kwong2@atlassian.com KellyW (Inactive)
              Votes:
              18 Vote for this issue
              Watchers:
              43 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: