How can I speed up a slow upgrade to 4.7?

Question

Upgrades to 4.7 for big CiviCRM databases can take many many hours.

What is the best way to speed them up?

Answer 1

The slowest part of upgrading on a large installation is usually the civicrm_mailing_event_* tables. That data is also often stale, so consider deleting it if you don't need to keep historical information about past civimailouts.

Answer 2

For the specific case of upgrading to 4.7, here's a useful tip from Dave Jenkins : https://issues.civicrm.org/jira/browse/CRM-18526#comment-106825

In short: upgrading to 4.7 involves multiple changes (4 or 5?) to foreign keys on two tables that are often very large. In order to do this, mysql makes copies of those tables for each change, which can take a very long time. The strategy below shortens that process by only making the copy one time.

And here's a translation into a step by step. Warning, this will still take a while, but should be about 4x faster than the default method if you have a lot of data in your mailing tables.

1. Take your site off-line and do a full database backup.

2. Dump the two tables: civicrm_mailing_recipients civicrm_mailing_event_queue data-only into a file. You can do that for example with drush, using a variant of this:

drush sql-dump --result-file=savebigtables.sql --data-only --tables-list=civicrm_mailing_recipients,civicrm_mailing_event_queue --database=civicrm

The last argument would only be necessary if you've got civi in a separate database. The key is to dump without the table create commands, you only want the data.

3. Truncate those two tables after setting foreign key checks to 0. From a mysql command line, you'd use

   SET FOREIGN_KEY_CHECKS=0;
   TRUNCATE civicrm_mailing_recipients;
   TRUNCATE civicrm_mailing_event_queue;
   SET FOREIGN_KEY_CHECKS=1;

4. Now run the upgrade normally.

5. Optional: check the civicrm status page. If you're going to do any modifications to the indices or modify the datetime fields to timestamps, then it's a good idea to do that now.

6. Reload the data in those two tables, e.g. with drush via

drush sqlq --file=savebigtables.sql --database=civicrm

This last step will take the longest time, but probably shorter than the upgrade would have if you didn't do this.

Definitely try this on a test first, and make backups before you start.

FWIW, I have recently successfully used this method on a site with about 10,000,000 rows in each of those two tables.

Answer 3

Consider moving your MySQL folder to a RAM disk. This is NOT recommended for a production upgrade, but could speed a dev/test site upgrade! See here for a script. Folks often report a 50x speedup - but I suspect that's vs. a spinning disk. I get roughly 2.5x speedup compared to SSD.