Hello!
We are expecting big troubles with Moodle (latest version 3.8.2+) on our server.
MySQL periodically takes 100% of CPU.
We have 16 CPUs, so it is not normal, I think.
Below are the processes of MariaDB
MariaDB [(none)]> show processlist;
+--------+-------------+-----------+--------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+-------------+-----------+--------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 205213 | moodle | localhost | moodle | Sleep | 1398 | | NULL | 0.000 |
| 205381 | moodle | localhost | moodle | Sleep | 1396 | | NULL | 0.000 |
| 205522 | moodle | localhost | moodle | Sleep | 46 | | NULL | 0.000 |
| 205905 | moodle | localhost | moodle | Sleep | 1389 | | NULL | 0.000 |
| 208406 | moodle | localhost | moodle | Sleep | 1312 | | NULL | 0.000 |
| 209418 | moodle | localhost | moodle | Sleep | 1275 | | NULL | 0.000 |
| 210930 | moodle | localhost | moodle | Sleep | 49 | | NULL | 0.000 |
| 211299 | moodle | localhost | moodle | Sleep | 1230 | | NULL | 0.000 |
| 212239 | moodle | localhost | moodle | Sleep | 1171 | | NULL | 0.000 |
| 212457 | moodle | localhost | moodle | Sleep | 1160 | | NULL | 0.000 |
| 212488 | moodle | localhost | moodle | Sleep | 1159 | | NULL | 0.000 |
| 212639 | moodle | localhost | moodle | Sleep | 1152 | | NULL | 0.000 |
| 212818 | moodle | localhost | moodle | Sleep | 1147 | | NULL | 0.000 |
| 213209 | moodle | localhost | moodle | Sleep | 1134 | | NULL | 0.000 |
| 215821 | root | localhost | NULL | Sleep | 904 | | NULL | 0.000 |
| 218011 | moodle | localhost | moodle | Sleep | 6 | | NULL | 0.000 |
| 221895 | moodle | localhost | moodle | Sleep | 946 | | NULL | 0.000 |
| 221914 | moodle | localhost | moodle | Sleep | 945 | | NULL | 0.000 |
| 221942 | moodle | localhost | moodle | Sleep | 945 | | NULL | 0.000 |
| 221967 | moodle | localhost | moodle | Sleep | 944 | | NULL | 0.000 |
| 221983 | moodle | localhost | moodle | Sleep | 943 | | NULL | 0.000 |
| 221997 | moodle | localhost | moodle | Sleep | 942 | | NULL | 0.000 |
| 222445 | moodle | localhost | moodle | Query | 4 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 223451 | moodle | localhost | moodle | Sleep | 904 | | NULL | 0.000 |
| 236018 | moodle | localhost | moodle | Sleep | 630 | | NULL | 0.000 |
| 236034 | moodle | localhost | moodle | Sleep | 629 | | NULL | 0.000 |
| 236075 | moodle | localhost | moodle | Sleep | 627 | | NULL | 0.000 |
| 236414 | moodle | localhost | moodle | Sleep | 616 | | NULL | 0.000 |
| 236576 | moodle | localhost | moodle | Sleep | 612 | | NULL | 0.000 |
| 236628 | moodle | localhost | moodle | Sleep | 611 | | NULL | 0.000 |
| 236665 | moodle | localhost | moodle | Sleep | 610 | | NULL | 0.000 |
| 236913 | moodle | localhost | moodle | Sleep | 605 | | NULL | 0.000 |
| 237041 | moodle | localhost | moodle | Sleep | 604 | | NULL | 0.000 |
| 237101 | moodle | localhost | moodle | Sleep | 603 | | NULL | 0.000 |
| 237142 | moodle | localhost | moodle | Sleep | 602 | | NULL | 0.000 |
| 237341 | moodle | localhost | moodle | Sleep | 597 | | NULL | 0.000 |
| 237357 | moodle | localhost | moodle | Sleep | 597 | | NULL | 0.000 |
| 237381 | moodle | localhost | moodle | Sleep | 596 | | NULL | 0.000 |
| 237455 | moodle | localhost | moodle | Sleep | 594 | | NULL | 0.000 |
| 237475 | moodle | localhost | moodle | Sleep | 593 | | NULL | 0.000 |
| 237491 | moodle | localhost | moodle | Sleep | 593 | | NULL | 0.000 |
| 237529 | moodle | localhost | moodle | Sleep | 591 | | NULL | 0.000 |
| 237540 | moodle | localhost | moodle | Sleep | 591 | | NULL | 0.000 |
| 237547 | moodle | localhost | moodle | Sleep | 591 | | NULL | 0.000 |
| 237569 | moodle | localhost | moodle | Sleep | 590 | | NULL | 0.000 |
| 237597 | moodle | localhost | moodle | Sleep | 590 | | NULL | 0.000 |
| 237646 | moodle | localhost | moodle | Sleep | 587 | | NULL | 0.000 |
| 237658 | moodle | localhost | moodle | Sleep | 587 | | NULL | 0.000 |
| 240615 | moodle | localhost | moodle | Sleep | 35 | | NULL | 0.000 |
| 241990 | moodle | localhost | moodle | Query | 220 | Sending data | SELECT DISTINCT
c.id AS course,
cr.id AS criteriaid,
| 0.000 |
| 242224 | moodle | localhost | moodle | Sleep | 458 | | NULL | 0.000 |
| 242316 | moodle | localhost | moodle | Sleep | 454 | | NULL | 0.000 |
| 243061 | moodle | localhost | moodle | Sleep | 432 | | NULL | 0.000 |
| 243467 | moodle | localhost | moodle | Sleep | 25 | | NULL | 0.000 |
| 244723 | moodle | localhost | moodle | Sleep | 389 | | NULL | 0.000 |
| 246033 | moodle | localhost | moodle | Sleep | 357 | | NULL | 0.000 |
| 246364 | moodle | localhost | moodle | Sleep | 350 | | NULL | 0.000 |
| 248389 | moodle | localhost | moodle | Sleep | 302 | | NULL | 0.000 |
| 250936 | moodle | localhost | moodle | Sleep | 250 | | NULL | 0.000 |
| 252239 | moodle | localhost | moodle | Sleep | 217 | | NULL | 0.000 |
| 253132 | moodle | localhost | moodle | Sleep | 192 | | NULL | 0.000 |
| 253935 | moodle | localhost | moodle | Sleep | 12 | | NULL | 0.000 |
| 254963 | moodle | localhost | moodle | Query | 3 | Sending data | SELECT COUNT(userid) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 254985 | moodle | localhost | moodle | Query | 164 | Sending data | SELECT c.id AS course, u.id AS userid, crc.id AS completionid, ue.timestart AS timeenrolled,
| 0.000 |
| 255753 | moodle | localhost | moodle | Sleep | 147 | | NULL | 0.000 |
| 255775 | moodle | localhost | moodle | Sleep | 146 | | NULL | 0.000 |
| 255826 | moodle | localhost | moodle | Sleep | 145 | | NULL | 0.000 |
| 256739 | moodle | localhost | moodle | Sleep | 129 | | NULL | 0.000 |
| 256992 | moodle | localhost | moodle | Sleep | 127 | | NULL | 0.000 |
| 257427 | moodle | localhost | moodle | Sleep | 115 | | NULL | 0.000 |
| 257615 | moodle | localhost | moodle | Sleep | 109 | | NULL | 0.000 |
| 257873 | moodle | localhost | moodle | Sleep | 104 | | NULL | 0.000 |
| 257896 | moodle | localhost | moodle | Sleep | 103 | | NULL | 0.000 |
| 258024 | moodle | localhost | moodle | Sleep | 100 | | NULL | 0.000 |
| 258160 | moodle | localhost | moodle | Query | 8 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 258317 | moodle | localhost | moodle | Sleep | 94 | | NULL | 0.000 |
| 258390 | moodle | localhost | moodle | Sleep | 92 | | NULL | 0.000 |
| 258454 | moodle | localhost | moodle | Sleep | 90 | | NULL | 0.000 |
| 258559 | moodle | localhost | moodle | Sleep | 87 | | NULL | 0.000 |
| 258571 | moodle | localhost | moodle | Query | 1 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 258584 | moodle | localhost | moodle | Sleep | 86 | | NULL | 0.000 |
| 258590 | moodle | localhost | moodle | Sleep | 86 | | NULL | 0.000 |
| 258595 | moodle | localhost | moodle | Sleep | 86 | | NULL | 0.000 |
| 258609 | moodle | localhost | moodle | Sleep | 86 | | NULL | 0.000 |
| 258619 | moodle | localhost | moodle | Sleep | 85 | | NULL | 0.000 |
| 258785 | moodle | localhost | moodle | Sleep | 81 | | NULL | 0.000 |
| 258852 | moodle | localhost | moodle | Sleep | 79 | | NULL | 0.000 |
| 258870 | moodle | localhost | moodle | Sleep | 79 | | NULL | 0.000 |
| 258893 | moodle | localhost | moodle | Sleep | 78 | | NULL | 0.000 |
| 259055 | moodle | localhost | moodle | Sleep | 71 | | NULL | 0.000 |
| 259386 | moodle | localhost | moodle | Sleep | 59 | | NULL | 0.000 |
| 259420 | moodle | localhost | moodle | Sleep | 58 | | NULL | 0.000 |
| 259541 | moodle | localhost | moodle | Query | 1 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 259725 | moodle | localhost | moodle | Sleep | 49 | | NULL | 0.000 |
| 259781 | moodle | localhost | moodle | Query | 1 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 259845 | moodle | localhost | moodle | Sleep | 44 | | NULL | 0.000 |
| 259912 | moodle | localhost | moodle | Sleep | 43 | | NULL | 0.000 |
| 259913 | moodle | localhost | moodle | Sleep | 43 | | NULL | 0.000 |
| 259915 | moodle | localhost | moodle | Query | 8 | Sending data | SELECT COUNT(userid) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 259950 | moodle | localhost | moodle | Sleep | 42 | | NULL | 0.000 |
| 260065 | moodle | localhost | moodle | Sleep | 38 | | NULL | 0.000 |
| 260092 | moodle | localhost | moodle | Sleep | 37 | | NULL | 0.000 |
| 260135 | moodle | localhost | moodle | Query | 0 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 260159 | moodle | localhost | moodle | Sleep | 35 | | NULL | 0.000 |
| 260200 | moodle | localhost | moodle | Query | 4 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 260248 | moodle | localhost | moodle | Query | 9 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 260249 | moodle | localhost | moodle | Sleep | 34 | | NULL | 0.000 |
| 260294 | moodle | localhost | moodle | Query | 4 | Sending data | SELECT COUNT(userid) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 260352 | moodle | localhost | moodle | Sleep | 33 | | NULL | 0.000 |
| 260432 | moodle | localhost | moodle | Query | 10 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 260456 | moodle | localhost | moodle | Query | 4 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 260506 | moodle | localhost | moodle | Query | 8 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 260507 | moodle | localhost | moodle | Sleep | 30 | | NULL | 0.000 |
| 260537 | moodle | localhost | moodle | Query | 8 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 260609 | moodle | localhost | moodle | Sleep | 29 | | NULL | 0.000 |
| 260748 | moodle | localhost | moodle | Sleep | 26 | | NULL | 0.000 |
| 260794 | moodle | localhost | moodle | Query | 3 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 260798 | moodle | localhost | moodle | Query | 0 | Sending data | SELECT COUNT(u.id)
FROM mdl_user u
LEFT JOIN mdl_user_pref | 0.000 |
| 260815 | moodle | localhost | moodle | Sleep | 25 | | NULL | 0.000 |
| 260908 | moodle | localhost | moodle | Query | 1 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid FROM mdl_logstore_standard_log
WHERE t | 0.000 |
| 260920 | moodle | localhost | moodle | Query | 11 | Sending data | SELECT COUNT(userid) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 261023 | moodle | localhost | moodle | Query | 9 | Sending data | SELECT COUNT(userid) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 261031 | moodle | localhost | moodle | Sleep | 21 | | NULL | 0.000 |
| 261139 | moodle | localhost | moodle | Query | 5 | Sending data | SELECT COUNT(userid) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 261140 | moodle | localhost | moodle | Query | 5 | Sending data | SELECT COUNT(userid) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 261184 | moodle | localhost | moodle | Query | 3 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 261232 | moodle | localhost | moodle | Sleep | 14 | | NULL | 0.000 |
| 261311 | moodle | localhost | moodle | Query | 0 | Sending data | SELECT COUNT(userid) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 261320 | moodle | localhost | moodle | Query | 10 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 261326 | moodle | localhost | moodle | Sleep | 10 | | NULL | 0.000 |
| 261356 | moodle | localhost | moodle | Sleep | 9 | | NULL | 0.000 |
| 261391 | moodle | localhost | moodle | Sleep | 8 | | NULL | 0.000 |
| 261397 | moodle | localhost | moodle | Sleep | 7 | | NULL | 0.000 |
| 261402 | moodle | localhost | moodle | Query | 6 | Sending data | SELECT g.id, CONCAT(i.itemmodule, '-', i.iteminstance, '-', g.userid) as exclusion
FR | 0.000 |
| 261409 | moodle | localhost | moodle | Sleep | 7 | | NULL | 0.000 |
| 261415 | moodle | localhost | moodle | Sleep | 7 | | NULL | 0.000 |
| 261435 | moodle | localhost | moodle | Query | 6 | Sending data | SELECT g.id, CONCAT(i.itemmodule, '-', i.iteminstance, '-', g.userid) as exclusion
FR | 0.000 |
| 261441 | moodle | localhost | moodle | Sleep | 5 | | NULL | 0.000 |
| 261452 | moodle | localhost | moodle | Query | 4 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 261479 | moodle | localhost | moodle | Query | 4 | Sending data | SELECT g.id, CONCAT(i.itemmodule, '-', i.iteminstance, '-', g.userid) as exclusion
FR | 0.000 |
| 261507 | moodle | localhost | moodle | Query | 3 | Sending data | SELECT g.id, CONCAT(i.itemmodule, '-', i.iteminstance, '-', g.userid) as exclusion
FR | 0.000 |
| 261516 | moodle | localhost | moodle | Query | 2 | Sending data | SELECT COUNT(DISTINCT(userid)) as countid
FROM mdl_logstore_standard_log
| 0.000 |
| 261529 | moodle | localhost | moodle | Sleep | 3 | | NULL | 0.000 |
| 261531 | moodle | localhost | moodle | Sleep | 2 | | NULL | 0.000 |
| 261542 | moodle | localhost | moodle | Query | 0 | Creating sort index | SELECT u.id,u.lastname
FROM mdl_user u
JOIN (SELECT DISTINCT userid
| 0.000 |
| 261552 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
| 261562 | moodle | localhost | moodle | Query | 1 | Sending data | SELECT u.id,u.lastname
FROM mdl_user u
JOIN (SELECT DISTINCT userid
| 0.000 |
| 261563 | moodle | localhost | moodle | Query | 0 | statistics | SELECT gi.id
FROM mdl_grade_items gi
WHERE (gi.gradetype | 0.000 |
| 261571 | moodle | localhost | moodle | Sleep | 0 | | NULL | 0.000 |
| 261572 | moodle | localhost | moodle | Sleep | 0 | | NULL | 0.000 |
| 261574 | moodle | localhost | moodle | Sleep | 0 | | NULL | 0.000 |
| 261576 | moodle | localhost | moodle | Query | 0 | Sending data | SELECT u.id,u.lastname
FROM mdl_user u
JOIN (SELECT DISTINCT userid
| 0.000 |
| 261578 | moodle | localhost | moodle | Sleep | 0 | | NULL | 0.000 |
| 261586 | moodle | localhost | moodle | Query | 0 | statistics | SELECT userid,null FROM mdl_scorm_scoes_track WHERE scormid='332' AND userid='5717' GROUP BY userid | 0.000 |
+--------+-------------+-----------+--------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
159 rows in set (0.00 sec)
I will apreciate any help.
Thanks!!