search

vadimonus / moodle-local_deleteoldquizattempts

GNU General Public License v3.0
3 stars 3 forks source link

Delete quiz attempts in chunks of 1000 #5

Open 1katoda opened 9 months ago

1katoda commented 9 months ago

We have a pretty large database, where a need arose to delete quiz attempts that are at least a year old. When we ran the delete_attempts function, there was no output, the script just stopped after 2 seconds. Manually querying the database revealed that the script had about 750000 attempts to delete, which seems to have caused it to crash.

Modifying the loop to only hold 1000 attempts in memory at a time fixes that.

vadimonus commented 9 months ago

@1katoda , thank's for your contribution. Currently code is using recordsets, recommended way of working with huge datasets. Moodle documentation do not say, that chunks are needed when working with recordset. So firstly i need some time to reproduce your problem, to be sure chunks are right solution. Please provide information about your environment: OS, php version, moodle version, database type and version. If you can find some php logs or OS logs (like oomkiller) that are relevant to situation, they would be very useful

1katoda commented 9 months ago

Understandable, testing is needed.

The staging server where the bug was encountered is an 8-core VM with 16G of RAM and PHP 7.4. Database is a local MariaDB 10.5 instance and Moodle release 4.1.7+ (Build: 20231215).

The specific course from which attempts were attempted to be deleted had ~900.000 attempts with ~760.000 being older that one year.

Unfortunately I couldn't find any relevant logs in syslog for the issue and CLI PHP doesn't get logged. The script appeared to have run succesfully, as if there weren't any attempts to delete. It just exited and produced no output (with --verbose flag) after about 2 seconds. I then modified the plugin to turn on sql debugging before deleting attempts, which showed me the last query to run (recordset). I ran the query manually in the mysql client, where the query itself was finished in about 2s, but the client was still returning the data of those 750.000 records 20s in. So I modified the loop to delete in chunks, which solved the issue.