OpenMRS database performance issues after update [Fixed]

Hello OpenMRS community,

We are trying to update Bahmni to version 0.92, moving from Centos 6.8 to 7.6. However in the update I think we are having an issue with the OpenMRS database.

I’ve installed the new system and restored the MySQL databases to the new server. The method I am trying is, after a standard install of bahmni, to drop the mysql databases and then restore them from a backup from our current system. Then when openmrs is started up again, my hope was that it would automatically update the databases (using liquibase).

This seems to have worked fine, and the new system is up an running. But a few large mysql queries are very, very slow on the new system. I’ll include a query below. On the previous (working) system, this query completes in about 30ms. On the new system it takes about 45 seconds.

Some things I’ve tried to investigate or have noticed:

  • the previous system indicates row counts for some tables that are lower than expected, like saying there are 1.1 million observations when in reality there are 2.1 million.
  • on the previous system, /var/lib/mysql/openmrs takes about 1.9gb, while on the new system it is 1.6gb
  • In the query below, removing joins and subquery little by little and re-running the query seems to increase the speed steadily - I can’t find anything in particular about the query that is an issue.
  • Some of the large subqueries with LEFT OUTER JOIN in this query are empty on my db, yet removing them makes a big difference in speed.
  • The previous system is running on a VM on a server, and the new system I am setting up is running on a VM on my desktop. The new system has the same VirturalBox VM hardware as the previous system, and the same format of disk (vmdk fixed size) and is running on an SSD.
  • I noticed that on the new system, when running the query all 4 cores start up but then 3 of them drop to 0% usage and 1 keeps going at 100%. mysqd doesn’t show memory usage going up or down.

It is hard to believe the difference in hardware is the issue, given the big difference in time. But as a check the next thing I can try is to copy the VM of the previous system onto the same hardware where the new is being developed, to see if that makes a difference.

Any help would be really appreciated. This is one of the big issues holding back updating for us. Is there a different method we should be using to try to migrate the database over to the new system?

  b.bed_number AS 'Bed',
  concat(pn.given_name, ' ', ifnull(pn.family_name,'')) AS 'Name',
  pv.uuid AS 'Patient Uuid',
  pi.identifier AS 'Id',
  pv.gender AS 'Gender',
  TIMESTAMPDIFF(YEAR, pv.birthdate, CURDATE()) AS 'Age',
  pa.city_village AS 'Village',
  patt.value AS 'Country',
  admission_provider_name.given_name AS 'Admission By',
  cast(DATE_FORMAT(latestAdmissionEncounter.max_encounter_datetime, '%d %b %y %h:%i %p') AS CHAR) AS 'Admission Time',
  diagnosis.diagnosisConcept AS 'Diagnosis',
  diagnosis.certainty AS 'Diagnosis Certainty',
  diagnosis.diagnosisOrder AS 'Diagnosis Order',
  diagnosis.status AS 'Diagnosis Status',
  diagnosis.diagnosis_provider AS 'Diagnosis Provider',
  cast(DATE_FORMAT(diagnosis.diagnosis_datetime, '%d %b %y %h:%i %p') AS
       CHAR) AS 'Diagnosis Datetime',
  dispositionInfo.providerName AS 'Disposition By',
  cast(DATE_FORMAT(dispositionInfo.providerDate, '%d %b %y %h:%i %p') AS CHAR) AS 'Disposition Time',
  adtNotes.value_text AS 'ADT Notes',
  v.uuid AS 'Visit Uuid'
