Upgrades to 4.7 for big CiviCRM databases can take many many hours.
What is the best way to speed them up?
CiviCRM Stack Exchange is a question and answer site for administrators and users of the CiviCRM Constituent Relationship Management software. It only takes a minute to sign up.Sign up to join this community
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.
Take your site off-line and do a full database backup.
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.
Truncate those two tables after setting foreign key checks to 0.
From a mysql command line, you'd use
Now run the upgrade normally.
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.
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.
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.