Challenges with large data during platform upgrade

Development Quality Assurance
, , ,
1

Hi team,

We’re in the process of upgrading one of our installations… moving from 1.11 to 2.1.

The biggest challenge we’ve faced is the time it takes to execute liquibase changeset. In particular, Obs table took almost 8 hours in executing just the following:

Adding "status" column to obs table
Adding "interpretation" column to obs table
Modify column length to 1000 from 255

I know that Liquibase processes changes one-by-one, so an alter command to execute the above 3 in single query isn’t Liquibase style. But can we think of some alternative way? Like disabling keys.

1 Like
2

@owais.hussain there was some discussion on this thread: Suggestions to model marking an observation as "Abnormal"

My understanding is that if you’re running MySQL 5.7+ then you can just manually add those column via SQL prior to doing the OpenMRS upgrade, and MySQL will magically do it in the background. It will still take a long time, but you should be able to leave OpenMRS running while this happens. (I haven’t personally tested this, however. It would be great if you can test this and share the results so that we know for sure!)

3

I didn’t know OpenMRS is fully compatible with MySQL 5.7. Last time I tried, I couldn’t deploy platform for MySQL specific errors. I’ll see if we can redo the exercise.

4

OpenMRS platform 2.x runs very well with MySQL 5.7

5

We are running into this same problem as well… there are five changesets that modify the obs table, and on our database they take about an hour to run each.

We tested running those 5 changes as part of a single “alter” command, but it didn’t speed up the change at all (in fact it might have slowed it down).

We are currently looking into other solutions, such as using Percona. (https://www.percona.com/doc/percona-toolkit/2.0/pt-online-schema-change.html). Sounds like upgrading to mysql 5.7 might also be an option.

Take care, Mark

6

We’ve confirmed that 4 of the 5 changesets (the ones that ADD and DROP) can be run “online” without locks in MySQL 5.6 (though they still take about an hour each to run), with one caveat… if your database was originally created with a version of MySQL prior to 5.6, the first change will need to lock and copy the entire table, at which point it will be updated to 5.6.

The changeset that alters the value_complex requires a full column copy and can take take 1-2 hours to run… it reading the docs it also appears that upgrading to MySQL 5.7 would not help this since the size of the VARCHAR field is being changed from 255 to 1000 (see https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html).

I was working yesterday on using Percona Toolkit to do this and have come up with something that appears to be working… it takes a few hours to run, but allows all 5 changesets to be run while the system is “online”… Percona Toolkit stresses through out its docs that you should use read the documentation very carefully, so here’s the command we use in ‘dryrun’ format:

pt-online-schema-change --alter ‘ADD COLUMN form_namespace_and_path varchar(255), ADD COLUMN status varchar(32) NOT NULL DEFAULT “FINAL”, ADD COLUMN interpretation varchar(32), DROP COLUMN value_boolean, MODIFY COLUMN value_complex varchar(1000)’ D=openmrs,t=obs -u root --ask-pass --alter-foreign-keys-method drop_swap --charset utf8 --print --statistics --dry-run

(Note that there are backticks around the column names that seem to get lost in the formatting). To run it for “real” change “–dry-run” to “–execute”… but please review first and note that we haven’t run this in production yet!!

Note that after running this changeset, all the foreign key constraints will have been renamed… you’ll want to rename these back, as future liquibase changesets may reference them. The following should do the trick, but should not be run against a live system since we temporarily turn off key keys… but with key checks off it appears to run in a matter of seconds:

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE obs
DROP FOREIGN KEY `_answer_concept`,
DROP FOREIGN KEY `_answer_concept_drug`,
DROP FOREIGN KEY `_encounter_observations`,
DROP FOREIGN KEY `_obs_concept`,
DROP FOREIGN KEY `_obs_enterer`,
DROP FOREIGN KEY  `_obs_grouping_id`,
DROP FOREIGN KEY  `_obs_location`,
DROP FOREIGN KEY `_obs_name_of_coded_value`,
DROP FOREIGN KEY  `_obs_order`,
DROP FOREIGN KEY  `_person_obs`,
DROP FOREIGN KEY  `_previous_version`,
DROP FOREIGN KEY  `_user_who_voided_obs`,
ADD CONSTRAINT `answer_concept` FOREIGN KEY (`value_coded`) REFERENCES `concept` (`concept_id`),
ADD CONSTRAINT `answer_concept_drug` FOREIGN KEY (`value_drug`) REFERENCES `drug` (`drug_id`),
ADD CONSTRAINT `encounter_observations` FOREIGN KEY (`encounter_id`) REFERENCES `encounter` (`encounter_id`),
ADD CONSTRAINT `obs_concept` FOREIGN KEY (`concept_id`) REFERENCES `concept` (`concept_id`),
ADD CONSTRAINT `obs_enterer` FOREIGN KEY (`creator`) REFERENCES `users` (`user_id`),
ADD CONSTRAINT `obs_grouping_id` FOREIGN KEY (`obs_group_id`) REFERENCES `obs` (`obs_id`),
ADD CONSTRAINT `obs_location` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`),
ADD CONSTRAINT `obs_name_of_coded_value` FOREIGN KEY (`value_coded_name_id`) REFERENCES `concept_name` (`concept_name_id`),
ADD CONSTRAINT `obs_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`),
ADD CONSTRAINT `person_obs` FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`) ON UPDATE CASCADE,
ADD CONSTRAINT `previous_version` FOREIGN KEY (`previous_version`) REFERENCES `obs` (`obs_id`),
ADD CONSTRAINT `user_who_voided_obs` FOREIGN KEY (`voided_by`) REFERENCES `users` (`user_id`);

SET FOREIGN_KEY_CHECKS=1

Hope this helps, but please use with care and test first! I will report back if we run into any issues…

Also, I guess this should be all be moved to the wiki in the second about upgrading to 2.x… I’ll see if there’s an appropriate place (2.x release notes?)

Take care, Mark

1 Like