FROM bed_location_map blm
  INNER JOIN bed b
    ON blm.bed_id = b.bed_id AND
       b.status = 'OCCUPIED' AND
       blm.location_id IN (SELECT child_location.location_id
                           FROM location child_location JOIN
                             location parent_location
                               ON parent_location.location_id =
                    = "Mens Ward")
  INNER JOIN bed_patient_assignment_map bpam ON b.bed_id = bpam.bed_id AND date_stopped IS NULL
  INNER JOIN person pv ON pv.person_id = bpam.patient_id
  INNER JOIN person_name pn ON pn.person_id = pv.person_id
  INNER JOIN patient_identifier pi ON pv.person_id = pi.patient_id
  INNER JOIN patient_identifier_type pit on pi.identifier_type = pit.patient_identifier_type_id
  INNER JOIN global_property gp on'bahmni.primaryIdentifierType' and gp.property_value=pit.uuid
  LEFT JOIN person_address pa ON pa.person_id = pv.person_id
  LEFT JOIN person_attribute patt ON patt.person_id = pv.person_id and patt.person_attribute_type_id = "28"
                max(encounter_datetime) AS max_encounter_datetime,
                max(visit_id) as visit_id,
                max(encounter_id) AS encounter_id
              FROM encounter
                INNER JOIN encounter_type ON encounter_type.encounter_type_id = encounter.encounter_type
              WHERE = 'ADMISSION'
              GROUP BY patient_id) latestAdmissionEncounter ON pv.person_id = latestAdmissionEncounter.patient_id
  INNER JOIN visit v ON latestAdmissionEncounter.visit_id = v.visit_id
  LEFT OUTER JOIN obs adtNotes
    ON adtNotes.encounter_id = latestAdmissionEncounter.encounter_id AND adtNotes.voided = 0 AND
       adtNotes.concept_id = (SELECT concept_id
                              FROM concept_name
                              WHERE name = 'Adt Notes' AND concept_name_type = 'FULLY_SPECIFIED')
  LEFT OUTER JOIN encounter_provider ep ON ep.encounter_id = latestAdmissionEncounter.encounter_id
  LEFT OUTER JOIN provider admission_provider ON admission_provider.provider_id = ep.provider_id
  LEFT OUTER JOIN person_name admission_provider_name
    ON admission_provider_name.person_id = admission_provider.person_id
                      bpam.patient_id AS person_id,
             AS disposition,
                      latestDisposition.obs_datetime AS providerDate,
                      person_name.given_name AS providerName
                    FROM bed_patient_assignment_map bpam
                      INNER JOIN (SELECT
                                    max(obs_id) obs_id
                                  FROM obs
                                  WHERE concept_id = (SELECT concept_id
                                                      FROM concept_name
                                                        name = 'Disposition' AND concept_name_type = 'FULLY_SPECIFIED')
                                  GROUP BY person_id) maxObsId ON maxObsId.person_id = bpam.patient_id
                      INNER JOIN obs latestDisposition
                        ON maxObsId.obs_id = latestDisposition.obs_id AND latestDisposition.voided = 0
                      INNER JOIN concept_name ON latestDisposition.value_coded = concept_name.concept_id AND
                                                 concept_name_type = 'FULLY_SPECIFIED'
                      LEFT OUTER JOIN encounter_provider ep ON latestDisposition.encounter_id = ep.encounter_id
                      LEFT OUTER JOIN provider disp_provider ON disp_provider.provider_id = ep.provider_id
                      LEFT OUTER JOIN person_name ON person_name.person_id = disp_provider.person_id
                    WHERE bpam.date_stopped IS NULL
                  ) dispositionInfo ON pv.person_id = dispositionInfo.person_id
                      diagnosis.person_id AS person_id,
                      diagnosis.obs_id AS obs_id,
                      diagnosis.obs_datetime AS diagnosis_datetime,
                      if( IS NOT NULL,,
                         diagnosis.value_text) AS diagnosisConcept,
             AS certainty,
             AS diagnosisOrder,
             AS status,
                      person_name.given_name AS diagnosis_provider
                    FROM bed_patient_assignment_map bpam
                      INNER JOIN visit latestVisit
                        ON latestVisit.patient_id = bpam.patient_id AND latestVisit.date_stopped IS NULL AND
                           bpam.date_stopped IS NULL
                      INNER JOIN encounter ON encounter.visit_id = latestVisit.visit_id
                      INNER JOIN obs diagnosis ON bpam.patient_id = diagnosis.person_id AND diagnosis.voided = 0 AND
                                                  diagnosis.encounter_id = encounter.encounter_id AND
                                                  diagnosis.concept_id IN (SELECT concept_id
                                                                           FROM concept_name
                                                                           WHERE name IN
                                                                                 ('Coded Diagnosis', 'Non-Coded Diagnosis')
                                                                                 concept_name_type = 'FULLY_SPECIFIED')
                      LEFT OUTER JOIN concept_name diagnosisConceptName
                        ON diagnosis.value_coded IS NOT NULL AND diagnosis.value_coded = diagnosisConceptName.concept_id
                           AND diagnosisConceptName.concept_name_type = 'FULLY_SPECIFIED'
                      LEFT OUTER JOIN encounter_provider ep ON diagnosis.encounter_id = ep.encounter_id
                      LEFT OUTER JOIN provider diagnosis_provider ON diagnosis_provider.provider_id = ep.provider_id
                      LEFT OUTER JOIN person_name ON person_name.person_id = diagnosis_provider.person_id
                      INNER JOIN obs certainty
                        ON diagnosis.obs_group_id = certainty.obs_group_id AND certainty.voided = 0 AND
                           certainty.concept_id = (SELECT concept_id
                                                   FROM concept_name
                                                   WHERE name = 'Diagnosis Certainty' AND
                                                         concept_name_type = 'FULLY_SPECIFIED')
                      LEFT OUTER JOIN concept_name certaintyConceptName
                        ON certainty.value_coded IS NOT NULL AND certainty.value_coded = certaintyConceptName.concept_id
                           AND certaintyConceptName.concept_name_type = 'FULLY_SPECIFIED'
                      INNER JOIN obs diagnosisOrder
                        ON diagnosis.obs_group_id = diagnosisOrder.obs_group_id AND diagnosisOrder.voided = 0 AND
                           diagnosisOrder.concept_id = (SELECT concept_id
                                                        FROM concept_name
                                                        WHERE name = 'Diagnosis order' AND
                                                              concept_name_type = 'FULLY_SPECIFIED')
                      LEFT OUTER JOIN concept_name diagnosisOrderConceptName ON diagnosisOrder.value_coded IS NOT NULL
                                                                                AND diagnosisOrder.value_coded =
                                                                                = 'FULLY_SPECIFIED'
                      LEFT JOIN obs diagnosisStatus
                        ON diagnosis.obs_group_id = diagnosisStatus.obs_group_id AND diagnosisStatus.voided = 0 AND
                           diagnosisStatus.concept_id = (SELECT concept_id
                                                         FROM concept_name
                                                         WHERE name = 'Bahmni Diagnosis Status' AND
                                                               concept_name_type = 'FULLY_SPECIFIED')
                      LEFT OUTER JOIN concept_name diagnosisStatusConceptName ON diagnosisStatus.value_coded IS NOT NULL
                                                                                 AND diagnosisStatus.value_coded =
                                                                                 = 'FULLY_SPECIFIED'
                  ) diagnosis ON diagnosis.person_id = pv.person_id

