Moodle
  1. Moodle
  2. MDL-18865

Log insert performance can be improved for busy sites that use Postgres

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Won't Fix
    • Affects Version/s: 1.9.4
    • Fix Version/s: BACKEND
    • Component/s: Performance
    • Labels:
    • Database:
      PostgreSQL
    • Affected Branches:
      MOODLE_19_STABLE
    • Epic Link:
    • Rank:
      18208

      Description

      Inserts to mdl_log are among the most significant database queries on our system - they don't take that long to run, but we usually do somewhere in the range of 3-10 log inserts per second, so it adds up.

      On Postgres, running single insert 100 times is much slower than running 100 inserts in a single transaction.

      I made a patch which allows you to select a local folder (must be on real local filesystem, not NFS etc, because it relies on file locking working) where log inserts will be cached for a number of seconds. When there is another log insert after that many seconds (eg 30), all the cached inserts are added into log. So your log table is delayed by about 30 seconds.

      This works fine with multiple servers (each having their own cache file, of course, since they are local). On test servers I saw ~20ms improvement per logged page view during a load test. Improvement is less significant if database is not loaded. We are currently most concerned about database performance so time saved per page isn't really the key number for us - however this should also correspond to time/effort saved by the database server as well.

      Notes

      • This patch applies to OU Moodle only. I can port it to Moodle 1.9 or (more likely) Moodle 2 if desired.
      • We will be deploying this code to our live system on 3 June so it will get significant testing after that...
      • Other 'proper' transactional databases may also receive performance improvement from this patch, but Postgres is the only one I know about.
      • Depending on how many entries are cached, on those requests where it does actually insert a batch, users might notice the slight delay (maybe 1ms per entry). The impact of this is obviously low on pages where the add_to_log call is at the end.

        Activity

        Hide
        Marina Glancy added a comment -

        Thanks for reporting this.

        I'm closing this issue because I believe it affects only unsupported versions of Moodle. This issue will remain here in case other users have the same problem.

        If you haven't already done so, I encourage you to upgrade to a supported version.

        Please note that in 2.7 Moodle is moving to the new Logging API and also implements the batch insert in the new log tables

        Show
        Marina Glancy added a comment - Thanks for reporting this. I'm closing this issue because I believe it affects only unsupported versions of Moodle. This issue will remain here in case other users have the same problem. If you haven't already done so, I encourage you to upgrade to a supported version. Please note that in 2.7 Moodle is moving to the new Logging API and also implements the batch insert in the new log tables

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: