|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
sync_users() function is called by a script which gets run from cron once a day:
This process does the following:
- Creates a temp table in MySQL
- Fetches all users from LDAP and puts them in a temp table
- Only fetches the UID
- Removes (actually suspends) users who are no longer in ldap
- Revives previously suspended users
- Updates all users who are in the temp table
- Users are updated by re-looking up each user in LDAP to get all of their attributes
- 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.
There are two proposed solutions:
- Modify the
auth.phpscript to add another function called
- Index the
usernameattribute of the
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
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:
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
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
Test 2 - Remove Users With Index
Test 3 - Revive Users Without Index
Test 4 - Revive Users With Index
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
4 min 24.17 sec
Test 5 - Add Users With Index
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:
create index username_index on mdl_user (username);