2

On CiviCRM installations that use CiviMail extensively, the mailing tables can become pretty big. For example, I'm looking at a database with a civicrm_mailing_recipients table that has 4 million entries since 2011.

Assuming that the stats from old mailings are not relevant anymore, what would be the best way to archive the data?

Ex: we are in 2019, stats from before 2016 could be archived elsewhere.

I did a bit of digging and found this old forum post which sounds like a good start. There was also this PR back in 2016 that helped avoid breakage of click-through links when mailing data was deleted.

Related SE question: Does archiving mailings do anything in particular.

2

If I'm understanding correctly, I'd say there are two possible solutions here. The first is the easier one where you don't care about keeping the stats or history of the mailings in Civi. If that's case, then you can probably use the API explorer to delete old mailings, which would be equivalent to clicking to delete them from the Scheduled and Sent Mailings management list. This doesn't really archive them per se. You're just deleting them, but doing so through the explorer seems cleaner than deleting through the MySQL cli.

The second option is what my clients want which is to keep the stats and remove the bloat, but this currently doesn't exist as far as I know. The largest tables they have are civicrm_mailing_recipients, civicrm_mailing_event_queue and civicrm_mailing_event_delivered. (The activity tables also get pretty big, but that's another issue.) I've schemed a solution that would require transferring the stats data to another table(s) to be able to reference them at a later date (not all that difficult), but hooking those new table(s) into the search functions, reports and other areas where it's needed is a taller order. I haven't really thought through this in any detail, so I'm sure there's lots I haven't considered. Maybe there's an interim solution between these two. Clicking Archive removes data from the some of the processing tables but keeps the stats tables in place?

  • I guess ideally clicking Archive would indeed compile the stats and keep them in a separate table and cleanup all of the queue data, but I agree that it would be complicated if we want search/reports to continue working. I suspect most people would not need that; I would create a job that auto-archives after a certain number of months or years, and worst case, provide an alternate report for archived mailings. – bgm Feb 14 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.