Platform 1.11.5 or above in implementation

AMPATH is doing assessment on upgrading from Platform 1.9.7 to 1.11.5. We are experiencing a few blockers. Currently, AMPATH has almost 220 millions obs, almost 800K patient records, and almost 6 millions encounters.

  • Patient search by name/ID took over 3 minutes for each search even though we have included additional indexes in obs, encounter, and patient* tables but it doesn’t speed up the search time.
  • Role-based Homepage Module cannot be started
  • Calculation Module cannot be installed and it blocks other modules that depend on this module.
  • For Remote Form Entry, it took four times of the usual time to generate/download the return data

We would like to know if other implementations have used Platform 1.11.5 or above. If so, what is your implementation details such as number of obs, number of encounters, number of patient records? Did you do an upgrade from older Platform version? If so, from which old Platform version to new Platform version? Have you encountered similar issues that AMPATH has?

1 Like

We are currently using 1.10.x, so can’t help with your issues directly, but, as we are always concerned with performance (and particularly performance degradation after updates) as well, the patient search metrics caught my eye. Just to clarify, in this a slowdown, and, if so, how much? How long did similar searches take in 1.9.7?

Thanks!

Mark

Thanks, @mogoodrich. In 1.9.7, it takes a second to get the patient search result but it’s taking over 3 minutes in 1.11.5. We double checked that all the indexes were included in our 1.11.5 setup. Any suggestion where to look next?

We’ve done an upgrade from 1.9 to 1.11.4 in eSaude (Mozambique), but we haven’t put it in production sites yet. All of those modules started fine for us. As far as performance goes, since we haven’t gone beyond our demo server yet with only a few patients, we can’t tell you if we will see any loss. But 3 minutes for searching concerns me!

1 Like

When doing this 3 minutes search, do the tomcat logs show any errors? Do you have some time to try do the same search without any modules and see if it is still slow? Am also assuming you are still using the same server resources as for the old version (memory, etc)

Well Daniel, first of all we are not using the same resources as the production however the performance degradation is quite significant. There are several reasons to conclude this has to do with how the search is performed and not really the resource constraints because when I examined the resource utilization during any of the search operation I noticed the RAM & CPU do not hit the server limits. As a matter of fact I thought mysql server setup was not optimized enough so I went in and tweak the configurations to give it enough memory allocation, the result is a bit of improvement (Now it took average of 2 minutes, that is down by 1 minute) and again mysql was not hitting the allocated max. CPU utilization is always less than 2% and to top it all although production server is far more powerful, the server we are using for testing is accessed by very few users in comparison in any given time!

After some time of tweaking tomcat & mysql I decided it was about time to examine what is really happening. So I figured the easiest way I could know whether it was a UI or an API thing is to fire an equivalent REST request. It turned out even that was too slow although slightly faster by very few seconds. So I decided to run a profiler, the results shows that most of time is spent doing the hibernate magic. The HibernatePatientDAO.getCountOfPatients() and HibernatePatientDAO.getPatients() takes significant portion of time to return. Removing attribute searches from criteria improved the search significantly, from 2 minutes to average of 30 seconds. I went further and separate identifier searches from name searches this brought down time for names searches further to an average of 1 second. Unfortunately identifier searches even with this separation still takes an average of 17 seconds. I am currently investigating the reason for this difference.

I hope this can shed some light on where to start looking.

Thanks everyone for responding so fast.

1 Like

Hi @ayeung, Sorry to hear about the performance issues. Something isn’t right. :frowning: in Jeremie, Haiti we were running Reference App 2.1.1 (on Platform 1.11). We didn’t have any issues, but the database there didn’t compare with your stats. We had… 101,430 patients 562,984 encounters 2,096,741 observations

This is very useful feedback @willa

What is the value of this global property? search.caseSensitiveDatabaseStringComparison What is the value of this global property? person.attributeSearchMatchMode

This is in relation to TRUNK-4563 and TRUNK-2472

1 Like

@dkayiwa Thanks for getting back and sorry for a late reply. Apparently the issues you have mentioned is exactly what we were facing. However there is another observation for us. I ran the bare queries on MySQL with and without lower() and below are the results.

The query generated by Platform 1.11.5 without any modification took 3.17 minutes:

