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

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




      This was originally noticed by eduardcercos on moodle.org. When we tested MDLSITE-6104, tsala 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.


        Issue Links



              mudrd8mz David Mudrák (@mudrd8mz)
              mudrd8mz David Mudrák (@mudrd8mz)
              Neill Magill Neill Magill
              Adrian Greeve Adrian Greeve
              CiBoT CiBoT
              David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo, Adrian Greeve, Ilya Tregubov, Kevin Percy, Mathew May, Mihail Geshoski, Shamim Rezaie, Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo, Andrew Lyons, Adrian Greeve, David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo
              1 Vote for this issue
              4 Start watching this issue



                Time Tracking

                  Original Estimate - Not Specified
                  Not Specified
                  Remaining Estimate - 0 minutes
                  Time Spent - 1 day