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

$DB->get_records uses a lot of Peak RAM (with Postgres)

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      CI Will test this fully

      I'd recommend that any reviewer and other interested parties uses the dbmemtestcli.php script to compare the different approaches on a variety of hardware and PHP versions.

      Show
      CI Will test this fully I'd recommend that any reviewer and other interested parties uses the dbmemtestcli.php script to compare the different approaches on a variety of hardware and PHP versions.
    • Affected Branches:
      MOODLE_36_STABLE, MOODLE_37_STABLE
    • Fixed Branches:
      MOODLE_38_STABLE
    • Pull Master Branch:
      MDL-66327-master

      Description

      This came from investigating group/overview.php which was running out of memory on one of our huge courses.

      Eventually I boiled it down to this test script https://gist.github.com/timhunt/e5fa1174e920ff266db07e8fcfd4cef8 (also attached). You can save that in the root of your Moodle and run it. You need to have Admin -> Development -> Debugging -> Performance info turned on, and then compare the 'RAM' and 'RAM peak' reported for different $methods.

      The results I get (64-bit Windows 10, Postgres 11) are:

      method              RAM (MB)      RAM peak (MB)
      none                      39            40.2
      array_fill_object        321           322.1
      get_records              369           554
      get_recordset            352.8         354
      (array_fill_object_other 449           450.1)
      

      The first thing to note is that loading the arrays from the DB causes some overhead compared to just building the arrays in PHP memory.

      However, the real problem is the huge 'RAM peak' for get_records. This can cause out-of-memory errors on scripts that reall ought to be able to run in the available RAM. It also means that using get_recordset then immediately copying the data to an array is more effecient that get_records, whereas to me, those two thigns should be logically equivalent.

      So, if anyone can fix this, it might be quite a quick perforamance win. (However, I did not look at running time of the scripts. I was just focussed on memory use.)

      (The other thing to note is, PHP is weird. Generating the array in PHP using sprintf is horribly inefficient for reasons I don't understand. That must be some sort of PHP bug!)

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                4 Vote for this issue
                Watchers:
                13 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  18/Nov/19

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 6 hours, 30 minutes
                  6h 30m