How might I go about purging huge tables/ can I delete quiz attempts PAST a certain date only?

How might I go about purging huge tables/ can I delete quiz attempts PAST a certain date only?

by Billy Zwiener -
Number of replies: 3

My hosting company recommends I purge or clear tables that are massive... They say that performance issues can occur when tables exceed 1 million rows... They gave me a list of these tables for me to go over that are problematic which is helpful but I don't know how to begin to purge or clear without destroying data I need. I KNOW there is old useless data in some of these tables, could even stem back years of quiz attempts... but how do I keep lets say the past 6 months of quiz attempts and purge or clear older than 6 months??? Thanks for any advice here.


| DB | TableName | Rows |
+-----------------------+--------------------------------+---------+
| coldlady_sprucemoodle | mdl_question_attempt_step_data | 5368330 |
| coldlady_dev | mdl_question_attempt_step_data | 4832450 |
| coldlady_sprucemoodle | mdl_question_attempt_steps | 4129807 |
| coldlady_dev | mdl_question_attempt_steps | 3694527 |
| coldlady_sprucemoodle | mdl_question_attempts | 1445599 |
| coldlady_dev | mdl_question_attempts | 1213101 |
| coldlady_sprucemoodle | mdl_logstore_standard_log | 204139 |
| coldlady_sprucemoodle | mdl_grade_grades_history | 143741 |
| coldlady_dev | mdl_grade_grades_history | 121975 |
| coldlady_dev | mdl_logstore_standard_log | 110316 |
+-----------------------+--------------------------------+---------+



+-----------------------+--------------------------------+---------+-------------+
| DB | TableName | Rows | SizeInBytes |
+-----------------------+--------------------------------+---------+-------------+
| coldlady_sprucemoodle | mdl_question_attempts | 1416783 | 721420288 |
| coldlady_dev | mdl_question_attempts | 1246798 | 554696704 |
| coldlady_sprucemoodle | mdl_question_attempt_steps | 4088727 | 317587456 |
| coldlady_sprucemoodle | mdl_question_attempt_step_data | 5301588 | 292405248 |
| coldlady_dev | mdl_question_attempt_steps | 3692386 | 281903104 |
| coldlady_dev | mdl_question_attempt_step_data | 4793823 | 267206656 |
| coldlady_sprucemoodle | mdl_message | 7475 | 47284224 |
| coldlady_sprucemoodle | mdl_logstore_standard_log | 219551 | 45694976 |
| coldlady_sprucemoodle | mdl_grade_grades_history | 146691 | 26804224 |
| coldlady_dev | mdl_logstore_standard_log | 95298 | 23658496 |
+-----------------------+--------------------------------+---------+-------------+

Average of ratings: -
In reply to Billy Zwiener

Re: How might I go about purging huge tables/ can I delete quiz attempts PAST a certain date only?

by Ken Task -
Picture of Particularly helpful Moodlers

Am almost cretain others might tell you, don't mess with the DB ... unless you know what you are doing .... and I'm sayin' that now also .... so ....

1. always get a backup (sql dump) of the DB *before* working directly with the DB.

2. ask provider to see if there is a column in said tables for something that references date.   Those will be fields that have Unix epoch times as numbers.   If there is ask provider to give you info regarding the earliest to present (which quickly changes - ever day).  OR learn how to do that with whatever tool they provide.

3. only after you have done #1 and after you learn about epoch times and how to use whatever tool your provider might have for you to work directly with table in the DB, then execute a query that would remove rows from table (one table at a time) that are oh, let's say 'less than' a certain epoch time stamp.

You'd be wise to put your site into the maintenance mode while you are doing such work.

You'd also be wise to know how to restore the backup of the database you made before beginning in case things don't turn out the way you thought they should.

Uhhh ... you've a response to the PM you sent me.   Please respond to that.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: How might I go about purging huge tables/ can I delete quiz attempts PAST a certain date only?

by Billy Zwiener -

thank you, Ken!

Average of ratings: -
In reply to Billy Zwiener

Re: How might I go about purging huge tables/ can I delete quiz attempts PAST a certain date only?

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Storing lots of data is exactly what databases are designed for. Saying that you should not store more than 1000000 rows in a table is a bit like saying you should not drive your car further than the local shops.

Anyway, if you want to clean up the quiz-realated data, then you need to understand the foreign-key relationships implied by this example query: https://docs.moodle.org/dev/Overview_of_the_Moodle_question_engine#Detailed_data_about_an_attempt

Average of ratings: -