Recommendations or Experiences on Archiving Mailing Data

Question

We have an instance that has been using CiviMail extensively for years. The civicrm_mailing and civicrm_activiy tables far exceed the number of records of any other tables.

It seems likely that these tables could have some ancient history removed from them and make some operations work more smoothly, faster, or less error prone. e.g. back-ups or operations in civicrm involving groups.

Are there any recommendations, best-practices, or gotcha's for archiving/deleting historic data in CiviCRM. Specifically for mailings and activities, but while we are at it: contact or other data as well?

Top database tables:

2.6G    ./prod_civicrm/civicrm_mailing_recipients.ibd
2.3G    ./prod_civicrm/civicrm_mailing_event_queue.ibd
1.6G    ./prod_civicrm/civicrm_activity_contact.ibd
1001M   ./prod_civicrm/civicrm_log.ibd
641M    ./prod_civicrm/civicrm_mailing_event_delivered.ibd

Answer 1

we have a custom overnight routine that copies data from civicrm_mailing_x and civicrm_mailing_event_x tables to duplicate tables with a archive namespace and then deletes the appropriate rows from the main tables. The biggest things is making sure that you adequately understand the relationships between the tables so you don't accidentally delete data that hasn't yet been archived due to a Foreign Key constraint. The other thing to note is that doing it this way means you loose stats from the reports etc for the mailings that have been archived.

Answer 2

One small gotcha with large deletions: MySQL may not free-up the space in the way you expect.

The exact mechanics and numbers probably depend on the variant of MySQL and its configuration options, but just for a general sense: if the goal is to reduce the disk-usage of those tables on the active system by 75% (8gb to 2gb), then you might try to DELETE the oldest 75% of records. That may not reduce actual disk-usage by 75% -- because MySQL has its own system for freeing/reusing/reallocating space.

If you really need to reduce usage in a major way, then you might have to setup new tables or a new DBMS instance; then copy over the desired subset of recent information and drop the old. (Of course, you don't have to do this first-thing; you might try a simple DELETE; assess the change in disk-usage; and then decide.)

This gotcha is only a consideration when performing one epic shrink. If you follow a practice like Seamus's comment, wherein data is incrementally migrated out (outflow =~ inflow), then the size of the active system can hover around an equilibrium.

Answer 3

I've had an excellent experience using this extension: https://github.com/jitendrapurohit/nz.co.fuzion.deleteoldbulkmailings

I needed a few small fixes for my purposes (include a limit parameter and removal of code that assumed extended logging is on), which you can see in my fork here:

https://github.com/adixon/nz.co.fuzion.deleteoldbulkmailings

As I recall, this was about 12 years of mailings and reduced the daily dump from maybe 12Gb to 4Gb (I left the previous 12 months of log data). Tim's notes are relevant if your goal is to reduce your disk size usage for the database, but you don't need to worry if you only goal is reducing the size of dumps for backup and reducing the potential for lengthy updates if those tables get changed.