Child pages
  • Moodle LDAP Sync Mods

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3
Table of Contents


This document describes the mods for the Moodle LDAP Sync Mods.

The current sync process fetches all the users in GALEN LDAP, puts them into a temp table in the Moodle database, and then does a series of SQL diffs to determine who is new, modified, deleted, etc. When this process runs it locks up the system as it takes significant CPU to process the queries, and that can cause a service outage for over 10 minutes.

Details of Current Process

Currently the Moodle LDAP sync uses a predefined sync_users() function in

Code Block

The sync_users() function is called by a script which gets run from cron once a day:

Code Block

This process does the following:

  1. Creates a temp table in MySQL
  2. Fetches all users from LDAP and puts them in a temp table
    1. Only fetches the UID
  3. Removes (actually suspends) users who are no longer in ldap
  4. Revives previously suspended users
  5. Updates all users who are in the temp table
    1. Users are updated by re-looking up each user in LDAP to get all of their attributes
  6. Adds new users who are in the temp table but not the main user table

Problems with Current Process

The current sync process is problematic because it updates 26,000+ records every night even if there are no updates to these records. This is not only a resource waste, but it also causes problems for users who are using Moodle while the update is taking place.

Although the above is bad, and probably makes the process run a lot longer than needed – especially over the network from Remote Learner to UCSF – it does not cause significant impact on the system.

There may be another issue with the sync process altogether, which is that by default, Moodle uses the MyISAM storage engine for its MySQL database tables. MyISAM does not do row-level locking, and therefore, when the sync process updates thousands of records it locks the entire users table, and this could potentially cause the outage that we are seeing with Moodle when the sync runs. Switching the users table to InnoDB may solve the outage problem as it has row-level locking when an update or insert is being performed.

Using InnoDB had no impact on performance. In fact, for updates, it took slightly longer.

Proposed Solutions

There are two proposed solutions:

  1. Modify the auth.php script to add another function called incremental_sync_users()
  2. Index the username attribute of the mdl_user table

Option 1 - Modify auth.php

Option 1 requires adding a new function to the Moodle auth.php script and also writing a new script to call the new function (the same way auth_ldap_sync_users.php calls sync_users(). Ideally, we would also want this new function to get pushed up to Moodle to be accepted as a patch, as we do not want an update of Moodle to overwrite our change.

Additionally, option 1 requires changes to the GALEN LDAP server. Specifically, the following two attributes need to be indexed with equality indexes:

  • createTimestamp
  • modifyTimestamp

Indexing these attributes may require a brief outage on the GALEN LDAP server while the server is restarted. Also, LDAP will most likely be in read-only mode while these attributes are being indexed.

Option 2 - Index the username attribute of the mdl_user table

It appears that the username attribute on the mdl_user table is only indexed as part of a multi-column index, and, therefore, when the Add User query is bring run, it does not use the index.

Option 2 Query Performance Runs

Given that Option 2 is the least severe, I am going to try that on a cloned VM of rltest3.

Test 1 - Remove Users Without Index



Remove Users

52.49 sec

Test 2 - Remove Users With Index



Remove Users

52.15 sec

Test 3 - Revive Users Without Index



Revive Users

0.00 sec

Test 4 - Revive Users With Index



Revive Users

0.00 sec

Both of the Revive Users tests were done after the DB was updated from LDAP, so the 0.00 is not completely accurate. However, the query when there are users to revive, sill only takes less than one second.

Test 5 - Add Users Without Index



Add Users

4 min 24.17 sec

Test 5 - Add Users With Index



Add Users

0.42 sec


It is clear that adding an index on username greatly improves performance for the Add Users query. It has no impact on the Remove Users query, and several other indexes where tried, and all had no affect.

Given that the nagios alert does not go off until the end of the script run – when is when the Add Users query is being run – I feel it is safe to say that adding the username index will have the most benefit. It will most likely end the nagios alerts, and hence, end the situation of users not being to log in or having other problems when submitting, quizzes, etc. Of course, since the Remove Users query takes almost a minute, it could be that that query, for about a minute each night might impact users. But, given that there are 1,440 minutes in a day, one minute where things might be slow might not be that bad...

The following is the index that should be added:

Code Block
create index username_index on mdl_user (username);