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.

Continue reading

Non optimized Moodle servers can crash under light load

This ‘Speed up Moodle’ series of 4 posts will teach you step-by-step how to optimise your Linux server for Moodle. It is aimed at beginner server administrators. If you find any mistakes or inconsistencies, please comment and I’ll rectify ASAP. This first post is about optimising Apache for Moodle. Check out the other posts on optimising MySQL, installation of an opcode cache such as APC and other ways to optimise your Moodle server. Please see ‘Assumptions’ and ‘Technical notes’ at the bottom of this post.

Continue reading

How to add Google fonts to TinyMCE editor in Moodle

Google has made it easy to use great looking fonts on websites, providing over 600 cross-browser fonts to date, for free. In this short post, you will learn how to add Google fonts to the default Moodle HTML editor, TinyMCE. 

Notes

  • To complete this tutorial you must be able to login to Moodle as an administrator.
  • You will also need access to the files on the server (FTP or SSH access).
  • This tutorial is for Moodle 2.3 or above (it can be done for Moodle 2.0, 2.1, 2.2 and I’m happy to extend this blog post if someone asks me for it).
  • Adding Google fonts will have a slight impact on page load time (not noticeable in most cases). The more fonts you add, the more significant the impact.
  • This tutorial also works for other Web font services such as FontSquirrelTypeKit, Adobe Edge fonts, etc.
  • Although I have never noticed any problems with this technique, don’t come and blame me if it breaks something in your Moodle installation 😉

How to add Google web fonts to Moodle TinyMCE editor

1. Go to the Google fonts page and find a font you like

Google fonts for Moodle

Click to zoom in

2. Click on the ‘Quick use’ icon (you can click ‘add to collection’ if you want to use more than one font)

Quick use google font for Moodle

Click to zoom in

3. Scroll down to the ‘Add this code to your website’ section and copy the text in the ‘Standard’ box

Select text

Click to zoom in

4. Scroll down to the ‘Integrate the fonts into your CSS’ section

Integrate the fonts into your CSS

Click to zoom in

5. Copy all text after the colon mark, but remove the quotation marks and the semi-colon

Clean text to be copied

Click to zoom in

6. In Moodle, go to ‘Administration > Site administration > Appearance > Additional HTML’
7. Paste the text you copied in step 3 of this tutorial

Additional HTML in Moodle

Click to zoom in

8. Scroll down and click ‘Save changes’
9. Go to ‘Administration > Site administration > Plugins > Text editors > TinyMCE HTML editor > General settings’
10. Scroll down to ‘Available fonts list’
11. Type a semi-colon mark (if not already there), a name for your new font (this can be anything) and an equal sign
12. Paste the text you copied in step 5 of this tutorial (make sure there are no quotation marks)

Adding a Google font to Moodle

Click to zoom in

13. Scroll down and click ‘Save changes’

14. Open the /lib/editor/tinymce/lib.php file for editing

Lib file for TinyMCE editor in Moodle

Click to zoom in

15. Locate ‘$contentcss’ and add a new line following the example below. Replace the bit in pink with the text you copied in step 3 of this tutorial. Note that there are two fonts available in the example. Also note the dot and comma.

Extend CSS for Moodle to register Google fonts in TinyMCEClick to zoom in

16. Save and close the file

17. (optional) Purge all caches at ‘Administration > Site administration > Development > Purge all caches’

18. Your fonts are now available anywhere TinyMCE is present. Enjoy!

How to add Google fonts to Moodle

Click to zoom in 

 Note: I have not yet been able to get this to work using the ‘Custom CSS’ available in some themes. Has anyone done it?

Make your Moodle courses faster without fiddling with the server

[pulledquote]Whenever I am asked what do to make a Moodle site faster, I always end up talking about tweaking the server. Often I miss the point as a significant amount of Moodle sites are hosted on servers that cannot be tweaked (e.g. GoDaddy 4GH, etc.), or by educators who simply don’t know how to setup a server.[/pulledquote] In this post, I explore a few simple ideas to help speed up Moodle courses without fiddling with the server, most of which can be done by a teacher. I also find out that some settings make no difference whatsoever. I have ordered my tips in decreasing order of importance i.e. the ones that really matter are at the top. When reading the graphs, make sure you take a look at the scale, as differences can be minimal. It is important to remember that sound pedagogy and good course design are more important than a super fast server. 

 

For the impatient (spoiler)

 

Things that impact performance

  • Amount of blocks displayed on your course pages
  • ‘Show one section only’ in your course settings
  • Images in labels
  • Amount of sections, resources & activities
  • Activity tracking & conditional activities
  • Forum tracking
  • ‘Theme designer’ mode
  • ‘Cache language strings’ option

Things that don’t really impact performance

  • Theme
  • Course format
  • Groups/Groupings 

Note: I ran a series of tests to find out what made a difference when displaying course pages on Moodle, as a student. I did not run tests as a teacher/admin, nor did I test other areas of Moodle. If you want to know more about the methodology for these tests, and for a copy of the raw data I collected, go to the ‘Methodology’ section at the bottom of this page.

 

Blocks

Out of the box, Moodle comes with 38 different blocks, 31 of which can be used on course pages. By default, only the ‘Navigation‘ and ‘Settings‘ blocks appear on course pages. I ran a test to find out to what extent the number of blocks affect the performance of Moodle course pages. 

Q: Should I keep the number of blocks on my course pages to a minimum?

A: Yes, you should only keep the blocks that are absolutely necessary to your teaching & learning experience. A course page with all blocks displayed takes on average 64% longer to generate compared with a page with only the 2 default blocks (0.871 seconds vs. 0.528 seconds). A course page with all blocks will also use 37.5% more RAM than a page with only the ‘Navigation’ and ‘Settings’ blocks displayed (67.62MB vs. 49.9MB).

The amount of blocks displayed on a Moodle course page impacts performance

Click to zoom on any of the images

In my tests, I found out that the ‘Comments‘ block (item 9 on the graph) and the ‘RSS‘ block (item 26 on the graph) are to be avoided if you have an underpowered Moodle server. The ‘Comments’ block needed an extra 7.4MB RAM, and 206 files to display on my test page, while the ‘RSS’ block added an extra 6.2MB RAM.

Recommendation(s):

  • Keep the amount of blocks to a minimum. 
  • If you have third-party blocks (not tested here) you could try and disable blocks one by one and check your server performance. For this, you need to turn on the ‘Performance’ stats in your theme and write down the values when you load pages (Site administration > Development > Debugging > Performance info).
  • Avoid the ‘Comments’ & the ‘RSS’ blocks if your server is underpowered.

  

Number of resources/activities

Adding more resources/activities to a Moodle course makes the page look longer (Scroll of Death), but it is not obvious to a Moodle user whether it affects performance or not. I ran 10 tests, one with the ‘baseline’ amount of activities/resources, and then each subsequent test with an extra ‘baseline’ worth of activities & resources e.g. course 1 has 29 resources & activities (1 of each type), course 2 has 58, course 3 has 87, up to course 10 with 290 resources & activities.

Q: Does the amount of resources/activities on a Moodle course page affect performance?

A: Yes it does, quite a bit but not as much as I thought prior to the tests. On average, adding 29 resources & activities to a course page added an extra 0.5 to 0.6MB of RAM, and the system needed an extra 0.1 to 0.2 seconds to generate the pages (although it was somehow not always the case). Multiplying the amount of activities/resources by 10 (or 1,000%) only slowed down the server by 147%, and increased the amount of RAM needed to generate a course page by 10%.

Amount of RAM used to generate a Moodle page depending on the number of activities and resources

Amount of RAM used to generate a Moodle page depending on the number of activities and resources

Recommendation(s):

  • Use folders if you cannot reduce the amount of resources on your course (for files).
  • Try to minimize the amount of resources you display on a page. It is sometimes possible to use Moodle tools to achieve this e.g. book module, database, lesson, wiki, etc. 
  • Change your course settings to ‘Show one section only‘ if you are on Moodle 2.3 or above.
  • Teach students how to use the ‘highlight’ option to show only 1 section/week at a time (usually a light-bulb icon).

 

Images in labels

If a picture is worth a thousand words, my courses are a few bibles worth. I always encourage teachers to use images on their Moodle courses as  not only does it make the courses look more appealing, it may also help with navigation.

Q: If I add a lot of images on my course pages, will it affect performance?

A: Yes, it will. The number of reads/writes to the database skyrockets when you add images in labels. A page with 100 images requires over 3 times the amount of calls to the database compared with a page with no images in labels. Looking at iMacros loading pages during the tests, I also noticed that images inserted as URL’s (i.e. not uploaded to Moodle) show up much faster in Moodle pages than the images uploaded to Moodle. 

Number of DB reads to generate a Moodle course page - Number of labels (with images)Number of DB writes to generate a Moodle course page - Number of labels (with images)

Recommendation(s): 

  • If you want to show multiple images, perhaps you could look at ‘Folders‘ instead of individual images in labels.
  • Use ‘Pages‘ to add your images to your courses, instead of directly on course pages.
  • Use only images that are useful to your course page e.g. images you cannot really do without.
  • If at all possible, simply link to your images rather than uploading them to Moodle.

 

Course sections

With Moodle 2.3 and above, you are able to ‘Show all sections on one page’ or ‘Show one section per page’ as your course layout (Edit course settings > Course layout)

Q: Does the ‘course layout’ I choose have an impact on Moodle performance?

A: Yes, on average ‘Show one section per page’ made 19% fewer database reads than the default setting. This allowed for marginally faster page generation (-4%). The database is often the first thing to cause a bottleneck on a Moodle server, so you might want to explore this avenue if your server is currently struggling. 

Time to generate a Moodle course page - Course sections optionNumber of DB reads to generate a Moodle course page - Course sections option

Recommendation(s): 

  • If you are running Moodle 2.3 or above and your server is struggling, it might be a good idea to try if ‘Show only one section per page’ makes a difference for you. 
  • It is worth noting the course pages look different with this setting, so it might take your users a while to get used to it.

 

Conditional activities & completion tracking

Conditional activities were introduced in Moodle 2.0 and have been very popular feature. It allows you to create learning pathways, control the flow of a course or even gamify your courses. 

Q: I love the ‘Conditional activities’ features of Moodle 2, should I stop using it on my cheap server?

A: It depends, but probably yes. I found that for courses with few activities & resources, this setting has very little impact (+2%). However, when more activities were involved, the simple fact of turning the ‘Completion tracking’ feature at the site level (Site Administration > Advanced Features > Enable Completion Tracking) and at the course level (Edit course settings > Completion tracking) increased the time needed to generate a page by 29%. When I tested the same course with activities depending on one another (Site Administration > Advanced Features > Enable Completion Tracking > Enable Conditional Access) the time increase was ‘only’ 27%. 

Time to generate a Moodle course page with activity tracking and conditional activities on

Recommendation(s):

  • Design good courses, use conditional activities/resources should it benefit teaching & learning.
  • Again, this feature impacts teaching & learning too much to be left behind, get rid of it only if you must, or if it is completely useless in your context.

 

Forum tracking

It is possible to have Moodle show how many new forum posts were written since you last visited a course page, for each forum. You can switch this option on/off  at the user level (Edit profile > Forum tracking).

Q: I like my students to share things with the forums, should I disable the tracking function?

A: It depends, really does. It mainly depends on how active your forums are. I conducted 3 tests,  one where there were only 10 unread forum posts in 1 forum, another with 500 unread posts, and yet another with a 1,000 unread posts. I found no noticeable difference with only 10 unread posts but a 23% increase in the time needed to generate a Moodle course page with 500 or 1,000 unread posts. Strangely enough there was no noticeable difference between 500 unread and 1,000 posts.

Time to generate a Moodle course page with forum tracking on

Recommendation(s):

  • My tests were run with one forum only. If you have a lot of very active forums, this setting could well cost you quite a bit in terms of performance.

 

Course format

By default, Moodle has 4 different course formats. For the purpose of this test, I only compared the ‘Topics’ and ‘Weekly’ formats, as they are the most widely used (Edit course settings > Format)

Q: Will the course format I choose affect performance? 

A: Very, very little. The difference is minimal, with a slight edge to the ‘Topics’ format being 4.7% faster. It is worth noting that the amount of topics/weeks shown does impact the performance, even if those are empty. A course page with 52 topics took 25% longer to generate than the baseline course page, which has only 3 topics.

Time to generate a Moodle course page depending on course format

I must say I was relieved to see no real difference between the course formats, as the decision to use either ‘Topics’ or ‘Weeks’ is a fundamental one, often you won’t even have the choice and will be forced by your institution. 

