Answers.Body: <p>For the specific case of upgrading to 4.7, here's a useful tip from <a href="https://civicrm.stackexchange.com/users/13/davejenx">Dave Jenkins</a> : <a href="https://issues.civicrm.org/jira/browse/CRM-18526#comment-106825" rel="nofollow noreferrer">https://issues.civicrm.org/jira/browse/CRM-18526#comment-106825</a></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <ol>
  <li><p>Take your site off-line and do a full database backup.</p></li>
  <li><p>Dump the two tables: <code>civicrm_mailing_recipients civicrm_mailing_event_queue</code> data-only into a file.
  You can do that for example with drush, using a variant of this:</p></li>
  </ol>
  
  <pre><code>drush sql-dump --result-file=savebigtables.sql --data-only --tables-list=civicrm_mailing_recipients,civicrm_mailing_event_queue --database=civicrm</code></pre>
  
  <p>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.</p>
  
  <ol start="3">
  <li><p>Truncate those two tables after setting foreign key checks to 0.
  From a mysql command line, you'd use
  <code><pre>
  SET FOREIGN_KEY_CHECKS=0;
  TRUNCATE civicrm_mailing_recipients;
  TRUNCATE civicrm_mailing_event_queue;
  SET FOREIGN_KEY_CHECKS=1;</pre></code></p></li>
  <li><p>Now run the upgrade normally.</p></li>
  <li><p>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.</p></li>
  <li><p>Reload the data in those two tables, e.g. with drush via</p></li>
  </ol>
  
  <pre><code>drush sqlq --file=savebigtables.sql --database=civicrm</code></pre>
  
  <p>This last step will take the longest time, but probably shorter than the upgrade would have if you didn't do this.</p>
  
  <p>Definitely try this on a test first, and make backups before you start.</p>
  
  <p>FWIW, I have recently successfully used this method on a site with about 10,000,000 rows in each of those two tables.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-18T21:38:18.990Z
Answers.LastActivityDate: 2017-10-20T13:51:24.783Z
Answers.LastEditDate: 2017-10-20T13:51:24.783Z
Answers.UserDisplayName: Alan Dixon
Answers.Body: <p>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!  <a href="https://bitbucket.org/emphanos/ram_mysql" rel="nofollow noreferrer">See here</a> 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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-12-28T01:35:52.303Z
Answers.LastActivityDate: 2016-12-28T01:35:52.303Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.Body: <p>The slowest part of upgrading on a large installation is usually the <code>civicrm_mailing_event_*</code> tables.  That data is also often stale, so consider deleting it if you don't need to keep historical information about past civimailouts.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-04T12:38:31.060Z
Answers.Text: details of how to do this are [here](http://civicrm.stackexchange.com/questions/16432/how-to-reduce-database-bloat-for-faster-upgrading)
Answers.UserDisplayName: ErichBSchulz
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-27T15:08:46.167Z
Answers.Text: Or see the answer below if you don't want to delete them.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-12-28T01:30:46.290Z
Answers.LastActivityDate: 2017-10-27T15:12:07.613Z
Answers.LastEditDate: 2017-10-27T15:12:07.613Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>Upgrades to 4.7 for big CiviCRM databases can take many many hours.</p>
  
  <p>What is the best way to speed them up?</p>
  
Tags: <upgrade><4.7><performance>
Title: How can I speed up a slow upgrade to 4.7?
ThreadText: # How can I speed up a slow upgrade to 4.7?
  
  (Asked by: username_0 on 2016-12-28)
  
  Upgrades to 4.7 for big CiviCRM databases can take many many hours.
  
  What is the best way to speed them up?
  
  **Tags:** upgrade, 4.7, performance
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_2 on 2016-12-28. Score: 6 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_0**: details of how to do this are [here](http://civicrm.stackexchange.com/questions/16432/how-to-reduce-database-bloat-for-faster-upgrading)  
  > **username_1**: Or see the answer below if you don't want to delete them.  
  
  ---
  
  ### Answer by username_1 on 2017-10-18. Score: 4
  
  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
  
  SET FOREIGN_KEY_CHECKS=0;
  TRUNCATE civicrm_mailing_recipients;
  TRUNCATE civicrm_mailing_event_queue;
  SET FOREIGN_KEY_CHECKS=1;
  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.
  
  
  ---
  
  ### Answer by username_2 on 2016-12-28. Score: 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](https://bitbucket.org/emphanos/ram_mysql) 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.
  
  
  ---
  
  