AMPATH 1.9 Testing

[Manu Tars]

Hi Daniel,
The sql dump file generated by the release test module is 12G. Our data base size show the following size

49401864    ./obs.ibd
15577092    ./hl7_in_archive.ibd
1421316    ./encounter.ibd
770052    ./person_attribute.ibd
327680    ./idcards_generated_identifier.ibd
241664    ./person_name.ibd
188416    ./patient_identifier.ibd
159744    ./formentry_error.ibd

obs hl7 and encounter are the biggest, if the size of these could be reduced in the sql dump then it may make it possible for us to import the file to 1.9 stand alone for testing

Thank you

Manu
Ampath

[Wyclif Luyima]

Having almost 50 million obs and 15 million encounters for only 100 patients, i think there is something wrong with the script that copies data from the production database.

Wyclif

---

Click here to unsubscribe from OpenMRS Implementers' mailing list

[Darius Jazayeri]

Remember that you added a feature to bring the patient with the most encounters and most observations? Could there be a test patient with an absurd number of observations?

-Darius (by phone)

[Rafal Korytkowski]

My understanding is that the sizes of tables given by Manu are before the module is used.

The big size of the dump comes from the hl7_in_archive table and other tables are already properly filtered.

-Rafał (by phone)

[Burke Mamlin]

What is the ticket for this issue?  I only see two open tickets for RELTEST, neither of which appears to address this issue.

-Burke

[Jeremy Keiper]

We should be able to mimic the query to find the patient with the most observations / encounters on the test system ... what is that query? (I can guess, but figured I would use the one from the module)

Jeremy Keiper
OpenMRS Core Developer
AMPATH / IU-Kenya Support

-- OpenMRS Implementers: http://go.openmrs.org/implementers
Post: implem...@openmrs.org
Unsubscribe: implementers...@openmrs.org

[Darius Jazayeri]

Those queries are:

String sql = "select patient_id from encounter " + "group by patient_id " + "having count(patient_id) =  "
        + "(select max(the_count) from (select patient_id, count(patient_id) as the_count from encounter "
        + "group by patient_id) as t) limit 1";

String sql = "select patient_id from encounter e inner join obs o " + "on e.encounter_id = o.encounter_id "
        + "group by patient_id " + "having count(obs_id) =  " + "(select max(the_count) from  "
        + "(select patient_id, count(obs_id) as the_count from encounter e "
        + "inner join obs o on e.encounter_id = o.encounter_id " + "group by patient_id) as t) limit 1";

You want to ensure the GP "releasetestinghelper.maxPatientCount" has a value that isn't too big, e.g. 100.

And also, I noticed that we get maxPatientCount-2 random patients + the one with the most obs + the one with the most encounters, then we look at the relationships table to get them + all their relations. So if you someone with a ton of relationships, that might mess things up.

-Darius

[Jeremy Keiper]

We do have one person with 545,808 observations.  All of them are voided, but the patient is not.  The next highest has 12,333 observations ... after that, the numbers become more realistic (6,155; 5,985; 5,742).

I believe this anomaly came from an HL7 importing problem that ran undetected for some time, then we cleaned it up by voiding (not deleting) those observations.

Jeremy Keiper
OpenMRS Core Developer
AMPATH / IU-Kenya Support

[Jeremy Keiper]

The top 5 number of relationships per person is: 14, 12, 10, 8, 8 ... so I don't think that's a problem.

Jeremy Keiper
OpenMRS Core Developer
AMPATH / IU-Kenya Support

[Burke Mamlin]

Sorry, could someone please identify the ticket for this effort?  I would like to follow it.

Thanks.

-Burke

[Mark Goodrich]

On a related note, I’ve started testing upgrading a Rwanda-sized (large) database from 1.6 to 1.9.  I have install the InnoDB performance plugin that Jeremy mentioned a few months back.

 

Right now the upgrade has been hung for the past hour on the liquibase changeset:

 

Author: Harsha.cse Comments: Drop Not-Null constraint from location column in Encounter and Obs table Description: Drop Not-Null Constraint (x2)

 

Now the obs table I am testing on has  6 million+ observations… but does it make sense that the time to drop a not-null constraint on a column would be dependent on the number of rows in the table?

 

Mark

[Burke Mamlin]

Maybe it's related to the known limitation of dropping foreign keys in MySQL 5.5:

MySQL 5.5 does not support efficient creation or dropping of FOREIGN KEY constraints. Therefore, if you use ALTER TABLE to add or remove a REFERENCES constraint, the child table is copied, rather than using Fast Index Creation.

Maybe MySQL is copying the entire table just to drop the not-null constraint.  Bummer.

-Burke

[Mark Goodrich]

Hmm… hopefully not… I installed the InnoDB performance plugin that supposed fixes this issue… maybe I haven’t installed it correctly?

[Jeremy Keiper]

That's exactly what's happening (what Burke said).  The same thing happens when you add or remove a column.  This means AMPATH requires 48 hours for just the obs table structure to be changed during major upgrades, each minor change completely copying the table.

One way of fixing that phenomenon is to condense liquibase updates somehow so that all structural changes happen at once to a table ... but that could be a monolithic task.

Jeremy Keiper
OpenMRS Core Developer
AMPATH / IU-Kenya Support

[Mark Goodrich]

@Jeremy:  How many obs do you have in your database? I’m trying to get a ballpark of how long the Rwanda update will take.

 

I know that this has been discussed before, but this is something that needs to be addressed.  48 hours for an upgrade is unacceptable and would most likely prevent our Rwanda sites from ever upgrading.

 

Mark

[Jeremy Keiper]

@Mark -

mysql> select voided, count(obs_id) from obs group by voided;

+--------+---------------+

| voided | count(obs_id) |

+--------+---------------+

|      0 |     127957372 |

|      1 |       1566712 |

+--------+---------------+

2 rows in set (1 min 7.78 sec)

Jeremy Keiper
OpenMRS Core Developer
AMPATH / IU-Kenya Support

[Mark Goodrich]

Jeremy—

 

What version of mysql are you using?  I’m wondering if there is any value (and risk) in  us upgrading from 5.1 to 5.5 in Rwanda.

[Jeremy Keiper]

Mark -

We are using 5.1 ... not sure what 5.5 would be like.  One thing I would suggest is using the innodb plugin that comes with MySQL.  You have to enable it, but I experienced quite a difference in building indexes after enabling it.  We have not enabled it on AMPATH's production system yet, but I believe we are testing it for remote sites.  I tested it on a dev server previously.

Jeremy Keiper
OpenMRS Core Developer
AMPATH / IU-Kenya Support

[Mark Goodrich]

Yes, Dave T. pointed me in the direction of the InnoDB plugin which you recommended.  I installed it yesterday, but unfortunately it only improves indexing… creating/removing foreign keys and columns still seem to require a full table copy.

 

Fyi, one thing I discovered is that the “show engine innodb status” command shows the number of rows inserted/updated/deleted/read, along with the average inserts, etc, per second, so it allows you to monitor the progress of a large operation.

 

Take care,

[Jeremy Keiper]

Mark,

I wonder what a multiple-column change would do ... and whether that would be a good use of developer time, to reduce the liquibase updates so each table is recreated just once to cover an entire release (like all of 1.7 or all of 1.8.1, all of 1.8.2, etc).

Jeremy Keiper
OpenMRS Core Developer
AMPATH / IU-Kenya Support

[Mark Goodrich]

I definitely think this is worth considering.  Also, we should all be aware of this as developers when writing liquibase changesets… in one case I noticed the date_started and date_stopped columns in the obs tables are dropped in two successive changesets.  These certainly could have been handled in a single changeset.