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

Poor performance finding open sessions on MySQL and Oracle

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: CLOSED
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.4.0, 2.4.1
    • Fix Version/s: 2.5.0
    • Labels:
      None
    • Environment:
      mysql

      Description

      Sakai determines whether a session is open by checking to see if SESSION_START = SESSION_END.

      Unfortunately on mysql this produces expensive queries, as the indexes are not used in for example this query which checks for open sessions from nonexistence app servers:

      mysql> explain select SS.SESSION_ID from SAKAI_SESSION SS left join SAKAI_CLUSTER SC on SS.SESSION_SERVER = SC.SERVER_ID where SS.SESSION_START = SS.SESSION_END and SC.SERVER_ID is null;
      ------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE SS ALL NULL NULL NULL NULL 329519 Using where
      1 SIMPLE SC eq_ref PRIMARY PRIMARY 192 sakai21.SS.SESSION_SERVER 1 Using where; Using index; Not exists

      ------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)

      Note the large number of rows examined. In production, this query shows up in the mysql slow query log if SAKAI_SESSION is of non-trivial size.

      The solution is to add a specific flag for whether the session is active or not, e.g.

      mysql> alter table SAKAI_SESSION ADD COLUMN SESSION_ACTIVE tinyint(1);
      mysql> alter table SAKAI_SESSION ADD INDEX SESSION_ACTIVE_I (SESSION_ACTIVE);

      which avoids the full table scan:

      mysql> EXPLAIN select SS.SESSION_ID from SAKAI_SESSION SS left join SAKAI_CLUSTER SC on SS.SESSION_SERVER = SC.SERVER_ID where SS.SESSION_ACTIVE = 1 and SC.SERVER_ID is null;
      -------------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      -------------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE SS ref SESSION_ACTIVE_I SESSION_ACTIVE_I 2 const 1 Using where
      1 SIMPLE SC eq_ref PRIMARY PRIMARY 192 sakai23uct.SS.SESSION_SERVER 1 Using where; Using index; Not exists

      -------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.08 sec)

      This would also resolve SAK-7452 which arises from limited precision in SESSION_START and SESSION_END and the way in which these 2 are used to establish whether the session is active or not.

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  smarquard Stephen Marquard
                  Reporter:
                  smarquard Stephen Marquard
                  Votes:
                  2 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration