MariaDB

From MoodleDocs

MariaDB is a MySQL fork that is developed and maintained by original MySQL developers organised under MariaDB Foundation. It is considered to be more open and is being distributed as default MySQL compatible database by majority of modern Linux distributions. More modern versions of MariaDB also feature better performance than MySQL with lower latency and higher throughput - MariaDB themselves actually ran a benchmark against MySQL, the results of which can be seen here: Benchmark: MariaDB vs MySQL on Commodity Cloud Hardware.

MariaDB is a drop-in replacement for Oracle MySQL, you can use it with any stable supported Moodle version using the standard mysqli drivers that ship with PHP by default.

MariaDB driver in Moodle

There is a dedicated driver in Moodle for MariaDB. It is recommended to explicitly specify mariadb Moodle driver in config.php:

$CFG->dbtype = 'mariadb'; $CFG->dblibrary = 'native';

At the moment the driver is similar to MySQL but in the future the implementations may diverge significantly. Moodle MariaDB driver is not compatible with MyISAM database engine.

See MySQL for more information, the setup procedure is nearly identical.

Installation

Installing MariaDB is relatively straightforward, and is similar to installing any package on Linux or Windows - you can head to the site and download what you need directly (mainly for Windows), otherwise on Linux I'd grab it through APT like most packages.

A good guide for installing/setting up MariaDB post-installation has been written by DigitalOcean: How To Install MariaDB on Ubuntu 22.04, but if you're going to install a LOMP Stack anyway, this guide might be more relevant: How To Install Linux, OpenLiteSpeed, MariaDB, PHP (LOMP stack) on Ubuntu 22.04. Note that the first guide may be more relevant as it also covers how to change a user to password authentication if required (the default on Linux is now Unix-Socket Authentication), but it could be worth having a quick look at both - they're very similar (and easy to flick through) otherwise.

Setting up the database is give or take exactly the same as MySQL (although you don't need to worry about setting the server character set to utf8mb4 or enabling InnoDB, as this is the default in MariaDB - just make sure to specify it when creating the database), so the instructions can be followed as per Installing Moodle or the Step-by-step Installation Guide for Ubuntu.

Optimizing MariaDB Post-Installation & Tuning

MariaDB out of the box comes pretty well tuned, and according to the developers, unnecessary tuning may actually reduce performance, so proceed with caution here. There's a whole section on Optimization and Tuning in the MariaDB Docs: Optimization and Tuning, but most of it is largely out of scope for users here as it talks about designing database queries and the like etc, so I'll skip past it for the moment and continue to the couple of bits the internet seemed to have consensus on about optimizing performance. First - Memory usage.

Memory Allocation

There's a dedicated page in the MariaDB Docs to Memory Allocation, and I'd say this is potentially the most useful page I found within those docs. The short of it is similar to MySQL, the majority of the Performance Gains to be had come from correctly configuring the innoDB_buffer_pool_size, depending on the memory you have available. Unfortunately due to how wide-ranging this can be, I can't really offer any sort of guidance for the most part here - it all depends on your setup, and how much RAM you have, and what else is running on the server. As a minimum though, if you're building something lightweight, I wouldn't really allocate any less than 1GB to MariaDB total, considering how cheap RAM can be with hosting these days, so it's a piece of mind thing more than anything else. This is however based off a relatively small Moodle Server, so naturally this will change as you scale. This GitHub page (dronezzzko/mysql-mariadb-tuning-and-optimization-for-best-performance.md) does offer some suggestions about optimizing MariaDB depending on your memory, though I'd again recommend having a look at what the options actually do (definitely don't just blindly set them) before making a decision as to whether they're right for you - the MariaDB Docs are pretty good in this respect. Here's the page you'll be looking for if that's the case: Server System Variables.

