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

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


    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Won't Fix
    • Affects Version/s: 1.9.4
    • Fix Version/s: BACKEND
    • Component/s: Performance
    • Labels:


      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.


      • 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.

        Gliffy Diagrams




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


                • Created: