Performance Issue with User Enrollment Count in High-Concurrency Moodle Site

Performance Issue with User Enrollment Count in High-Concurrency Moodle Site

by Mohamed Atia -
Number of replies: 4
Hi All,

I am encountering a performance issue on my Moodle site, which has courses with tens of thousands of users (ranging from 50,000 to 80,000 users per course), and the total number of users on the site exceeds 500,000. Our theme displays the count of users per course, and certain pages show more than 20 courses per page.

The problem arises when we have a high number of concurrent users, typically ranging from 1,000 to 2,000 users. It appears that the database becomes overloaded. Upon investigation, I found that the 'count_enrolled_users' function in the Moodle core, located in the file 'lib\enrollib', takes around 8 seconds to execute when there is high user concurrency. Consequently, this leads to numerous database threads, indicating that the enrollment count might need to be handled differently. One potential solution could be utilizing Moodle cache.

I would greatly appreciate it if anyone has any ideas or configurations that I could implement to overcome this issue. Should this be considered an enhancement request for Moodle core, or am I missing any specific configuration settings to handle a high number of concurrent users?

Below is the code for the aforementioned function and a section of a slow query for reference:


function count_enrolled_users(context $context, $withcapability = '', $groupid = 0, $onlyactive = false) {

    global $DB;


    $capjoin = get_enrolled_with_capabilities_join(

            $context, '', $withcapability, $groupid, $onlyactive);


    $sql = "SELECT COUNT(DISTINCT u.id)

              FROM {user} u

            $capjoin->joins

             WHERE $capjoin->wheres AND u.deleted = 0";


    return $DB->count_records_sql($sql, $capjoin->params);

}

Part of Slow Query Report

# User@Host: mahara[mahara] @  [10.144.243.99]

# Thread_id: 32  Schema: moodle  QC_hit: No

# Query_time: 9.787826  Lock_time: 0.000050  Rows_sent: 1  Rows_examined: 128204

# Rows_affected: 0  Bytes_sent: 79

# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No

SET timestamp=1686830092;

SELECT COUNT(DISTINCT u.id)

              FROM mdl_user u

            JOIN mdl_user_enrolments ej13_ue ON ej13_ue.userid = u.id

JOIN mdl_enrol ej13_e ON (ej13_e.id = ej13_ue.enrolid AND ej13_e.courseid = '7')

             WHERE 1 = 1 AND u.deleted = 0 AND u.deleted = 0;

Any suggestions or insights would be greatly appreciated. Thank you in advance!

Average of ratings: Useful (1)
In reply to Mohamed Atia

Re: Performance Issue with User Enrollment Count in High-Concurrency Moodle Site

by Dan Marsden -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators
This sounds like a performance issue in your theme to me... you shouldn't need to generate that query on every page load of a course - if you really need to display the user count you should probably put it into a cache value.

Larger sites like yourselves need to be extremely careful when choosing a 3rd party theme - many 3rd party themes are developed in a way that "looks good" but the 3rd party developer hasn't written them in a way that peforms well under larger load.

This isn't an enhancement request for "moodle" but an enhancement request for the 3rd party theme you are using - you should reach out to the theme developer or report an issue in their issues tracker related to it.
Average of ratings: -
In reply to Dan Marsden

Re: Performance Issue with User Enrollment Count in High-Concurrency Moodle Site

by Mohamed Atia -
Hi Dan,

Thank you for your response. I understand your point about the performance issue potentially residing within the theme. However, I wanted to clarify that this problem is not limited to the third-party theme we are currently using. We have observed the same issue occurring in the Boost theme when a particular block, responsible for displaying the enrollment count per course, is present. Upon investigating, we discovered that the block calls the function I mentioned in my initial post, which is located in the Moodle core file 'lib\enrollib'.

To address this performance concern, I took the initiative to apply a caching technique. Leveraging my development experience and following Moodle documentation, I created a function within the problematic block and implemented caching. The function, named 'mahara_count_enrolled_users', has yielded significant performance improvements. Prior to implementing the caching technique, with 2,500 concurrent users, we observed 392 database threads. However, after applying the caching technique, the number was reduced to 19 threads.

Given these findings, I believe that incorporating caching at the Moodle core level for the enrollment count could potentially resolve the issue across different themes, including the third-party one we are currently using.

While I understand that this may not be an enhancement request for Moodle itself, but rather for the specific third-party theme, I wanted to explore the possibility of a more general solution that could benefit multiple themes. do you think there is merit in considering a broader enhancement at the Moodle core level?

Thank you for your insights and guidance.

Best regards,
Average of ratings: -
In reply to Mohamed Atia

Re: Performance Issue with User Enrollment Count in High-Concurrency Moodle Site

by Mohamed Atia -
here is the cache definition and function I used
<?php
$definitions = [
'count_enrolled_users' => [
'mode' => cache_store::MODE_APPLICATION,
'ttl' => 1800, // Set the desired TTL value in seconds
]
];

the function

function mahara_count_enrolled_users(context $context, $withcapability = '', $groupid = 0, $onlyactive = false) {
$cachekey = 'edumy_enrolled_users_count_' . $context->id . '_' . $withcapability . '_' . $groupid . '_' . (int)$onlyactive;
$cache = cache::make('theme_edumy', 'count_enrolled_users');
if ($cache = cache::make('theme_edumy', 'count_enrolled_users')) {
if ($cachedcount = $cache->get($cachekey)) {
return $cachedcount;
}
}
$count = count_enrolled_users($context);
$cache->set($cachekey, $count);
return $count;
}
Average of ratings: Useful (1)
In reply to Mohamed Atia

Re: Performance Issue with User Enrollment Count in High-Concurrency Moodle Site

by Dan Marsden -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators
cool - thanks for sharing! -

Keep in mind that Cache's aren't "free" - they use up some level of resource, and it's best not to create a cache that is not actually used, ideally you would have a cache defined by the plugin that requires them. (eg in the block that you mention above.) - but If you can identify an area in core Moodle that would benefit from that value being cached, then it would be great to see a tracker issue around this with your patch and then the other areas could also benefit from that improvement.
Average of ratings: -