Continuing back along our original Memory Allocation page, I would read this page and have a look at what it says - it's actually nowhere near as long as it looks, but does offer some other useful suggestions:

  • Adjusting parameters depending on your configuration: 64-bit OS and MariaDB¶ (Generally you should be 64-bit these days).
  • Adjusting key_buffer_size, and reducing this if you're not using MyISAM Tables, (It's only relevant to have a large key_buffer_size if you're using MyISAM Tables, which by default MariaDB uses InnoDB. General consensus is InnoDB offers better performance as well, so really only use MyISAM if you have a reason to).
  • Adjusting table_open_cache (previously called table_cache) - in Performance recommendations under MySQL, it recommends having this as 512 for most Moodle Installations, but I left it at the default MariaDB setting of 2000.
  • Adjusting the query_cache - this differs from MySQL where by default it is disabled in MariaDB. Enabling it may even reduce performance if it isn't needed, as per the docs. I unfortunately don't know enough about how Moodle uses the database as to whether it is better to have this on or off these days, so went with the MySQL recommendation of On, but with a query_cache_size of 64M (seems to be the recommendation of many other sites, as memory is plentiful these days). I did however leave the query_cache_min_res_unit with the default of 4096 (4KB).
  • Adjusting the thread_cache_size - as mentioned on the page, normally this doesn't have a huge effect on things, so leaving it as default (256) doesn't seem to do too much damage. Other sites however recommend setting this at a value of 16 or so - the best thing would be to test it in your setup as recommended, and see what works best. I also tried a setting of 16 to no real noticeable difference on a small Moodle site.
  • Adjusting Swappiness - Swappiness is how aggressively the OS will swap to and from RAM to disk - useful for applications you're using and then quickly switching, not so much for databasing where the buffer_pool could then be swapped out to disk, defeating the purpose of the buffer_pool. I set this to the recommended value of 5.


Other Adjustments from the Inter-webs:

  • I adjusted tmp_table_size to 64MB, seems to be recommended a lot over the internet, and I figured it couldn't hurt due to the amount of RAM we have available (default is 16MB for this).
  • I also adjusted max_heap_table_size to 64MB again, on the recommendation of the internet. Looking at the information as to what it does, I figured it couldn't hurt - default is again 16MB.
  • Following the GitHub suggestion linked above, I also set innodb_flush_log_at_trx_commit to 0 - this means the database will no longer flush the to disk every time a transaction is performed, meaning it no longer conforms with ACID Principles, but however giving you a noticeable performance boost. This does mean if you lose power, you will lose the last second of transactions, but I'd prefer to have a UPS installed with Auto-Shutdown configured instead, or if in a datacenter some sort of failover and redundancy there. If you are turning this off though, make sure you have some sort of alternative to have those transactions written to disk in place - the last thing you want is to corrupt your Moodle Installation! (Hence why you have a Site backup of course).
  • I also changed the log_slow_query_time to 1, meaning that anything taking longer than 1 second to process is considered a slow query. Seems quick to the naked eye, but bear in mind usually MariaDB/MySQL connections last milliseconds.
  • Finally, I also enabled skip_name_resolve by setting it to 1 - this means that only IP addresses are used for connections, and host names are not resolved. Something to consider if your webserver isn't setup correctly with reverse DNS (where DNS Lookups will continuously fail and slow down the database), but also on our installation the database and webserver are on the same box, meaning the only address it needs to resolve is localhost/itself anyway, so this doesn't really hurt anything (I think).


That should do most of the heavy lifting and get your database server running reasonably well hopefully - in most cases, if you're getting beyond the point of these settings not working and needing to be (re)configured for more connections/transactions/memory usage, you'd also be familiar with what your needs are and therefore what needs to be configured and how, so that's a problem that almost solves itself documentation wise. In any case, we arrive back at the start point - unfortunately every installation is different, and so every one will need you to look at what you have available and what your needs are, and to configure appropriately.

But if you've made it this far, congratulations! You should hopefully have a working MariaDB Server (and Moodle instance by extension), so time to take a break and celebrate - buy yourself something nice (a coffee perhaps?) :)

FAQ

It fails during installation due to binlog_format configuration: In my.cnf file set binlog_format = ROW under [mysqld] and restart mysql service

See also