Boosting Performance by Using Read Write Splitting of Database Traffic with Moodle 3.9

Moodle is a very well known Learning Management System which is intended to help educational organizations organize their online learning activities. As you can imagine, given the online shift in 2020 caused by the COVID-19, such systems became very popular and the load those systems have to handle has increased significantly. Many administrators are wondering how to improve the performance of the database that backs up Moodle installation. Luckily, if you are running Moodle 3.9 or later you have some built-in options that can help you to boost performance. In this blog post we will show you how to do it.

First of all, we assume that you have a Moodle installation with a single database node. Let’s take a look at the steps you may want to take to improve the performance of your Moodle database. Of course, all of the steps that we explain here can be performed by hand. We are going to use ClusterControl for that as we value our time.

Assuming you have ClusterControl installed, the first step will be to import an existing database node.

The SSH connectivity using a passwordless key has to be in place. We have it set up as a root user with an SSH key located in /root/.ssh/id_rsa.

As the next step we defined the superuser and its password. We also enabled information_schema queries (as we know we don’t have tens of thousands of tables) and both autorecovery options so ClusterControl will be able to recover our database if needed.

After a brief moment our database shows on the list of clusters:

Now, we can start to scale out our cluster by adding more slaves. We should ensure that the master has binary logs enabled. If not, it can be done from the ClusterControl. Please keep in mind that enabling binary logs requires restart so you probably want to do it in a time when the load is the lowest and, ideally, giving some heads up to the users of your Moodle platform.

We passed the IP (or hostname) of the node we want to use as a slave. ClusterControl will provision it with the data from our master node. We could also use backups to provision a slave but we haven’t taken any backups using ClusterControl so far.

Installation will take a couple of minutes, we can follow the progress by looking at the job logs in ClusterControl.

Adding a slave to our system doesn’t make any difference. We have to tell Moodle to actually start using it. Luckily, Moodle has a feature that lets you configure slave nodes and then the “safe” writes will be redirected to them, reducing the load on the master and improving overall performance. 

In the configuration file (config-dist.php) you can see the ‘readonly’ section of the ‘dboptions’ array. In that place you can define one or more slave nodes that will be used by Moodle to send the traffic to.

  'readonly' => [          // Set to read-only slave details, to get safe reads

                            // from there instead of the master node. Optional.

                            // Currently supported by pgsql and mysqli variety classes.

                            // If not supported silently ignored.

     'instance' => [        // Readonly slave connection parameters

       [

         'dbhost' => '10.0.0.132',

         'dbport' => '',    // Defaults to master port

         'dbuser' => '',    // Defaults to master user

         'dbpass' => '',    // Defaults to master password

       ],

       [...],

     ],

As you can see, we can add more than one slave host, allowing us to spread the safe reads across multiple nodes which you can easily provision from ClusterControl and reduce the load on the cluster.

If you are interested in more advanced, highly available database setups for Moodle, we have several blog posts on this topic describing, among others, how you can utilize Moodle with Galera Cluster as a backend. We also described the more advanced scaling techniques for Moodle, involving ProxySQL load balancing.

Let us know your thoughts and experience on working with Moodle.