Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-38612

Queries for permission checks are slow in large sites

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: CLOSED
    • Priority: Critical
    • Resolution: Won't Fix
    • Affects Version/s: 2.8.x, 2.9.x
    • Fix Version/s: None
    • Component/s: Kernel
    • Previous Issue Keys:
      SAK-10193, KNL-1068

      Description

      Permission checks like the one below turn out to be slow in sites with large membership. For example in one site with 22300 members, this query appears to scan 67544 rows, even though a mysql 'explain' would seem to suggest that the query is using indexes correctly and is relatively efficient.

      A large number of such permission checks in a short space of time have in the past caused major performance problems for the database.

      1. Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 67544
        select count(1) from SAKAI_REALM_RL_FN MAINTABLE LEFT JOIN SAKAI_REALM_RL_GR GRANTED_ROLES ON (MAINTABLE.REALM_KEY =
        GRANTED_ROLES.REALM_KEY AND MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS, SAKAI_REALM_ROLE ROLES, SAKAI_REALM_FUNCTION FUNCTIONS where ( ROLES.ROLE_NAME in('.anon','.auth') or ( GRANTED_ROLES.USER_ID = 'xyz' AND GRANTED_ROLES.ACTIVE = 1 ) ) AND FUNCTIONS.FUNCTION_NAME = 'calendar.all.groups' AND REALMS.REALM_ID in ('/site/96dbcba6-01c2-4059-007a-40e484873bb9') AND MAINTABLE.REALM_KEY
        = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY AND MAINTABLE.ROLE_KEY = ROLES.ROLE_KEY;

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                k1team KERNEL TEAM (Inactive)
                Reporter:
                smarquard Stephen Marquard
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Git Source Code