Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-68647

SQL performance issue with mod_forum\privacy\provider::export_all_posts()

    XMLWordPrintable

    Details

      Description

      This was originally noticed by Eduard Cercós on moodle.org. When we tested MDLSITE-6104, Helen Foster filled a data export request for her account, which got stuck in the processing state. Eduard reported there is one SQL query taking extremely long to process. We were able to trace it down to the query in the mod_forum\privacy\provider::export_all_posts() method.

      In the isolated staging environment on the DB server that had nothing else to do, executing the query to export Helen's data took 1 hour and 48 minutes. On the production environment, it seemed to never be able to finish and it was suspected to cause significant drop in the perceived performance of the whole site.

      I assume that the problem here lies in the usage of LEFT JOINs combined with the OR conditions that cause the SQL server to scan through too many combinations to seek for NOT NULL values.

      After the query was reimplemented to use three separate queries, optimised thanks to INNER JOINs and UNIONed into the final data set, the same data were obtained in less than a few seconds. And I added an index over the privatereplyto field (because it is used as a search criteria here), the data were received almost instantly in 0.2 seconds.

      While fixing this, I was also looking at other queries in the class to see if there is a potential for improvements. I found a candidate in the export_discussion_data() but there I did not see a straightforward way to rewrite it. And because we did not have any actual problem with that query (took a few seconds to finish in this test case), I decided to not fix what ain't broken and I am leaving it be.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              mudrd8mz David Mudrák (@mudrd8mz)
              Reporter:
              mudrd8mz David Mudrák (@mudrd8mz)
              Peer reviewer:
              Neill Magill
              Integrator:
              Adrian Greeve
              Tester:
              CiBoT
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                14/Sep/20

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day
                  1d