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

Slow queries from Forums

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: CLOSED
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.9.3
    • Fix Version/s: None
    • Component/s: Messages Tool
    • Labels:
      None
    • Previous Issue Keys:
      SAK-11463, MSGCNTR-72

      Description

      Our recently upgraded mysql server is currently under very light load (this week is mid-term vac) but is reporting slow queries from Forums. This type is the most frequent:

      Count: 20 Time=2.10s (42s) Lock=0.00s (0s) Rows=11.8 (235), sakaiuser[sakaiuser]@3hosts

      select areaimpl5_.CONTEXT_ID as col_0_0_, topicimpl2_.ID as col_1_0_, membership3_.NAME as col_2_0_, count as col_3_0_
      from MFR_MESSAGE_T messageimp0_
      inner join MFR_TOPIC_T topicimpl2_ on messageimp0_.surrogateKey=topicimpl2_.ID
      inner join MFR_MEMBERSHIP_ITEM_T membership3_ on topicimpl2_.ID=membership3_.t_surrogateKey
      inner join MFR_OPEN_FORUM_T openforumi4_ on topicimpl2_.of_surrogateKey=openforumi4_.ID
      inner join MFR_AREA_T areaimpl5_ on openforumi4_.surrogateKey=areaimpl5_.ID, MFR_PERMISSION_LEVEL_T permission1_
      where (areaimpl5_.CONTEXT_ID in ('S')) and (membership3_.NAME in ('S' , 'S'))
      and messageimp0_.DRAFT=N and messageimp0_.DELETED=N
      and openforumi4_.DRAFT=N and topicimpl2_.DRAFT=N
      and (permission1_.TYPE_UUID<>'S' and permission1_.NAME=membership3_.PERMISSION_LEVEL_NAME or membership3_.PERMISSION_LEVEL=permission1_.ID)
      and permission1_.X_READ=N
      and (permission1_.MODERATE_POSTINGS=N or messageimp0_.APPROVED=N or messageimp0_.CREATED_BY='S')
      group by areaimpl5_.CONTEXT_ID , topicimpl2_.ID , membership3_.NAME

      A specific example of this below shows the high number of rows examined (400K+) means that this is likely to become a serious scaleability issue as the volume of data in Forums tables increases.

      This is probably a result of the way the query is constructed in relation to the indexes on the tables. It's not clear if this is a mysql-only issue or might also lead to slow execution times for this query on Oracle.

      1. Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 410297
        select areaimpl5_.CONTEXT_ID as col_0_0_, membership3_.NAME as col_1_0_, count as col_2_0_
        from MFR_MESSAGE_T messageimp0_
        inner join MFR_TOPIC_T topicimpl2_ on messageimp0_.surrogateKey=topicimpl2_.ID
        inner join MFR_MEMBERSHIP_ITEM_T membership3_ on topicimpl2_.ID=membership3_.t_surrogateKey
        inner join MFR_OPEN_FORUM_T openforumi4_ on topicimpl2_.of_surrogateKey=openforumi4_.ID
        inner join MFR_AREA_T areaimpl5_ on openforumi4_.surrogateKey=areaimpl5_.ID, MFR_PERMISSION_LEVEL_T permission1_
        where (areaimpl5_.CONTEXT_ID in ('96dbcba6-01c2-4059-007a-40e484873bb9')) and (membership3_.NAME in ('Tutor' , 'Tutorial Group 4' , 'Student'))
        and openforumi4_.DRAFT=0
        and topicimpl2_.DRAFT=0
        and messageimp0_.DRAFT=0
        and messageimp0_.DELETED=0
        and (permission1_.TYPE_UUID<>'00000000-0000-0000-7777-000000000000' and permission1_.NAME=membership3_.PERMISSION_LEVEL_NAME or membership3_.PERMISSION_LEVEL=permission1_.ID)
        and permission1_.X_READ=1
        and (permission1_.MODERATE_POSTINGS=0 or messageimp0_.APPROVED=1 or messageimp0_.CREATED_BY='c4c2561d-f23c-4034-804c-19e6d08feb5b')
        group by areaimpl5_.CONTEXT_ID , membership3_.NAME;

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  baholladay Bryan Holladay
                  Reporter:
                  arwhyte Anthony Whyte
                  Votes:
                  1 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration