My grades history (as well as grades) tables are really large. I cannot control the grades but the grades history is not important to me. The table size has been stubbornly high at > 6GB even after disabling site admin->clean->disable grades history. Therefore, can I run the MYSQL command:
TRUNCATE TABLE mdl_grade_grades_history;
without encountering any issues with running of Moodle? I searched for this high and low but could not find any answer anywehere.
Thanks in advance for any help.
The database for an old/used site can develop tables that need optimizing. Even if the cleanups are keeping the rows in the tables for the set number of days, etc., cleanups won't reduce the size the table.
If you can, install mysqltuner and run it. Among the checks tuner performs is alerting you of tables that need optimizing.
Assuming MySQL:
https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
'SoS', Ken
Here's what mine looks like... notices the index data size. Grade history lifetime is set to 365 days and I have around 40,000 active users.
And..... here is the code for the cleanup task....
if (!empty($CFG->gradehistorylifetime)) {
$now = time();
$histlifetime = $now - ($CFG->gradehistorylifetime * DAYSECS);
$tables = [
'grade_outcomes_history',
'grade_categories_history',
'grade_items_history',
'grade_grades_history',
'scale_history'
];
foreach ($tables as $table) {
if ($DB->delete_records_select($table, "timemodified < ?", [$histlifetime])) {
mtrace(" Deleted old grade history records from '$table'");
}
}In a nutshell, if you must I can't see any issue TRUNCATEing the contents of those 5 tables.
Wonder if you wouldn't mind sharing info on server setup?
All in one? DB server dedicated? Memory?
In tuner output, Performance Metrics
what does it say for:
Maximum reached memory usage
Maximum possible memory usage
Overall possible memory usage with other process is compatible with memory available
And it appears there really isn't a problem with size of tables, but, rather, the server's adhoc_task jobs which have been building up and failing related to assignments.
Am in agreement with Howard ... 500 of anything is too much ... I, personally, would hate to be in a course, that had 500 assignments!!! :|
Moodle admin user doesn't get any advanced warning about course designs ... there is no plugin/addon/report that one could set for threshold warnings. Even if there were, a Moodle Admin might have a hard time convincing teachers of such courses how to design them in such a way as to not overtax a server. Teachers, however, do need to realize Moodle is a shared resource.
'SoS', Ken
Thanks for info ... at top you say you have 32M (which is megs), yet in tuner, those checks on memory, I see 31.4G (which is Gigs).
Devils are always in details! :|
There is one recommendation tuner made that you should correct .... in config.php the DB host is 'localhost' ... correct? If so that is actually using socket connection and not using 'network' thus one doesn't need MySQL to perform DNS lookups on every ... that's every ... query.
The the recommendation: skip-name-resolve=1
Also ... each time we change config of MySQL and restart the service, it will take some time for the DB server to collect stats ... so one needs to wait a day ... then run tuner again.
Am assuming that these classes are not being conducted face-2-face, where teacher and students are present in class using moodle at the same time, but async. ... where students can access at any time of the day/evening or even late at night.
Is there a class schedule?
Are the assignments configured to be opened at a certain time and then also close at a certain time?
Also, there are assignment types ... are the assignments such that students have to upload a file to the moodle?
Also, what about document conversion?
At any given time, how many students/teachers are logged on?
Here's a command line script I have called 'last5' and placed in code/admin/cli/:
mysql -u root -p'yourpassword' -e "use moodle;SELECT firstname,lastname,lastip,from_unixtime(lastaccess) as LastClick,now()-interval 5 minute as Last5Minutes FROM mdl_user having LastClick > Last5Minutes order by lastaccess desc;"> last5.txt;cat last5.txt;wc -l last5.txt
-rwx------. 1 apache apache 272 Jul 23 2020 last5
It's not really accurate cause it depends upon users logging out ... and many don't! (grrrr) ... but I use it to see how disruptive I would be if I were to update/upgrade or perform a backup.
Got any errors in your apache error logs?
This is a balancing act between MySQL/Apache and whatever else is running on server.
'SoS', Ken
Ok, in re-reading ...
this:
Scheduled task failed: Background processing for assignment module (mod_assign\task\cron_task),Invalid course module ID
is really the problem.
From command line in /admin/tool/task/cli/
run
php schedule_task.php --execute="\core_files\task\conversion_cleanup_task"
I have a bash shell script at that location just for cleanup ... called 'cleanup' and made executable for root users only.
Looks like:
php schedule_task.php --execute="\logstore_legacy\task\cleanup_task"
php schedule_task.php --execute="\logstore_standard\task\cleanup_task"
php schedule_task.php --execute="\core\task\file_trash_cleanup_task"
php schedule_task.php --execute="\core\task\messaging_cleanup_task"
php schedule_task.php --execute="\core\task\password_reset_cleanup_task"
php schedule_task.php --execute="\core\task\session_cleanup_task"
php schedule_task.php --execute="\core_files\task\conversion_cleanup_task"
php schedule_task.php --execute="\tool_recyclebin\task\cleanup_category_bin"
php schedule_task.php --execute="\tool_recyclebin\task\cleanup_course_bin"
'SoS', Ken
"This is the same as reported by several people on the forums."
References/urls please.
"turn on debugging there is a lot of garbage regarding deprecated calls"
Can you share an example of those?
"am on 3.11++"
No such version ... highest for 3.11 is:
3.11.3+ (Build: 20210917)
Again ... 'devil' is in the details.
"same error for the mod_assign"
| Tables_in_moodle311 (%assign%) |
+----------------------------------+
| mdl_assign |
| mdl_assign_grades |
| mdl_assign_overrides |
| mdl_assign_plugin_config |
| mdl_assign_submission |
| mdl_assign_user_flags |
| mdl_assign_user_mapping
What do you get with this query:
select id, course, name from mdl_assign;
'SoS', Ken