select 
	this_.patient_id as person1_216_4_, 
	this_1_.uuid as uuid216_4_, 
	this_1_.gender as gender216_4_, 
	this_1_.birthdate as birthdate216_4_, 
	this_1_.birthdate_estimated as birthdate5_216_4_, 
	this_1_.birthtime as birthtime216_4_, 
	this_1_.dead as dead216_4_, 
	this_1_.death_date as death8_216_4_, 
	this_1_.deathdate_estimated as deathdate9_216_4_, 
	this_1_.cause_of_death as cause10_216_4_, 
	this_1_.creator as creator216_4_, 
	this_1_.date_created as date12_216_4_, 
	this_1_.changed_by as changed13_216_4_, 
	this_1_.date_changed as date14_216_4_, 
	this_1_.voided as voided216_4_, 
	this_1_.voided_by as voided16_216_4_, 
	this_1_.date_voided as date17_216_4_, 
	this_1_.void_reason as void18_216_4_, 
	this_.patient_id as patient1_229_4_, 
	this_.creator as creator229_4_, 
	this_.date_created as date3_229_4_, 
	this_.changed_by as changed4_229_4_, 
	this_.date_changed as date5_229_4_, 
	this_.voided as voided229_4_, 
	this_.voided_by as voided7_229_4_, 
	this_.date_voided as date8_229_4_, 
	this_.void_reason as void9_229_4_, 
	case when exists (select * from patient p where p.patient_id = this_1_.person_id) then 1 
		else 0 
		end as formula1_4_, 
	name1_.person_name_id as person1_221_0_, 
	name1_.uuid as uuid221_0_, 
	name1_.preferred as preferred221_0_, 
	name1_.person_id as person4_221_0_, 
	name1_.prefix as prefix221_0_, 
	name1_.given_name as given6_221_0_, 
	name1_.middle_name as middle7_221_0_, 
	name1_.family_name_prefix as family8_221_0_, 
	name1_.family_name as family9_221_0_, 
	name1_.family_name2 as family10_221_0_, 
	name1_.family_name_suffix as family11_221_0_, 
	name1_.degree as degree221_0_, name1_.creator as creator221_0_, 
	name1_.date_created as date14_221_0_, 
	name1_.changed_by as changed15_221_0_, 
	name1_.date_changed as date16_221_0_, 
	name1_.voided_by as voided17_221_0_, 
	name1_.date_voided as date18_221_0_, 
	name1_.void_reason as void19_221_0_, 
	name1_.voided as voided221_0_, 
	attribute2_.person_id as person3_216_6_, 
	attribute2_.person_attribute_id as person1_6_, 
	attribute2_.person_attribute_id as person1_217_1_, 
	attribute2_.uuid as uuid217_1_, 
	attribute2_.person_id as person3_217_1_, 
	attribute2_.person_attribute_type_id as person4_217_1_, 
	attribute2_.value as value217_1_, 
	attribute2_.creator as creator217_1_, 
	attribute2_.date_created as date7_217_1_, 
	attribute2_.changed_by as changed8_217_1_, 
	attribute2_.date_changed as date9_217_1_, 
	attribute2_.voided_by as voided10_217_1_, 
	attribute2_.date_voided as date11_217_1_, 
	attribute2_.voided as voided217_1_, 
	attribute2_.void_reason as void13_217_1_, 
	attributet3_.person_attribute_type_id as person1_218_2_, 
	attributet3_.uuid as uuid218_2_, 
	attributet3_.name as name218_2_, 
	attributet3_.description as descript4_218_2_, 
	attributet3_.format as format218_2_, 
	attributet3_.foreign_key as foreign6_218_2_, 
	attributet3_.sort_weight as sort7_218_2_, 
	attributet3_.searchable as searchable218_2_, 
	attributet3_.creator as creator218_2_, 
	attributet3_.date_created as date10_218_2_, 
	attributet3_.changed_by as changed11_218_2_, 
	attributet3_.date_changed as date12_218_2_, 
	attributet3_.retired_by as retired13_218_2_, 
	attributet3_.date_retired as date14_218_2_, 
	attributet3_.retire_reason as retire15_218_2_, 
	attributet3_.retired as retired218_2_, 
	attributet3_.edit_privilege as edit17_218_2_, 
	ids4_.patient_id as patient3_216_7_, 
	ids4_.patient_identifier_id as patient1_7_, 
	ids4_.patient_identifier_id as patient1_230_3_, 
	ids4_.uuid as uuid230_3_, ids4_.patient_id as patient3_230_3_, 
	ids4_.identifier as identifier230_3_, 
	ids4_.identifier_type as identifier5_230_3_, 
	ids4_.location_id as location6_230_3_, 
	ids4_.date_created as date7_230_3_, 
	ids4_.date_voided as date8_230_3_, 
	ids4_.preferred as preferred230_3_, 
	ids4_.voided as voided230_3_, 
	ids4_.void_reason as void11_230_3_, 
	ids4_.creator as creator230_3_, 
	ids4_.voided_by as voided13_230_3_, 
	ids4_.changed_by as changed14_230_3_, 
	ids4_.date_changed as date15_230_3_ 
