We are using moodle on mysql and have very poor performance with e.g. 5k concurrent users. We use redis, serveral frontend servers...
We found poor performance when user is doing request on /my/ when there are 10k entries in course_categories.
Following is stack trace:
script_filename = //srv/www/moodle-latest/my/index.php
[0x00007f4435215730] query() /srv/www/moodle-latest/lib/dml/mysqli_native_moodle_database.php:1272
[0x00007f44352155f0] get_records_sql() /srv/www/moodle-latest/lib/dml/moodle_database.php:1624
[0x00007f4435215520] get_record_sql() /srv/www/moodle-latest/lib/dml/moodle_database.php:1596
[0x00007f4435215440] get_record_select() /srv/www/moodle-latest/lib/dml/moodle_database.php:1575
[0x00007f4435215370] get_record() /srv/www/moodle-latest/lib/accesslib.php:6731
[0x00007f4435215290] instance() /srv/www/moodle-latest/course/classes/category.php:2761
[0x00007f4435215220] get_context() /srv/www/moodle-latest/course/classes/category.php:2708
[0x00007f4435215140] prepare_to_cache() /srv/www/moodle-latest/cache/lib.php:72
[0x00007f44352150d0] __construct() /srv/www/moodle-latest/cache/classes/loaders.php:763
[0x00007f4435214fd0] set_many() /srv/www/moodle-latest/course/classes/category.php:398
[0x00007f4435214ea0] get_all() /srv/www/moodle-latest/calendar/lib.php:1235
[0x00007f4435214d70] set_sources() /srv/www/moodle-latest/calendar/lib.php:1137
[0x00007f4435214c80] create() /srv/www/moodle-latest/blocks/calendar_month/block_calendar_month.php:54
[0x00007f4435214b90] get_content() /srv/www/moodle-latest/blocks/moodleblock.class.php:341
[0x00007f4435214b10] formatted_contents() /srv/www/moodle-latest/blocks/moodleblock.class.php:235
[0x00007f4435214a60] get_content_for_output() /srv/www/moodle-latest/lib/blocklib.php:1181
[0x00007f4435214940] create_block_contents() /srv/www/moodle-latest/lib/blocklib.php:1239
[0x00007f4435214810] ensure_content_created() /srv/www/moodle-latest/lib/blocklib.php:374
[0x00007f4435214770] region_has_content() /srv/www/moodle-latest/lib/outputrenderers.php:3966
[0x00007f44352146a0] blocks() /srv/www/moodle-latest/theme/boost/layout/columns2.php:40
[0x00007f4435214550] [INCLUDE_OR_EVAL]() /srv/www/moodle-latest/lib/outputrenderers.php:1374
[0x00007f4435214460] render_page_layout() /srv/www/moodle-latest/lib/outputrenderers.php:1304
[0x00007f44352142f0] header() /srv/www/moodle-latest/lib/setuplib.php:1945
[0x00007f4435214220] __call() /srv/www/moodle-latest/my/index.php:164
Sql queries are in attached file. We found out there are number of SELECTS where wildcard * is used which in turn returns "heavy result set" to php-fpm clients. Those queries are made just for one request and all those result sets are returned to the php client. When there are several thousand concurrent users this can be a problem.
In first column in csv is how many seconds does client need to "parse results" and in second column is query. I do not think wildcard * is needed for all those queries. Can this be optimized?
---
Also we found out that there is poor performance in following section:
lib/outputrenderers.php:1304 -> $rendered = $this->render_page_layout($layoutfile);
lib/outputrenderers.php:195 -> $renderedtemplate = trim($template->render($context));
course/index.php:68 -> echo $OUTPUT->header();
Can this be also somehow optimized?
---
We are using
$version = 2020061502.05;
$release = '3.9.2+ (Build: 20201006)';
$branch = '39';
Kind regards,
Rok