What is the value for the setting named search.caseSensitiveDatabaseStringComparison?

It was set to ‘true’. I’ve tried setting it to ‘false’.

But also this problem exists for the database even with openmrs shut down - just running the query directly.

I’ve copied the openmrs database from the “live” (previous) system to the new VM to test the query, and it was very slow.

Then I copied the openmrs database from the new system to the “live” VM to test the query, and it is fast.

So I suppose the issue must be one of:

  • the difference in physical hardware
  • a difference in VirtualBox between the systems. (One is running on Windows, the other on Linux)
  • a difference in the VM OS (one is CentOS 6.8, the other CentOS 7.6)
  • a difference in mysql version or configuration between the two. The previous system is running 14.14 Distrib 5.6.34, and the new 14.14 Distrib 5.7.30.

It’s good to know that it isn’t an issue with the database or the data. I need to try to work out which of these could be the issue.

I’m still trying to work through this issue. Here is about how fast the query runs on different hosts + guests. (The query is to list the inpatients in the ADT app).

guest VM PC Host Server Host
Centos 6.8 (0.89) 0.75s 0.05s
Centos 7.6 (0.92) 40s 15s

The better hardware of the server makes a difference but still isn’t a solution. I also tried installing mysql and copying over the db to a new VM with Centos 7.6 (without Bahmni) on the PC host, and the performance is also bad, taking several minutes to complete the query.

I’ve noticed that the new system (0.92) uses XFS filesystem, and the old one EXT4. That is something else I may check. Otherwise I’m so far at a loss as to what is going on. I know it may be something simple that I’ve overlooked.

After trying many different things I’ve finally found the issue. The derived_merge=on mysql optimizer setting is what is causing the slowdown, and turning it to off fixes the problem.


Very interesting! Thanks @mdg583 for sharing the solution. :slight_smile: