DB has grown so huge and it started to cause problems with sending mass mailings. I want to delete old mass mailings, the sent ones and the drafts, etc. Is there away to do it in one click instead of deleting each and everyone individually?
This does not exist in the UI currently. However, it does seem like a good idea to have a script that deletes OR archives mailings that are greater than N months / years old. So i'd recommend writing a api job that does the needful.
Approx steps would be:
a. Create a new delete/archive mailing job taking parameters like list of ids or mailing delivered before date XXX
b. Check and ensure that the mailing delete api deletes all the right tables. Most of the deletion is done by mysql via cascaded relationships. At a quick glance, the api delete does not seem to handle file attachments
c. Use the API from item b to implement the scheduled job
Please do share your extension so others can benefit
You can try this extension Delete Old Bulk Mailings for CiviCRM (https://github.com/jitendrapurohit/nz.co.fuzion.deleteoldbulkmailings) which provides some new API calls.
You can either use the API in a script or you can use the command line tool cv (https://github.com/civicrm/cv). The README provides some good examples on how to use it.
I don't think it is likely that old mailings would cause troubles with sending a CiviMail. They would use up disk space which would likely manifest other problems when disk space ran low or was exhausted.
It's more likely that a problem sending a mass mailing is due to growth in your list size, and that sending to more recipients is causing your server to run out of memory. Can you describe the symptoms of the problems you are having when sending?
It's still going to be a good idea to create ways to deal conveniently with continual growth of disk space by old mailings.
If it turns out the issue is RAM usage for sending CiviMails, then you can tune the throttling of sending.
Certain email integrations may also be implemented to send a CiviMail by sending a single email for delivery to a synchronised list held on an external server, which would offload from your CiviCRM server virtually all of the load of sending. But I'm not confident about this. It's not true of our Mandrill extension but could be true of the MailChimp or Constant Contact ones. I don't think it is true of the CiviSMTP service.
I like this idea. For reference, one of my bigger installs has the following sizes of the 4 biggest files in mysql (in MB). So removing the corresponding activities (or maybe only some of the associations) is probably a good idea also.
110592 civicrm_mailing_event_delivered.ibd 151552 civicrm_mailing_recipients.ibd 188420 civicrm_activity_contact.ibd 266240 civicrm_mailing_event_queue.ibd