Recommendation(s):

  • Choose whichever course format you want!
  • If you use the ‘topics’ format, keep an eye on sections – limit the amount of sections if that doesn’t impact your course design (at the end of the day, it’s all about teaching & learning NOT the performance).

  

Groups & groupings

Teachers have the ability to setup their course with different groups. I ran a test to check if the ‘Separate groups‘ made a difference (Edit course settings > Group mode). I also ran a test to find out whether the ‘Show to groups members only‘ option had an impact on performance (Site administration > Development > Experimental settings > Enable group members only).

Q: Do the different group modes affect performance? 

A: Not really, if anything it has a positive impact on performance. Whilst it might affect other parts of Moodle (not tested), it certainly doesn’t affect performance as far as course pages are concerned – in fact it seems to speed things up marginally.

Time to generate a Moodle course page - Groups: groupings

Recommendation(s):

  • ‘Groups’ are too useful to not be used, it even seems to make things marginally better in terms of performance, so go for it!
  • The ‘Show to group members only’ also has a positive impact on performance.

  

Themes

Q: Does it matter which theme I use? 

A: If you’re using any of the themes that come standard with Moodle, the answer is ‘not really’. All of the themes performed pretty much the same in my tests. ‘Boxxie‘ was the fastest theme with an average of 0.494 seconds to generate a page, when ‘Fusion‘ and ‘Sky high‘ were the slowest at 0.526 seconds on average. It is important to keep in mind that the differences are so minimal that it could be put down to external factors (I/O on the server, etc.), rather than the themes themselves. I did not test any third-party themes

 Time to generate a Moodle course page with all box-standard themes

Warning: Please make sure the ‘Theme designer mode’ is set to ‘OFF’ on your Moodle installation (Site administration > Appearance > Themes > Theme settings > Theme designer mode). This is often the cause of slow Moodle sites. I found that on average it takes at least twice the amount of CPU to run a Moodle site with theme designer mode ON, as shown below.

Load average to generate a Moodle course page - Theme designer mode

Recommendation(s):

  • Pick the theme you like best, it doesn’t ‘cost’ you in terms of server resources
  • If you’re using a custom theme, make sure you compare it with the ‘Standard’ theme, as it’ll give you a good idea of how well it performs

 

Bonus – Cache language strings

Someone recently told me that their site ran faster with the ‘Cache language strings’ option left ‘off’ (Site administration > Language > Language Settings > Cache all language strings). I was intrigued and decided to test it. 

Q: Does the ‘Cache language strings’ option have an effect on Moodle performance?

A: Yes, in my tests course pages were created 64% more slowly with the cache option turned off.

Time to generate a Moodle course page with the cache language strings off

Recommendation(s):

  • This one is obvious – leave it on (it is on by default)

 

Methodology

 

Server and technical

  • 1GB RAM VPS from DigitalOcean (2 Virtual CPU’s) – You can run your own tests using the same server (you pay by the hour) so that you can easily compare with my ‘benchmarks’
  • Ubuntu 12.04LTS
  • As vanilla a LAMP as it gets (no modifications/customisations whatsoever)
  • atop and htop installed

Moodle

  • Moodle 2.4 Beta
  • No customisations
  • No 3rd party plugins
  • All default settings left untouched
  • Only default modules & blocks
  • 200 courses
  • Each course has 1,000 users (students) enrolled through cohort sync
  • Standard theme

Courses

  • All courses share the same backbone i.e. all have an example of each module (1 quiz, 1 page, 1 folder, etc.)
  • Some courses have been tweaked for particular tests e.g. course 10 has twice the amount of activities
  • Some courses have different blocks enabled to allow for the ‘blocks’ tests to be run 
Tests
  • Every test was run between 50 and 100 times (altogether over 20,000 pages were generated)
  • I used iMacros to run the tests and scrape the data 
  • All tests were run as students (50 to 100 different users per test)
  • Browser cache was reset for each user (between 50 to 100 times per test) as to not skew results
Raw data
  • Here is my contribution to big data – raw results in CSV format
  • There are parts of the data that I have not analysed, so have a go if you like
  • If you use the data for anything, please let us know – I’d like to see the results

Have you run any tests of your own? Feel free to share your results in the comments section below. If you want to know more about how I ran individual tests, simply write a comment and I’ll indulge.