Clearing database

Clearing database

by solmaz naderi -
Number of replies: 6

Hi every one

After using Moodle for about 5 years, I am looking for ways of reducing the size of the database. It just takes too long (about 2 hours!) to backup and restore.

I want to keep the courses and every other setting (including blocks, permissions, template, custom css etc).

I am looking to delete all students  than their last access is more than 6 months with their logs, and reduce log table .

Would you help me how can i do that? is there any plugin for?

Thanks in advance

Solmaz

Average of ratings: -
In reply to solmaz naderi

Re: Clearing database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

I'd start by looking at what the largest tables are. In my experience inactive students don't take up much space, 10000 rows in mdl_user doesn't take much storage.

The logs in mdl_logstore_standard_log are often the largest table. Setting a retention period (Keep logs for) is a good idea but note that expired log entries are removed in chunks: by default in every daily run of the scheduled task it removes entries for five minutes. So if there are lots of old entries to be removed – there may be millions – this can take many days. So when setting Keep logs for for the first time you could manually remove the old entries manually, e.g. MySQL/MariaDB or PostgreSQL.

Average of ratings: Useful (3)
In reply to Leon Stringer

Re: Clearing database

by solmaz naderi -
By clearing the records based on the specified date on the table you mentioned, my problem was solved
Thanks
Average of ratings: -
In reply to solmaz naderi

Re: Clearing database

by C Behan -
Hi Solmaz,
You can do this with Bulk User actions but you need to be very careful of course, so check things very carefully before you press the delete button!

  1. Create a cohort called 'accessed more than 6 months ago'.
  2. Create a cohort called 'never accessed'.
  3. In Bulk User Actions, set a filter to identify users who last logged in more than 6 months ago. Add these users to the cohort 'accessed more than 6 months ago'. However this also includes users who never logged in, some of which might have been created recently, and just had not had the chance to log in yet.
  4. In Bulk User Actions, clear everything including selected users. Now add a filter to identify users who never logged in and add these users to the 'never accessed' cohort.
  5. In Bulk User Actions, clear everything including selected users. Now add 2 filters: 
    1. Users who ARE members of  'accessed more than 6 months ago' cohort.
    2. Users who ARE NOT members of 'never accessed' cohort.
  • This will leave you with accounts who last logged in 6 months ago but exclude those who never logged in.
Bulk delete these users after carefully checking the list to confirm that all selected users meet your criteria for people who should be deleted.
Average of ratings: Useful (2)
In reply to C Behan

Re: Clearing database

by solmaz naderi -
I will use the important and useful points you mentioned in maintaining my database
Thanks
Average of ratings: -
In reply to solmaz naderi

Re: Clearing database

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Solmaz, how big is "big?" And how are you doing backups?

Whatever you decide to do, make sure to create a backup of your Moodle before doing anything. The suggestions by Leon and C Behan are good. However, they might "modify" data that may prevent you from doing course analytics in the future, if this is important to you.

Many years ago, I had a problem with a database that grew to be so big (1GB) that my server company said I needed to upgrade to a VPS. I did that, and it solved my database-size problem. It also improved my Moodle's overall performance, including backups.

By the way, my "backup" method is to backup the database via the MySQL command line, like this:

mysqldump -uyour_database_user -pyour_password --opt -r$your_server_location/backup_moodle/NOW-moodledb.sql your_moodle_database_name

My backup of the database is currently 2.5GB for around 50 courses, around 2000 students. My moodledata backup is around 10GB. Yep, I run a relatively small Moodle.

I do have two backup Moodle's, one for Moodle 1.9 and one for Moodle 2.9, that are not mixed into my current Moodle.  I can access data from these older Moodles via my sandbox MAMP.  As you might guess, I will probably begin a new Moodle when Moodle 4.0 comes out, and then move my current Moodle3 to my MAMP.
Average of ratings: Useful (2)