How to reduce database bloat for faster upgrading?

Question

In order to speed up slow upgrades, experienced folk (1 2) recommend reducing the size of the database to facilitate testing.

What is the best way to declutter the database?

Answer 1

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 feature should then reduce the size of the database and the amount of work it does for each change.