Answers.Body: <ol>
  <li><p><strong>Never try the upgrade on live</strong> :)</p></li>
  <li><p><strong>Take small steps</strong>. Sometimes CiviCRM chokes on a minor upgrade step, but you can get the upgrader to run from v1.2.3 all the way to v4.5.6 ... in that case, you'll have a more robust process if you don't try to do it all in one leap. You can automate the upgrade to v4.5.6, then work out what's breaking at the next step. (Some examples of why this is even a thing might be helpful? Version numbers in this paragraph are made up.)</p></li>
  <li><p><strong>Automate the process so that it's repeatable</strong>, and therefore can be tested thoroughly. This means investing a little extra time in writing a bash script which performs the upgrade steps, and then using that script only to perform each test run. This approach means that any extra steps (eg adjusting some index) end up in the script, and the process is repeated as consistently as possible. Some upgrades can take hours, and you want to avoid pressuring yourself to "just make a small change" in order to save starting over.</p></li>
  <li><p><strong>Have a fast test environment</strong>. On the one hand, it needs to accurately mirror the environment you'll perform the final upgrade on. On the other hand, if you can use a system with solid state drives, or put all of MySQL's data into RAM during the upgrade, you'll save a lot of time. Tune your MySQL. (A couple dozen upgrade runs will give you plenty of data in <a href="http://mysqltuner.com/" rel="nofollow noreferrer"><code>mysqltuner</code></a> also...)</p></li>
  <li><p><strong>Observe DB activity during the upgrade</strong>. Use <a href="http://jeremy.zawodny.com/mysql/mytop/" rel="nofollow noreferrer"><code>mytop</code></a>, to shows current SQL queries as they happen. (You can do this in SQL with <code>SHOW FULL PROCESSLIST</code>, but <code>mytop</code> is convenient.) This will let you know if a query is taking a long time, and might inform your approach to help you reduce upgrade time. (<em>Maybe, it's taking eight hours to upgrade a chunk of data you no longer needed, or maybe you can speed the process by adding an index in your upgrade script, then removing it after the upgrade is done.</em>) Note also the CiviCRM debug log as mentioned in Jon's comment.</p></li>
  <li><p><strong>Observe upgrade progress</strong> also: <code>SELECT version FROM civicrm_domain ;</code> - the result will change as CiviCRM progresses through the upgrade steps.</p></li>
  <li><p><strong>Generate a reduced dataset for testing</strong>. I might bring the total contacts down to a couple thousand from the original dataset, and similarly reduce other large tables to a small sample of the original. This means the process is no longer an accurate test of the upgrade, but it does ensure you are testing schema changes, and if it can reduce each repeated upgrade run by minutes, it can save you hours down the track. If doing this, make sure you then test the full upgrade a few times before deploying - by this stage, it will run without your attention anyway, since (3).</p></li>
  <li><p><strong>Dump data for components you don't care about</strong>. Some tables in CiviCRM can grow huge, and can steal more time then they deserve. Look at the table sizes (<a href="https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database">MySQL: show largest tables</a>) and decide which are worth pruning. You might decide to drop mailings more than two years old, or an activity type you retired a while back, or ... (Jon also mentions this <a href="https://civicrm.stackexchange.com/questions/16426/speeding-up-a-slow-upgrade">here</a>.)</p></li>
  <li><p><strong>Consider turning off logging</strong>. CiviCRM's trigger logging may affect upgrade performance, and you could turn it off during the upgrade. (This needs to be an informed decision, since a large chunk of data changes "off the radar" during upgrade may not be acceptable to your org.)</p></li>
  <li><p><strong>Ask for help</strong>. You might find some assistance here or on <a href="https://chat.civicrm.org" rel="nofollow noreferrer">CiviCRM chat</a> if you're stuck. If you have a challenging upgrade to deal with, it's worth finding a good team to support your CiviCRM site!</p></li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-12-28T02:13:08.633Z
Answers.Text: Posted this in response to [conversation on chat.civicrm.org](https://chat.civicrm.org/civicrm/pl/3b317jz45pdn8fx6cmek4sjuih) today. Please add your tips! Upgrades are admittedly a challenge for CiviCRM, and it's worth hearing how things go wrong so we can improve the process.
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-12-28T02:16:07.780Z
Answers.Text: wow!! `mytop`!!! that is cool!
Answers.UserDisplayName: ErichBSchulz
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-12-28T03:26:32.497Z
Answers.Text: Apologies, this is really an answer to both this question and [What's the best way to speed up a slow upgrade?](http://civicrm.stackexchange.com/questions/16426/speeding-up-a-slow-upgrade), I stormed in here from the chat channel and didn't stop to see which question I was answering :D
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-12-28T02:12:23.037Z
Answers.LastActivityDate: 2016-12-28T03:42:16.813Z
Answers.LastEditDate: 2017-05-23T12:38:59.763Z
Answers.UserDisplayName: Chris Burgess
Answers.Body: <p>The upgrader builds a queue of version-specific upgrade tasks -- then executes them. You can measure progress based on the size of the queue.</p>
  
  <p>If you use the web-based upgrader, it displays a progress-bar based on the queue.</p>
  
  <p>If you use the CLI to upgrade the DB, then it doesn't display a progress-bar. However, it would be patch-welcome to add something. (If I were doing this, I'd write a <a href="https://github.com/civicrm/cv" rel="nofollow noreferrer"><code>cv upgrade</code></a> command which borrows heavily from <a href="http://symfony.com/doc/current/components/console/helpers/progressbar.html" rel="nofollow noreferrer">http://symfony.com/doc/current/components/console/helpers/progressbar.html</a> and <a href="https://github.com/civicrm/civicrm-core/blob/master/CRM/Upgrade/Headless.php" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/blob/master/CRM/Upgrade/Headless.php</a>).</p>
  
  <p>If you're looking for ways to poke under the hood, then you can look at SQL tables <code>civicrm_domain</code> and <code>civicrm_queue_item</code>. I wouldn't recommend building external processes on these, but they could be good for a quick peek. These (untested) queries should be interesting:</p>
  
  <ul>
  <li><code>select count(*) from civicrm_queue_item where queue_name  = 'CRM_Upgrade'</code> (the queue gets smaller as tasks progress)</li>
  <li><code>select version from civicrm_domain</code> (updates to reflect the most recent incremental update)</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-12-28T01:57:13.407Z
Answers.LastActivityDate: 2016-12-28T01:57:13.407Z
Answers.UserDisplayName: Tim Otten
Answers.Body: <p>If you are upgrading to CiviCRM 4.6.10+, <a href="https://issues.civicrm.org/jira/browse/CRM-17324" rel="nofollow noreferrer">the data is written</a> in two places besides the screen.</p>
  
  <p>One place is the <code>civicrm_log</code> database table.</p>
  
  <p>The other is the CiviCRM log file.  If you have shell access to your server, consider running <code>tail -f</code> on your CiviCRM log, <a href="https://civicrm.stackexchange.com/questions/15931/where-are-the-configandlog-and-templates-c-directories">located in the ConfigAndLog directory</a>.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-12-28T01:40:00.430Z
Answers.LastActivityDate: 2016-12-28T01:40:00.430Z
Answers.LastEditDate: 2017-04-13T12:53:13.017Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>CiviCRM upgrades can take a long time.</p>
  
  <p>What is the best way of seeing where they are up to?</p>
  
Tags: <upgrade>
Title: How can I monitor the progress of a slow upgrade?
ThreadText: # How can I monitor the progress of a slow upgrade?
  
  (Asked by: username_0 on 2016-12-28)
  
  CiviCRM upgrades can take a long time.
  
  What is the best way of seeing where they are up to?
  
  **Tags:** upgrade
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_1 on 2016-12-28. Score: 5
  
  1. **Never try the upgrade on live** :)
  2. **Take small steps**. Sometimes CiviCRM chokes on a minor upgrade step, but you can get the upgrader to run from v1.2.3 all the way to v4.5.6 ... in that case, you'll have a more robust process if you don't try to do it all in one leap. You can automate the upgrade to v4.5.6, then work out what's breaking at the next step. (Some examples of why this is even a thing might be helpful? Version numbers in this paragraph are made up.)
  3. **Automate the process so that it's repeatable**, and therefore can be tested thoroughly. This means investing a little extra time in writing a bash script which performs the upgrade steps, and then using that script only to perform each test run. This approach means that any extra steps (eg adjusting some index) end up in the script, and the process is repeated as consistently as possible. Some upgrades can take hours, and you want to avoid pressuring yourself to "just make a small change" in order to save starting over.
  4. **Have a fast test environment**. On the one hand, it needs to accurately mirror the environment you'll perform the final upgrade on. On the other hand, if you can use a system with solid state drives, or put all of MySQL's data into RAM during the upgrade, you'll save a lot of time. Tune your MySQL. (A couple dozen upgrade runs will give you plenty of data in [`mysqltuner`](http://mysqltuner.com/) also...)
  5. **Observe DB activity during the upgrade**. Use [`mytop`](http://jeremy.zawodny.com/mysql/mytop/), to shows current SQL queries as they happen. (You can do this in SQL with `SHOW FULL PROCESSLIST`, but `mytop` is convenient.) This will let you know if a query is taking a long time, and might inform your approach to help you reduce upgrade time. (*Maybe, it's taking eight hours to upgrade a chunk of data you no longer needed, or maybe you can speed the process by adding an index in your upgrade script, then removing it after the upgrade is done.*) Note also the CiviCRM debug log as mentioned in Jon's comment.
  6. **Observe upgrade progress** also: `SELECT version FROM civicrm_domain ;` - the result will change as CiviCRM progresses through the upgrade steps.
  7. **Generate a reduced dataset for testing**. I might bring the total contacts down to a couple thousand from the original dataset, and similarly reduce other large tables to a small sample of the original. This means the process is no longer an accurate test of the upgrade, but it does ensure you are testing schema changes, and if it can reduce each repeated upgrade run by minutes, it can save you hours down the track. If doing this, make sure you then test the full upgrade a few times before deploying - by this stage, it will run without your attention anyway, since (3).
  8. **Dump data for components you don't care about**. Some tables in CiviCRM can grow huge, and can steal more time then they deserve. Look at the table sizes ([MySQL: show largest tables](https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database)) and decide which are worth pruning. You might decide to drop mailings more than two years old, or an activity type you retired a while back, or ... (Jon also mentions this [here](https://civicrm.stackexchange.com/questions/16426/speeding-up-a-slow-upgrade).)
  9. **Consider turning off logging**. CiviCRM's trigger logging may affect upgrade performance, and you could turn it off during the upgrade. (This needs to be an informed decision, since a large chunk of data changes "off the radar" during upgrade may not be acceptable to your org.)
  10. **Ask for help**. You might find some assistance here or on [CiviCRM chat](https://chat.civicrm.org) if you're stuck. If you have a challenging upgrade to deal with, it's worth finding a good team to support your CiviCRM site!
  
  > **Comments:**
  > **username_1**: Posted this in response to [conversation on chat.civicrm.org](https://chat.civicrm.org/civicrm/pl/3b317jz45pdn8fx6cmek4sjuih) today. Please add your tips! Upgrades are admittedly a challenge for CiviCRM, and it's worth hearing how things go wrong so we can improve the process.  
  > **username_0**: wow!! `mytop`!!! that is cool!  
  > **username_1**: Apologies, this is really an answer to both this question and [What's the best way to speed up a slow upgrade?](http://civicrm.stackexchange.com/questions/16426/speeding-up-a-slow-upgrade), I stormed in here from the chat channel and didn't stop to see which question I was answering :D  
  
  ---
  
  ### Answer by username_2 on 2016-12-28. Score: 5
  
  The upgrader builds a queue of version-specific upgrade tasks -- then executes them. You can measure progress based on the size of the queue.
  
  If you use the web-based upgrader, it displays a progress-bar based on the queue.
  
  If you use the CLI to upgrade the DB, then it doesn't display a progress-bar. However, it would be patch-welcome to add something. (If I were doing this, I'd write a [`cv upgrade`](https://github.com/civicrm/cv) command which borrows heavily from <http://symfony.com/doc/current/components/console/helpers/progressbar.html> and <https://github.com/civicrm/civicrm-core/blob/master/CRM/Upgrade/Headless.php>).
  
  If you're looking for ways to poke under the hood, then you can look at SQL tables `civicrm_domain` and `civicrm_queue_item`. I wouldn't recommend building external processes on these, but they could be good for a quick peek. These (untested) queries should be interesting:
  
  * `select count(*) from civicrm_queue_item where queue_name = 'CRM_Upgrade'` (the queue gets smaller as tasks progress)
  * `select version from civicrm_domain` (updates to reflect the most recent incremental update)
  
  
  ---
  
  ### Answer by username_3 on 2016-12-28. Score: 3
  
  If you are upgrading to CiviCRM 4.6.10+, [the data is written](https://issues.civicrm.org/jira/browse/CRM-17324) in two places besides the screen.
  
  One place is the `civicrm_log` database table.
  
  The other is the CiviCRM log file. If you have shell access to your server, consider running `tail -f` on your CiviCRM log, [located in the ConfigAndLog directory](https://civicrm.stackexchange.com/questions/15931/where-are-the-configandlog-and-templates-c-directories).
  
  
  ---
  
  