Moodle LMS Administration

Moodle Database · Performance & Tuning Masterclass

Optimize your MariaDB/MySQL database to handle massive user loads. This guide details InnoDB buffer configurations, dynamic table maintenance, collation repair procedures, and lock factory optimizations to keep Moodle highly responsive.

Back to Moodle Dashboard

Moodle Learning Path

1. Installation Guide 2. Manual & Bitnami Stacks 3. Enterprise Blueprint 4. Forensic Diagnostics 5. Database Masterclass 6. Plugin & API Integration 7. Web Service Playground 8. Security Hardening 9. LMS Course Curriculum

Pipeline K: Database Query & Cache Routing

Request
$DB->get_record
PHP Application Call
L1/L2 Cache
APCu / Redis
MUC Cache Intercept
Primary DB
MariaDB (Write)
Master DB Query
Read Replica
MariaDB (Read)
Secondary Replica

🗃️ Database Tuning Roadmap

1. Check database size, engine, and indexes

Why this matters
Moodle databases are index-heavy. Over time, tables like mdl_logstore_standard_log and mdl_sessions grow excessively, degrading query times. Identifying unoptimized tables is the crucial first step.
sql · Analyze Database Structure 
-- Find the top 10 largest tables in your Moodle database
SELECT table_name AS "Table",
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)",
       engine AS "Engine"
FROM information_schema.TABLES
WHERE table_schema = "moodle_demo"
ORDER BY (data_length + index_length) DESC
LIMIT 10;
Potential Issue
If any critical core table (like mdl_config or mdl_course) is using the legacy MyISAM engine, transaction rollbacks will fail and database locks will freeze the site. Moodle 5.0 strictly requires the InnoDB engine.

2. Optimize InnoDB Buffer Pool & Log Files

How to do it
Ensure the MariaDB service allocates enough memory to keep your database indexes hot in RAM. We will set innodb_buffer_pool_size to 75% of your available server memory, increase log file limits, and optimize writes.
bash · Edit server.cnf 
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# Under the [mysqld] section, add or update:
innodb_buffer_pool_size = 4G # Set to ~70-80% of total system RAM
innodb_log_file_size = 512M  # Set to ~25% of your buffer pool size
innodb_flush_log_at_trx_commit = 2 # Best balance: write logs to OS buffer hourly, instead of sync-disk per transaction
innodb_file_per_table = 1
innodb_buffer_pool_instances = 4 # Split pool to reduce concurrent access lock contention

# Save and restart MariaDB
sudo systemctl restart mariadb

3. Fix Character Sets & Collation to utf8mb4

Why this matters
Moodle 5.0 requires full UTF-8 support (utf8mb4_unicode_ci) for multilingual scripts and emojis. Running on mismatched collations throws blocking database exceptions during upgrades.
bash · Collation repair via CLI 
# Test database collation status
php /var/www/moodl_ulo2026Demo/admin/cli/mysql_collation.php --check

# Execute collation migration on the database schema
sudo -u www-data php /var/www/moodl_ulo2026Demo/admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
Potential Issue
If table schemas contain custom plugins with manual indexes, migration might run out of key prefix limits (767 bytes). Ensure innodb_large_prefix = ON is set in your MariaDB configuration file.

4. Resolve Transaction Locking & Deadlocks

How to do it
When multiple students submit answers to a quiz simultaneously, locking contention can lead to timeouts. We will monitor locks and increase thread configurations to handle bursts.
sql · Transaction Diagnostics 
-- Check active locks and transaction status
SHOW ENGINE INNODB STATUS;

-- Check running queries causing database lock queues
SELECT * FROM information_schema.innodb_trx;

-- Set a strict transaction wait timeout limit in /etc/mysql/my.cnf
-- innodb_lock_wait_timeout = 30

5. Automate Zero-Downtime Backups

Why this matters
Standard locks during database dumps freeze student actions. Using --single-transaction with mysqldump guarantees safe, transactional online backups without freezing course interactions.
bash · Backup Script 
# Create backup directory
sudo mkdir -p /var/backups/moodle
sudo chown -R root:root /var/backups/moodle

# Dynamic online transactional backup execution
mysqldump --user=moodleDemo2026 --password='2026PassDemo!!' \
          --single-transaction --quick --lock-tables=false \
          moodle_demo | gzip > /var/backups/moodle/moodle_db_$(date +%F).sql.gz

6. Integrate Redis Lock Factory

Why this matters
Offload resource locks from the database to Redis. This significantly reduces transactional locking contention and maximizes simultaneous page processing speeds.
php · config.php integration 
// Add the following Redis lock allocation settings in /var/www/moodl_ulo2026Demo/config.php:
$CFG->lock_factory = "\\core\\lock\\redis_lock_factory";
$CFG->lock_redis_server = '127.0.0.1';
$CFG->lock_redis_port = 6379;
$CFG->lock_redis_auth = ''; // Add authentication password if configured
$CFG->lock_redis_db = 2;

Core Config Vault

config.php (DB Driver) 
$CFG->dbtype    = 'mariadb';
$CFG->dblibrary = 'native';
$CFG->dbhost    = 'localhost';
$CFG->dbname    = 'moodle_demo';
$CFG->dbuser    = 'moodleDemo2026';
$CFG->dbpass    = '2026PassDemo!!';
$CFG->dboptions = array(
    'dbcollation' => 'utf8mb4_unicode_ci',
    'dbpersist'   => true
);

Performance Rules

Table Maintenance: Run weekly optimized sweeps on heavy indexes.

mysqlcheck -o -u moodleDemo2026 -p moodle_demo

Max Connections: Ensure MariaDB limits match PHP-FPM thread pools.

max_connections = 150

Database Diagnostics

  • Identify locked queries:
    mysqladmin proc stat -u root -p
  • Check index health:
    ANALYZE TABLE mdl_course_modules;
  • Clear Moodle application caches:
    php admin/cli/purge_caches.php