Moodle Mysql database CPU usage at 200%

Originally, the issue raised was that an ad-hoc query was taking a long time to run. But checking the server, found that mysqld was using 200% CPU.

Checking why the database usage is so high, I tried to check which queries are taking more time on the db - 

mysql - mysqld performance high cpu and memory usage - Stack Overflow

sql - How do I enable the MySQL slow query log? - Stack Overflow

Found that some ad hoc tasks are the culprit.

# Time: 2024-07-08T08:07:05.163875Z

# User@Host: db_admin[db_admin] @  [::1]  Id:  8699

# Query_time: 7.503725  Lock_time: 0.000050 Rows_sent: 3  Rows_examined: 384225

SET timestamp=1720426017;

SELECT classname FROM task_adhoc WHERE nextruntime < '1720426017' GROUP BY classname;

# Time: 2024-07-08T08:07:05.193117Z

# User@Host: db_admin[db_admin] @  [::1]  Id:  8551

# Query_time: 5.819065  Lock_time: 0.000049 Rows_sent: 3  Rows_examined: 384225

SET timestamp=1720426019;

SELECT classname FROM task_adhoc WHERE nextruntime < '1720426019' GROUP BY classname;

 

Checking under tasks,

https://ourserver.org/admin/tool/task/runningtasks.php

I see that an ad-hoc task of course_delete_modules has been running for 5+ hours

This is what is causing the db to become unresponsive.

From this page,

https://ourserver.org/admin/category.php?category=taskconfig

I see that the task is not supposed to take so long. Max lifetime is supposed to be 30 minutes.

 

Rebooting didn't help.

I had to go to the database and delete the relevant row from the adhoc_tasks table in the database.

So that task is now gone from the "Tasks running now" screen. 

But other tasks are running, which is normal. What is not normal is the extremely high CPU usage of the mysql database on the database server.  

The CPU 200% issues seems to be related to adhoc tasks being run by mod_forum

The task_adhoc table for this instance is filled with entries created by

\mod_forum\task\send_user_notifications

tasks - the other moodle instances don't have these.

 

From

https://moodle.org/mod/forum/discuss.php?d=440932

checked

mdl_forum_queue table.

Found 45 rows, starting July 2, just like the adhoc

timemodified 1719909620

Could not find the relevant forum posts, so blindly deleted. 

Currently the cpu usage of mysql database has come down to < 1% - for which the following were done:

• deleted the mod_forum notification ad hoc tasks from task_adhoc table - around 385,000 rows! dating from July 2
• deleted the vv_forum_queue notification queue - around 45 rows, dating from July 2.

I'm leaving this for now, since I'm not sure what exactly we can do to prevent this sort of behaviour in future.