How can I monitor the progress of a slow upgrade?

Question

CiviCRM upgrades can take a long time.

What is the best way of seeing where they are up to?

Answer 1

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 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 2

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 also...)

5. Observe DB activity during the upgrade. Use 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) 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.)

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 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!

Answer 3

If you are upgrading to CiviCRM 4.6.10+, the data is written 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.