What's the best way to reduce the size of the database?

đź’¬ General Discussion
1

What’s the best way to reduce the size of the database? The db is about 1.1GB and the 5 tables below contribute significantly to the size.

  1. securitygroups_records - 498MB
  2. tracker - 276MB
  3. job_queue - 43MB
  4. emails_beans - 28MB
  5. aod_indexevent - 20MB

All 5 of the scheduled jobs below are running fine but is there any way to reduce the size of the tables, especially the top 3?

  1. Prune SuiteCRM Feed Tables
  2. Optimise AOD Index
  3. Prune Tracker Tables
  4. Clean Jobs Queue
  5. Removal of documents from filesystem

Thanks!

2

This isn’t particularly big at all in terms of database size.

I would ensure those jobs are running but as you continue to use the CRM the database will grow.

You could remove records which are deleted = 1 etc. but this shouldn’t be required.

Thanks,

Will.

3

ok - thanks Will - I guess it is what it is!

4

I had plenty of “orphaned” relationship records, and security suite records, but it was caused by my own large successive imports (done by php code, not by the “wizard”) on my development machine. As I was fixing the Import routine, I kept deleting things over and over, but traces were left behind.

Basically, what I mean is that the DB doesn’t seem to enforce “cascaded deletes”, maybe the application level handles that, but you might want to check what all those security suite records are pointing to - is it something real, something still in your DB, or is it a lost link?

5

@Will or anybody: Could somebody explain what exactly this scheduled script does:

“removal of documents from the file system” ?

Does it automatically remove files saved in the /upload folder? when no longer referenced in the database (e.g. the “notes” table?)
If yes, that would be great for pruning the system. We could just delete old database-entries and have SuiteCRM take care of deleting the files. But I’m not confident that’s what’s going to happen… Anybody have any experience with this?

6

The job seems to run function removeDocumentsFromFS inside modules/Schedulers/_AddJobsHere.php:

https://github.com/salesagility/SuiteCRM/blob/932b87108edc154dd3c9c86b57ceaa24acd40835/modules/Schedulers/_AddJobsHere.php#L427-L483

Funny enough, at first glance I can’t spot any file deleting commands in there. I would say it deletes just directories, unless I’m missing something.

1 Like
7

this cleaning function is never run because of $return = true; at the beginning of the function.

we have been using sugarcrm then suitecrm, i have more than 10GB of files in deleted folder.

why blocking this function from running ?
I just re-activated it by commenting the line with “$return = true;”
it is a mistake ?

8

$return=true; is just setting a variable that tells the calling function whether there was success or not. It doesn’t really change whether the rows are deleted…