How to optimize a Moodle server? Part 2 – MySQL

Optimize MySQL database on Moodle

This post is part of ‘Speed up Moodle’, a 4 post series showing ways to optimize a Moodle server. Moodle is a database driven application, and the faster the database, the faster your Moodle installation will be. In the second post, I show you a simple way to increase your Moodle database performance, focusing on MySQL. Do keep in mind that fine tuning a database is a vast topic, and entire books have been dedicated to it. In this post, I only focus on the part that will likely show the most improvement.

Disclaimer: I am not a professional server administrator but I have looked after Moodle servers for several years now, and never experienced major issues. What I am sharing has worked for me, but might not work for you. You’ve been warned! 

Why it matters

As I explained in a previous post, Moodle builds all of the pages it displays on-the-fly, as they are requested by users (i.e. pages are not ‘stored’ on disk). For each page generated, Moodle reads data from the database, and also writes to it. As your database is stored on disk, the speed at which Moodle can read/write from this storage is vital: the faster the storage, the faster Moodle will be. On my test server, as well as cheaper Virtual Private Servers, disk I/O (or speed) is (really) slow. The ‘secret’ to making your database faster is to make heavy use of your available RAM, as RAM is much faster than disks.

Calculate Moodle database size

The first thing to do is work out how much space the Moodle database uses on storage.

mysql - u root -p

Type the password and press enter. Then type:

SELECT table_schema "DB", sum(data_length + index_length)/1024/1024 "DB size in MB" FROM information_schema.TABLES GROUP BY table_schema;

It will return results similar to this:

+--------------------+---------------+
| DB                 | DB size in MB |
+--------------------+---------------+
| information_schema |    0.00878906 |
| moodle             |  251.82812500 |
| mysql              |    0.65609837 |
| performance_schema |    0.00000000 |
| phpmyadmin         |    0.02246094 |
+--------------------+---------------+
5 rows in set (1.26 sec)

In this example the Moodle database takes up only just over 250MB of space. Make a note of this figure, you will need it later.

Note: It is likely your database will take up a much more significant amount of space.

Calculate innodb_buffer_pool_size value

This MySQL setting is likely to have the most significant impact on your Moodle installation. In a nutshell, it determines how much of your database can be stored in the RAM (a proper explanation here).

  • Your database is also likely to grow, and you may wish to add some extra space to prepare for it. For example a further 10%. You would be better off checking your database size every few months and adjust innodb_buffer_pool_size rather than setting too high a value for it, in a bid to ‘future proof’ your server, as it simply wastes RAM that could be used for other purposes.
  • Add 10% as InnoDB might need it, as explained here.

As per the example:

  • 252MB + (10% of 252MB) = 277MB
  • 277MB + (10% of 277MB) = 305MB

In this example, I would set innodb_buffer_pool_size to 305MB

Edit ‘my.cnf’ MySQL configuration file

You now need to add the calculated value to your ‘my.cnf’ file.

sudo nano /etc/mysql/my.cnf
  • Use the arrows on your keyboard to navigate the file, as the mouse won’t work
  • Scroll down to the # * InnoDB sections
  • Add
innodb_buffer_pool_size = xxxM
  • Replace xxx with the value you calculated, here 305MB
  • When finished editing press Ctrl X on your keyboard and Y when prompted to save the file
  • Restart the MySQL server
sudo service mysql restart<span style="text-align: justify;"> </span>

MySQL InnoDB Configuration file

Click to zoom in

Useful links

As mentioned at the beginning of this post, MySQL InnoDB optimization is a vast topic, and you might find the following links of use.

Percona configuration tool

This free tool will guide you through generating an optimized MySQL configuration file. This is my ‘go-to’ configuration tool when optimizing a MySQL server.

High performance MySQL, 3rd edition

This great book covers benchmarking and optimization, among other topics.

Interesting discussions and blog posts

I have found these discussions very useful: 1, 2, 3.

Bonus – run your server for a while & use diagnostics tools

Tools such as tuning-primer.sh and mysqltuner.pl can help you optimize your MySQL server further. Those tools look through MySQL server usage and give an idea of what needs to be done in order to tweak a server. I have added those tools here as they are popular but some database specialists do not rate them highly. If you are running Moodle 2 or above, you are most likely using the InnoDB engine so you may safely disregard any advice about MyISAM engine.

1. Tuning-primer.sh

  • Copy/paste the following code into your SSH window (press enter for every line)
wget http://www.day32.com/MySQL/tuning-primer.sh
cd /root/
chmod +x tuning-primer.sh
./tuning-primer.sh
  •  You will get results that look like these (you might need to enter your admin password first)

tuning-primer.sh results example

Click the image to zoom in

  • You need to look for the parts in red, as they are recommendations for you to follow.

2. Mysqltuner.pl

  • Copy/paste the following code into your SSH window (press enter for every line)
wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
cd /root/
chmod +x mysqltuner.pl
./mysqltuner.pl
  •  You will get results that look like these

Mysqltuner.pl for Moodle

Click the image to zoom in

  • You need to look for the parts signalled by the [!!] symbol and the ‘Recommendations’ section at the bottom of the results

In the next post, you will learn how to disable unnecessary Apache modules to save on RAM usage.

Photo credit: Speed Bump Ahead by VeggieFrog

4 comments

Leave a Reply

Your email address will not be published.