Uploaded image for project: 'Opencast'
  1. MH-8637

update db schema to have index on user_action tables

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Duplicate
    • Affects versions: 1.2
    • Fix versions: None
    • Components: Engage User Interface
    • Labels:
      None
    • Severity:
      Performance
    • Steps to reproduce:
      Hide
      Adam and I dug through the DB this afternoon to try and understand the
      performance issues being experienced. We found some low hanging fruit;
      one query in particular is being run quite frequently, depending on the
      number of people who are viewing lectures in engage:

      SELECT ID AS ID1, OUTPOINT AS OUTPOINT2, INPOINT AS INPOINT3,
      MEDIA_PACKAGE_ID AS MEDIA_PACKAGE_ID4, SESSION_ID AS SESSION_ID5,
      CREATED AS CREATED6, USER_ID AS USER_ID7, LENGTH AS LENGTH8, TYPE AS
      TYPE9 FROM USER_ACTION WHERE (SESSION_ID = '8ssaybxw7uvx') ORDER BY
      CREATED DESC LIMIT 0, 1

      This query has no incidicies on it, and needs to look at around 1.8
      million database rows to complete. It gets slower and slower the more
      users who use the system since this table grows. It currently takes
      around 50 seconds to run (really slow).
      Show
      Adam and I dug through the DB this afternoon to try and understand the performance issues being experienced. We found some low hanging fruit; one query in particular is being run quite frequently, depending on the number of people who are viewing lectures in engage: SELECT ID AS ID1, OUTPOINT AS OUTPOINT2, INPOINT AS INPOINT3, MEDIA_PACKAGE_ID AS MEDIA_PACKAGE_ID4, SESSION_ID AS SESSION_ID5, CREATED AS CREATED6, USER_ID AS USER_ID7, LENGTH AS LENGTH8, TYPE AS TYPE9 FROM USER_ACTION WHERE (SESSION_ID = '8ssaybxw7uvx') ORDER BY CREATED DESC LIMIT 0, 1 This query has no incidicies on it, and needs to look at around 1.8 million database rows to complete. It gets slower and slower the more users who use the system since this table grows. It currently takes around 50 seconds to run (really slow).
    • Tags (folksonomy):

      TestRail: Results

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                greg_logan Greg Logan
                Reporter:
                cab938 Christopher Brooks
              • Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  TestRail: Cases