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?
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
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.
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.
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?
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.
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
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
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.
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
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.
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).
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
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:
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
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
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)