Moodle on OCI with MySQL HeatWave: Extended Architectures – part 1

In the previous post, we saw how to quickly deploy Moodle to Oracle Cloud Infrastructure on Ampere compute instances and using MySQL HeatWave.

In this post, we will explore some other features and the benefits of running on OCI and MySQL HeatWave to extend our architecture dedicated to Moodle in the Cloud.

Read Replicas

Moodle natively offers the possibility of distributing the load between reads and writes. When using MySQL HeatWave Database Service, adding read replicas is also a very easy task. Let’s see how we can benefit from it.

To be able to use MySQL HeatWave Read Replicas, the MySQL shape must have at least 4 OCPUs.

Let’s modify the moodle stack and deploy it again but this time we choose a bigger shape for MySQL:

When everything is ready, we can now enable the Read Replicas:

We choose a name for the replica and optionally we enter a description:

When the read Replica is created, we can see it active and we get its IP:

Our aim when deploying read replicas is to offload Moodle read requests to a second server (replica). We can use the IP of the replica, but if later we want to add another read replica, we’ll have to modify Moodle’s configuration again to add the second replica and so on… With MySQL HeatWave, when you deploy a new read replica, you automatically get an internal load balancer that will balance connections between all the read replicas you’ve deployed, current or future. It’s a more elastic solution and the one we’ll be using in our architecture:

The next thing to do is to modify Moodle’s configuration to use the Load Balancer for all readings.

Connection to the Compute Instance

Moodle is running on an Ampere Compute Instance, when we used the Terraform’s Stack, a Private SSH Key has been created. We need that key to connect to our Moodle application server in SSH:

We copy the content of the key on a file locally (it’s also possible to use Cloud Shell in OCI’s dashboard).

Pay attention that the key must be formatted as expected, replaces the spaces by a carriage return (new line) at the end of each lines.

Then you can use the key to connect to Moodle’s compute instance using the opc user and the key:

Moodle DB settings

We need to modify Moodle’s configuration file to enable the Read/Write splitting and use the new Read Replica.

We open the file /var/www/html/config.php and in the section $CFG->dboptions, we add after the dbcollation line the following lines:

  'readonly' => [
          'instance' => '10.0.1.19',
          'connecttimeout' => 5,
          'latency' => 2,
  ],

Where 10.0.1.19 is the IP of the Read Replica Load Balancer.

When the file is modified and saved, this is it ! Now Moodle will offload all the reads to the load balancer and they will reach the Read Replica.

We can add a Second Replica the same way we added the first one to have something like this:

This is an illustration of the architecture we just deployed:

To verify that the reads are also sent to the Read Replicas, we can connect directly to them (from the Moodle’s compute instance) using MySQL Shell:

And we can run the following SQL query:

select schema_name, format_pico_time(total_latency) tot_lat,
       exec_count, format_pico_time(total_latency/exec_count) latency_per_call, 
       query_sample_text
  from sys.x$statements_with_runtimes_in_95th_percentile as t1      
  join performance_schema.events_statements_summary_by_digest as t2        
    on t2.digest=t1.digest
 where schema_name ='superset' and query_sample_text like 'select%' 
 order by (total_latency/exec_count) desc limit 10\G

Conclusion

Once again, you can see how easy it is to extend your MySQL architecture in OCI.

MySQL HeatWave Database Service provides you several key features for an easy deployment.

In part 2, we will extend it even more. Stay tuned !

Enjoy MySQL, MySQL HeatWave on OCI and Moodle !