How to optimize a Moodle server? Part 2 – MySQL

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 explanationhere).

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>

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)

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