Patient Reminders (4.1.0) fails on large db

tmccormi wrote on Friday, September 30, 2011:

We added to “Diabetic Reminders” and then clicked "Administratration -> “Patient Reminders” .   On one system the query never finished and we had to kill it and turn of the reminder.  On the other it finished, but produced only a white screen of death…   All my previous tests were successful on small to very small datasets.      Suggestions?

-Tony

bradymiller wrote on Saturday, October 01, 2011:

Hi Tony,

To begin trying to isolate the bottleneck(s) in performance, would be helpful to know:
1) At about how many rows of patient_data (ie. size of the database) the problem begins appearing
2) going through adminstration->alerts and working through the rules(set as Patient Reminder) one at a time and seeing which ones specifically cause this issue and which ones are fine
3) also playing around with reports in Reports->Clinical to see which ones are running fine and which ones aren’t

thanks,
-brady

yehster wrote on Sunday, October 02, 2011:

Rather than just guessing where the bottlenecks are, it might be useful to run XDEBUG and use KcacheGrind to profile the app and isolate the problem directly.

-Kevin Yeh

zhhealthcare wrote on Sunday, October 02, 2011:

When sending the patient reminders  , one thing I noticed is some messages are going and some is failing, and when we send the reminder again the failed one is getting transferred.  This behavior I noticed on a very small database (xamp set up). The mail was configured through Mercury.

Eldho
ZHhealthcare

tmccormi wrote on Sunday, October 09, 2011:

the patient database is 4,517 records on one of them.  running Administration->Reminders drives my system load to 13+ on the ubuntu server.  It eventually completes, 30 to 40 minutes later, but there are information in the page source past the </head> tag. 

Yehster says:

Rather than just guessing where the bottlenecks are, it might be useful to run XDEBUG and use KcacheGrind

However I do not have any idea how you would do that.    Have you done this?  Can you describe how?

This is a show stopper for using reminders in any kind of clinic with a real database if you ask me.  It’s very slow for just single look-ups as well (widgets).  Impossible for the overall batch tool.

Does anyone besides me have customers with real size databases?  Are you experiencing the same issues?

-Tony

bradymiller wrote on Sunday, October 09, 2011:

Hi Tony and everybody,

Placed a wiki page here summarizing the forum threads that have been related to slow CDR engine performance:
http://open-emr.org/wiki/index.php/CDR_Performace#Plan

What is needed now is testing and reporting of these tests on the CDR engine related scripts(as described on the wiki page).

thanks,
-brady

tmccormi wrote on Monday, October 10, 2011:

OK … Here some results.

The Database:
Patient_data = 4517
Lists = 13753
form_encounter = 8168

I turned off logging as it was timing out and crashing the query in log,inc exceeding 120 sec max execution.  when I did that the query completed.  

The bad news.  I hard coded a limit of the first 1000 pids.  It still took 30 minutes on a quadcore PC with 4 gig of ram.  It was all CPU; pegging one CPU at a time 90%+

When it completed this was the content of the $logging array.

Count of PIDS: int(1000)

$logging: array(9) { => int(360) => int(0) => int(0) => int(360) => int(0) => int(0) => int(0) => int(360) => int(360) } LOGarray(9) { => int(360) => int(0) => int(0) => int(360) => int(0) => int(0) => int(0) => int(360) => int(360) }

Running “show processlist” once every second seems to show that the following query is the culprit, it takes between 2-5 secs per PID per query to complete.

Sending data | SELECT * FROM `lists` WHERE `type`='medical_problem' AND `pid`='167' AND `diagnosis` LIKE '%ICD9:250

same for the other ICD9:684 query

-Tony

bradymiller wrote on Monday, October 10, 2011:

Hi,
Remove all Diabetes-related Patient Reminders from Administration->Alerts and try it again. Curious if this speeds things up.
-brady

bradymiller wrote on Monday, October 10, 2011:

Also,
See if placing an index on the ‘pid’ column in the ‘lists’ tables helps.
-brady

bradymiller wrote on Monday, October 10, 2011:

