Answers.Body: <p>One small gotcha with large deletions: MySQL may not free-up the space in the way you expect.</p>
  
  <p>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 <code>DELETE</code> 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.</p>
  
  <p>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 <em>have</em> to do this first-thing; you might try a simple <code>DELETE</code>; assess the change in disk-usage; and then decide.)</p>
  
  <p>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 (<code>outflow =~ inflow</code>), then the size of the active system can hover around an equilibrium.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-28T23:41:54.563Z
Answers.LastActivityDate: 2019-07-28T23:41:54.563Z
Answers.UserDisplayName: Tim Otten
Answers.Body: <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-30T08:32:41.187Z
Answers.Text: Is that something you could share, or give a more detailed outline?
Answers.UserDisplayName: Aidan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-28T23:08:37.783Z
Answers.LastActivityDate: 2019-07-28T23:08:37.783Z
Answers.UserDisplayName: Seamus Lee
Answers.Body: <p>I've had an excellent experience using this extension: <a href="https://github.com/jitendrapurohit/nz.co.fuzion.deleteoldbulkmailings" rel="nofollow noreferrer">https://github.com/jitendrapurohit/nz.co.fuzion.deleteoldbulkmailings</a></p>
  
  <p>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:</p>
  
  <p><a href="https://github.com/adixon/nz.co.fuzion.deleteoldbulkmailings" rel="nofollow noreferrer">https://github.com/adixon/nz.co.fuzion.deleteoldbulkmailings</a></p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-30T19:40:07.237Z
Answers.LastActivityDate: 2019-07-30T19:40:07.237Z
Answers.UserDisplayName: Alan Dixon
Body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Top database tables:</p>
  
  <pre><code>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
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-07-28T19:11:51.180Z
Comments.Text: Relevant: https://civicrm.stackexchange.com/questions/28483/best-way-to-cleanup-civimail-database-tables (similar question) and I'd be interested to compare the results of DB table size queries (see https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database) versus the .ibd file sizes cited here. (Likely not a big difference but those files aren't always the whole picture.)
Comments.UserDisplayName: Chris Burgess
Tags: <email><database>
Title: Recommendations or Experiences on Archiving Mailing Data
ThreadText: # Recommendations or Experiences on Archiving Mailing Data
  
  (Asked by: username_0 on 2019-07-28)
  
  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
  
  ```
  
  **Tags:** email, database
  
  > **Comments:**
  > **username_1**: Relevant: https://civicrm.stackexchange.com/questions/28483/best-way-to-cleanup-civimail-database-tables (similar question) and I'd be interested to compare the results of DB table size queries (see https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database) versus the .ibd file sizes cited here. (Likely not a big difference but those files aren't always the whole picture.)  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_2 on 2019-07-28. Score: 4
  
  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 username_3's comment, wherein data is incrementally migrated out (`outflow =~ inflow`), then the size of the active system can hover around an equilibrium.
  
  
  ---
  
  ### Answer by username_3 on 2019-07-28. Score: 4
  
  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.
  
  > **Comments:**
  > **username_4**: Is that something you could share, or give a more detailed outline?  
  
  ---
  
  ### Answer by username_5 on 2019-07-30. Score: 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.
  
  
  ---
  
  