Answers.Body: <p>Best to review the tables which are largest in your DB, and also identify which queries during the upgrade take the longest. This is a chicken-and-egg problem, which means you might review the tables you "lighten" as you work through the upgrade process.</p>
  
  <p>Here's something I was using a few years ago on fairly heavy DB. This would exclude data in those tables from being tested on upgrade, but that was an acceptable tradeoff to test the core upgrade.</p>
  
  <p><strong>WARNING - this SQL will destroy data in the DB you execute it against.</strong></p>
  
  <pre><code>-- Trim a CiviCRM DB by stripping out the enormous amount of data
  -- stored in some tables, especially the ones relating to mailing.
  SET FOREIGN_KEY_CHECKS=0;
  TRUNCATE TABLE civicrm_cache;
  TRUNCATE TABLE civicrm_log;
  TRUNCATE TABLE civicrm_mailing_event_bounce;
  TRUNCATE TABLE civicrm_mailing_event_delivered;
  TRUNCATE TABLE civicrm_mailing_event_forward;
  TRUNCATE TABLE civicrm_mailing_event_opened;
  TRUNCATE TABLE civicrm_mailing_event_reply;
  TRUNCATE TABLE civicrm_mailing_event_trackable_url_open;
  TRUNCATE TABLE civicrm_mailing_event_unsubscribe;
  TRUNCATE TABLE civicrm_mailing_event_queue;
  TRUNCATE TABLE civicrm_mailing_recipients;
  TRUNCATE TABLE civicrm_mailing_spool;
  TRUNCATE TABLE civicrm_subscription_history;
  SET FOREIGN_KEY_CHECKS=1;
  </code></pre>
  
  <p>A couple of automated scripts would use the above to reduce a copy of the live DB to a more workable upgrade dataset, then test the upgrade against this reduced DB.</p>
  
  <p>Another set of DB tables you might remove is those prefixed <code>log_*</code>. The ARCHIVE table format they use by default has on-the-fly compression, and every DB update will trigger a duplicate query against log. This means you run double the number of DB updates, one of which will also require some compression. <a href="https://civicrm.stackexchange.com/questions/7504/how-to-migrate-database-without-logs">Disabling CiviCRM's trigger logging</a> feature should then reduce the size of the database and the amount of work it does for each change.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-12-28T23:03:42.640Z
Answers.LastActivityDate: 2017-01-04T23:48:02.110Z
Answers.LastEditDate: 2017-04-13T12:53:13.167Z
Answers.UserDisplayName: Chris Burgess
Body: <p>In order to speed up slow upgrades, experienced folk (<a href="https://civicrm.stackexchange.com/questions/16425/how-can-i-monitor-the-progress-of-a-slow-upgrade">1</a>
  <a href="https://civicrm.stackexchange.com/questions/16426/speeding-up-a-slow-upgrade">2</a>) recommend reducing the size of the database to facilitate testing.</p>
  
  <p>What is the best way to declutter the database?</p>
  
Tags: <database><upgrade><performance>
Title: How to reduce database bloat for faster upgrading?
ThreadText: # How to reduce database bloat for faster upgrading?
  
  (Asked by: username_0 on 2016-12-28)
  
  In order to speed up slow upgrades, experienced folk ([1](https://civicrm.stackexchange.com/questions/16425/how-can-i-monitor-the-progress-of-a-slow-upgrade)
  [2](https://civicrm.stackexchange.com/questions/16426/speeding-up-a-slow-upgrade)) recommend reducing the size of the database to facilitate testing.
  
  What is the best way to declutter the database?
  
  **Tags:** database, upgrade, performance
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2016-12-28. Score: 6 | Accepted answer
  
  Best to review the tables which are largest in your DB, and also identify which queries during the upgrade take the longest. This is a chicken-and-egg problem, which means you might review the tables you "lighten" as you work through the upgrade process.
  
  Here's something I was using a few years ago on fairly heavy DB. This would exclude data in those tables from being tested on upgrade, but that was an acceptable tradeoff to test the core upgrade.
  
  **WARNING - this SQL will destroy data in the DB you execute it against.**
  
  ```
  -- Trim a CiviCRM DB by stripping out the enormous amount of data
  -- stored in some tables, especially the ones relating to mailing.
  SET FOREIGN_KEY_CHECKS=0;
  TRUNCATE TABLE civicrm_cache;
  TRUNCATE TABLE civicrm_log;
  TRUNCATE TABLE civicrm_mailing_event_bounce;
  TRUNCATE TABLE civicrm_mailing_event_delivered;
  TRUNCATE TABLE civicrm_mailing_event_forward;
  TRUNCATE TABLE civicrm_mailing_event_opened;
  TRUNCATE TABLE civicrm_mailing_event_reply;
  TRUNCATE TABLE civicrm_mailing_event_trackable_url_open;
  TRUNCATE TABLE civicrm_mailing_event_unsubscribe;
  TRUNCATE TABLE civicrm_mailing_event_queue;
  TRUNCATE TABLE civicrm_mailing_recipients;
  TRUNCATE TABLE civicrm_mailing_spool;
  TRUNCATE TABLE civicrm_subscription_history;
  SET FOREIGN_KEY_CHECKS=1;
  
  ```
  
  A couple of automated scripts would use the above to reduce a copy of the live DB to a more workable upgrade dataset, then test the upgrade against this reduced DB.
  
  Another set of DB tables you might remove is those prefixed `log_*`. The ARCHIVE table format they use by default has on-the-fly compression, and every DB update will trigger a duplicate query against log. This means you run double the number of DB updates, one of which will also require some compression. [Disabling CiviCRM's trigger logging](https://civicrm.stackexchange.com/questions/7504/how-to-migrate-database-without-logs) feature should then reduce the size of the database and the amount of work it does for each change.
  
  
  ---
  
  