Also,
(sorry for all the messages, just keep having new thoughts)
Could be even more aggressive with the indexes and place an index on the ‘type’ column in the ‘lists’ table too (in addition to the ‘pid’ column).
-brady

tmccormi wrote on Monday, October 10, 2011:

One issue that is strictly “user interface” is that the report assumes it won’t take long to run, so it displays nothing until it is complete, leaving the user to assume the system just stopped working altogether.  

The report should show and “Process….” Status screen of some sort at least.

-Tony

bradymiller wrote on Monday, October 10, 2011:

man,
Just one more I promise. I also noted ‘pid’ was indexed in the form_encounter, but we should also have ‘pid’ indexed in all the form_* tables (for example, would speed up searching for vitals via CDR engine).
-brady

tmccormi wrote on Monday, October 10, 2011:

Now some good news …  I added indexes to lists table as ‘pid’ and ‘diagnosis’.   The report (limited at 1000) now completes in 2 minutes.

Now to test the full report and turn logging back on or figure out why that’s crashing.

-Tony

bradymiller wrote on Monday, October 10, 2011:

Hi Tony,

Since the diagnosis column can hold multiple dx, would rec trying to avoid indexing that one. May not be helpful anyways here since a LIKE is used to look up that column (rec checking mysql manual for details on this). Just by doing ‘pid’ and ‘type’ alone you will decrease every query of ‘lists’ needing to check13753 items to only the number that exist per that pid,type (guessing just checking 1-10 or so). This query for dx is done about 100 times or so for each diabetes rule filter (of which there are about 7 or so diabetes rules).  Pretty cool what mysql indexing can do.

-brady

tmccormi wrote on Monday, October 10, 2011:

Tests on the full 4500 are not going as well as I hoped…  still running after 30 minutes…  I was hoping for 10 min.

Brady says :

pid index on form_* tables ….

Vitals would be easy to do as that is a standard (used by all) the rest are almost always replaced by something customized (at least with my customers).

-Tony

bradymiller wrote on Monday, October 10, 2011:

Hi,
To summarize real quick, the following makes real sense:
1. ‘pid’ and ‘type’ indexes on the ‘lists’ table
2. ‘pid’ index in the ‘form_vitals’ table
3. ‘pid’ index in the ‘forms’ table
4. Ensure ‘pid’ index in the ‘form_encounter’ table (should be there already)
5.For the widgets on patient summary screen, consider trying out this patch (from ‘master’) to see if sorts out the ajax call issues and speeds things up(it upgrades patient summary,calender, and left_nav to most recent jquery, so there could be potential other issues): http://github.com/openemr/openemr/commit/a23f3e34e5410f4cdd61f3304b9e0900a8c3e699

-brady

tmccormi wrote on Monday, October 10, 2011:

I have added indexes for PID on form, form_encounter, form_vitals, lists and for grins prescriptions.  I added an index for ‘type’ to lists and dropped the diagnosis index.   Re running the report now.

PS: This command is great for watching to be sure a report/process like this is actually running:

mysqladmin --user=root -pmcmlxi -i 10 processlist | grep SELECT

-Tony
www.mi-squared.com and oemr.org

bradymiller wrote on Monday, October 10, 2011:

for grins,

Could consider (won’t help here likely, but could help in the future as the per patient clinical_rules gets utilized):
‘pid’ indexes in ‘clinical_plans’ and ‘clinical_rules’ tables

Keeping fingers crossed on your current attempt :slight_smile:

-brady

tmccormi wrote on Monday, October 10, 2011:

While the PID index is part of database.sql it was never added to any of the upgrades.   All these new changes to indexes should be included in the upgrade scripts.
-Tony

bradymiller wrote on Monday, October 10, 2011:

Agreed (I assume you are talking about form_encounter),

After looking through database (from current database.sql):
patient_id in immunizations should have a index (this will directly effect CDR queries)
patient_id in procedure_order may help also
Guessing that keying onotes,pnotes, and transactions will be helpful in other places for large databases also.

Confirmed in 2.8.3 following key is missing (now in current database.sql):
pid in form_encounter

(strike my remark above on clinical_rules and clinical_plans index for pid; these already exist)

-brady