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

General performance fix for slow bound query parameter evaluations in emulate_bound_params in mysqli

    XMLWordPrintable

    Details

      Description

      This issue was first discovered with the SCORM reports having slow page load times and the fix turned out to be a general performance increase for Moodle. We have about 40,000 Moodle users and our basic and interactions SCORM reports span hundreds of pages.

      It turns out that the call to array_shift in function emulate_bound_params from the file lib/dml/mysqli_native_moodle_database.php is very slow when it needs to evaluate thousands of bound query parameters.
      The function array_shift reindexes the array which is not necessary for the function emulate_bound_params, and is at least one reason for the bad performance.

      Our fix involves using array_reverse and array_pop for faster bound parameter evaluations. These are the before and after stats for a SCORM report page load time:
      Pre-patch page load time: 128.496265 secs
      Post-patch page load time: 5.149125 secs

      We only made the fix for mysqli.

      Please take a look at these branches at http://github.com/timgus/moodle for the patch and unit tests (for M23 and up):
      array_shift_fix_m22
      array_shift_fix_m23
      array_shift_fix_m24
      array_shift_fix_m25
      array_shift_fix_master

      I've attached my xhprof run before and after the fix when viewing a SCORM report page.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              skodak Petr Skoda
              Reporter:
              tgus Tim Gus
              Integrator:
              Dan Poltawski Dan Poltawski
              Tester:
              Ankit Agarwal Ankit Agarwal
              Participants:
              Component watchers:
              Matteo Scaramuccia, Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                8/Jul/13