from 
	patient this_ inner join person this_1_ on this_.patient_id=this_1_.person_id 
	inner join person_name name1_ on this_.patient_id=name1_.person_id 
	left outer join person_attribute attribute2_ on this_.patient_id=attribute2_.person_id 
	left outer join person_attribute_type attributet3_ on attribute2_.person_attribute_type_id=attributet3_.person_attribute_type_id 
	left outer join patient_identifier ids4_ on this_.patient_id=ids4_.patient_id 
where 
(((name1_.voided=0 and (name1_.given_name like 'wafula' or name1_.middle_name like 'wafula' or name1_.family_name like 'wafula' or name1_.family_name2 like 'wafula')))
	or ((attributet3_.searchable=1 and attribute2_.voided=0 and lower(attribute2_.value) like 'wafula'))
	or (ids4_.voided=0 and lower(ids4_.identifier) like 'wafula'))
and this_.voided=0 
order by attribute2_.voided asc, attribute2_.date_created desc, ids4_.voided asc, ids4_.preferred desc, ids4_.identifier_type desc,
ids4_.date_created desc, name1_.given_name asc, name1_.middle_name asc, name1_.family_name asc;

Modifying the above query removing the lower() mysql function made the query to take about 46 seconds which is a massive improvement. However this is too long for practical purposes so I decided to rid attribute searches altogether because we don’t actually search for attributes at AMPATH (or so it seems). The performance improved further down to about 10 seconds. But then again this is still not enough so I decided to separate the name searches from identifier searches, finally this gave results near instantaneously:

https://github.com/mhawila/openmrs-core/commit/47f108046cde27b98d3055edaaac9ea9f7ca8d6b

Remarks & Afterthoughts

When going through the HibernatePatientDAO class I realize there is a lot of flexibilities provided by the API which is not accessible via UI. I was thinking it could be better to expose these functionalities to provide the users with enough options to tweak the patient search to suit their purposes. I had to manually do the quick fix because the default implementation assumes several things which may be okay for majority of purposes but not exactly suitable for some few implementation like AMPATH.

Any thoughts on this would be nice.

Thanks.

2 Likes

@willa excellent work! Do you think you can create a ticket and pull request?

1 Like

@dkayiwa I don’t think the changes I have made can be applied to master because I have made an assumption that any string with a number is an identifier. Do you really think my assumptions can be universal?

No they are not. I just meant a pull request with the real universal thing. :smile:

Just want to give an update to this post that AMPATH has successfully upgraded from 1.9.7 to 1.11.5 a couple weeks ago. One minor issue was identified on Java version compatibility - see Java creating new threads after upgrade to Platform 1.11.5. Except that, the upgrade progress went nice and smooth within a weekend. Big thanks to everyone who has helped during our testing process as well as the AMPATH Team (@willa, @amiencha, @jdick, and more).

2 Likes

Great to hear that the upgrade was successful!

And thanks much for coming back to update us here!

Can someone from your team comment on how you ended up resolving the patient search slowness issue?

1 Like

The answer is in post #9 above.

I see, so ultimately you are forced to run on a (slightly) forked version of openmrs-core. It’s unfortunate that you have to do that.

I guess the slowdown must be due to the fact that in Platform 1.11 we started searching on person attributes:

While that’s a nice feature, if it causes horrible slowdown for large databases, it’s not worth it.

Some possibile approaches:

  1. Quick fix (might work): if no attributes are marked as “searchable” then don’t search for attributes (don’t join against more tables, etc)
  2. Would definitely work: add a global property to enable/disable searching on attributes (and if it’s turned off, then don’t show the “searchable” checkbox on managing attributes)
  3. Right thing to do: rewrite patient searching from scratch, to use lucene/hibernate-search, since this was added in Platform 1.11.

Anyone interested in looking into this?

I am very interested, unfortunately we have things that are coming up in our pipeline that I doubt I will have enough time to look at this. In any case I will do my best to find some time for this, most likely after one month from today.

2 Likes

Hi,

During the upgrade did you get any error? like Error executing SQL alter table reporting_report_design modify report_definition_uuid char(38) not null: Cannot change column ‘report_definition_uuid’: used in a foreign key constraint ‘reporting_report_design_ibfk_3’.

Can you list me the of the modules you are using?

Regards

@willa or anyone else who wants to try rewriting the patient search in lucene, feel free to reach out to me for help. I could provide more details or even help you get started in a short pair programming session.

1 Like

Hi, @steliomo,

Please see https://wiki.ampath.or.ke/x/IACyBQ and https://wiki.ampath.or.ke/x/xgClBQ.

Thanks!