bradymiller wrote on Thursday, September 29, 2011:
Hi,
Would be interesting to see what happens if upgrade the jquery from 1.3.2 on the patient summary page to 1.6.4 to see if this improves performance of the ajax request (especially the issue of them seeming to all finish at the same time). Reading online, it sounds like jquery’s management of ajax calls has improved drastically since its older 1.3 version.
-brady
rpl121 wrote on Thursday, September 29, 2011:
More detective work on the remedies for slowness:
1. I set vmstat from Linux command line to run every 3 seconds for a few minutes while I was using openemr. This keeps track of whether swap memory is being used. I found that memory was not swapping to virtual memory in the swap partition.
2. I changed the clinical rules for diagnoses on the diabetes and hypertension sections to use only the very few typical diagnosis codes we actually use. I also deleted all but the exact match for the pneumococcal and influenza vaccine codes we actually use. With this, performance is still slow, but not terrible.
3. I set one of the Internet Explorer browsers to display a notification about every script error. Here is a typical display I get:
Webpage error details
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)
Timestamp: Thu, 29 Sep 2011 12:51:12 UTC
Message: ‘length’ is null or not an object
Line: 173
Char: 19
Code: 0
URI: http://192.168.1.5/openemr/interface/main/left_nav.php
I am not sure if this is significant, but who knows?
Ronald Leemhuis
bradymiller wrote on Monday, October 10, 2011:
Hi,
Check out the following wiki page section for mysql indexes to add. (There are about 10 of them to add and these appear to drastically speed things up; note that for upgraders to 4.0 to ensure you add the one in form_encounters also):
http://www.open-emr.org/wiki/index.php/CDR_Performace#MySQL_Optimization
I’m also very interested to hear if anybody wants to test the patch for the jquery upgrade, which is described here:
http://www.open-emr.org/wiki/index.php/CDR_Performace#Jquery_Optimization
-brady
tsvas wrote on Tuesday, October 11, 2011:
Brady, thanks for update. Added these 10 indexes to database. Now response is very fast.
Next… we will test with jquery-1.6.4.
rpl121 wrote on Tuesday, October 11, 2011:
I also added these indexes to the listed tables, and the response is much faster now.
Ronald Leemhuis MD
juggernautsei wrote on Tuesday, October 11, 2011:
Could you pleaes post the syntax for adding these indexes to the MySQL?
Thanks!
Sherwin
juggernautsei wrote on Tuesday, October 11, 2011:
Belay my last, too early in the morning. I found how to add the indexes.
Sherwin
cverk wrote on Wednesday, October 12, 2011:
I am not quite sure how to add indexes to tables? But I am interested in getting the spinning circles to go away. If this works in testing is it possibly going to be part of a patch?
bradymiller wrote on Wednesday, October 12, 2011:
Hi,
Up until now, the standard patches have not been able to modify the database. I think it’s time to change this for several reasons:
1. Have a mechanism for laypeople to add these keys
2. Allow addition of new rows/tables to OpenEMR 4.1 (of course, after good testing) since the Certification is for this version (I don’t know this, perhaps somebody can weigh in, but will version 4.2 in the future need to be recertified?)
In order to get the new keys in, another function needs to be built into the sql_upgrade.php script:
1. Check if a key exist
2. If key doesn’t exist, then add it
Then when have this mechanism, can make a script that can be used in a patch mechanism to deal with database upgrades(the first being the addition of the keys).
Any developers want to to take a stab at adding the mechanism to the sql_upgrade.php script? Guessing it will involve utilization of the mysql SHOW INDEX and CREATE INDEX commands.
-brady
bradymiller wrote on Saturday, October 15, 2011:
Hi cverk (and everybody),
The next patch will add these new keys/indexes (should go out sometime over the next week). For details on this, see this thread:
http://sourceforge.net/projects/openemr/forums/forum/202506/topic/4759848
-brady
rpl121 wrote on Tuesday, October 18, 2011:
Adding the ten file indexes sped up things considerably, but it was still a problem so we got a new $1300 server which has 8 MB memory and a really fast CPU. We got one with a heavy duty battery backup system and four hard drives. (We put the first three into a RAID configuration and saved the last one for conventional use.) Now things are so fast we don’t notice any delay at all.
As a matter of practicality, I don’t think the memory was a problem, as I was able to show before that we were not swapping to and from disk. Also, the RAID drive is just icing on the cake - the real difference was in picking a CPU of 2-4 GHz rather than 300 MHz or so.
Ronald Leemhuis MD
yehster wrote on Wednesday, October 19, 2011:
MySQL by default may not be configured to optimally use your available memory. Although it wasn’t swapping, it might be hitting the hard disk to read data when it could just read from memory instead.
I think you are right that the memory wasn’t the fundamental problem, but I think you may be able to better take advantage of the memory you’ve got.
juggernautsei wrote on Sunday, December 18, 2011:
Hi,
Every since I upgraded my people to 4.0. The calendar has gotten slower and slower. I have read through this post and done everthing in this posting and then some except the firebug. I have not understood how to install it yet. I have setup the eaccelorator for PHP and indexed all the tables and installed the latest patch 5 and updated the database. It still takes upto a full 20 - 30 seconds to load the calendar at every change. I am getting screamed at to make something happen faster. I don’t know what to do. I have 4GB of RAM in the system and 90GB of free space on the hard drive. It is a windows system running server 2003. I am perplexed as to make it go any faster.
Any suggestions would be nice.
Sherwin
juggernautsei wrote on Sunday, December 18, 2011:
PS
It is just a webserver. it does nothing else but run OpenEMR.
Sherwin
yehster wrote on Sunday, December 18, 2011:
Sherwin,
I suspect the bottleneck is the size of your openemr_postcalendar_* tables. A stop gap may be to delete historical records. However, given that amount of time I’ll bet the database is table scanning rather than using the indices.
Best way to figure out the exact problem is to profile the code as I’ve mentioned in a couple of other threads. In addition if you can look at the query plans for the calendar activity, you can see if it is indeed table scanning as I suspect, or if it’s something else.
-Kevin Yeh
kevin.y@integralemr.com
juggernautsei wrote on Monday, December 19, 2011:
Kevin,
thanks for the reply.
I am trying to grasp what you are saying and get onboard. The openemr_postcalendar_events table has 6,588 records in it. Is this what you suspect in slowing down the loading of the calendar? The rest of the postcalendar table have nothing in them. So, this one alone has the most data.
You mentioned “profile the code”.
Could you point me to the thread where this is discussed? Is that what FireBug is used for?
Should I delete the records that are over a year old in the postcalendar table?
Sherwin
yehster wrote on Monday, December 19, 2011:
https://sourceforge.net/projects/openemr/forums/forum/202506/topic/4884045
https://sourceforge.net/projects/openemr/forums/forum/202506/topic/4880879
Hmm… 6000 entries doesn’t really seem like that many, but if my suspicion is correct that the database is table scanning instead of using a good index, then the performance is going to be related to the size of that table, so the more rows you can delete, the faster I think it will run.
Firebug can give you info on the “browser side” about how long things take. Profiling with xdebug breaks down the code running on the server side and shows you how long each operation takes.
juggernautsei wrote on Monday, December 19, 2011:
Hi Kevin,
I click the little check box in PHP myadmin that says profiling and got this information.
Status Time
starting 0.000075
Opening tables 0.000670
System lock 0.000006
Table lock 0.000015
init 0.000087
optimizing 0.000008
statistics 0.000022
preparing 0.000019
executing 0.000005
Sending data 0.002746
end 0.000012
query end 0.000006
freeing items 0.000084
logging slow query 0.000005
cleaning up 0.000009
Does any of this mean anything to you?
Sherwin
yehster wrote on Monday, December 19, 2011:
Sherwin,
I never use phpMyAdmin.
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
Using the slow query log might help you too though.
juggernautsei wrote on Monday, December 26, 2011:
Hi,
I took some time to work on this issue again. I finally got firebug loaded to FireFox and ran the profiler on loading the patients demographics page and got this report
.
POST https://web.openmedpractice.com/…/interface/patient_file/summary/stats.php 200 OK 853ms
GET https://web.openmedpractice.com/…./interface/patient_file/summary/pnotes_fragment.php 200 OK 2.71s
GET https://web.openmedpractice.com/…./interface/patient_file/summary/disc_fragment.php 200 OK 2.77s
POST https://web.openmedpractice.com/……/interface/patient_file/summary/clinical_reminders_fragment.php 200 OK 2.6s
GET https://web.openmedpractice.com/……/interface/patient_file/summary/patient_reminders_fragment.php 200 OK 2.93s
GET https://web.openmedpractice.com/……/interface/patient_file/summary/vitals_fragment.php 200 OK 3.01s
I got this information when I profieled the click on the calendar. I ordered it from the longest time to the shortest.
nodeName 319 1.36% 0.573ms 38.611ms 0.121ms 0.037ms 24.841ms jquery….min.js (line 20)
ready 2 0.08% 0.033ms 25.01ms 12.505ms 0.004ms 25.006ms jquery….min.js (line 28)
ready 1 0.02% 0.01ms 24.827ms 24.827ms 24.827ms 24.827ms jquery….min.js (line 28)
ready 1 0.02% 0.009ms 24.817ms 24.817ms 24.817ms 24.817ms jquery….min.js (line 28)
size 17 2.76% 1.163ms 16.641ms 0.979ms 0.085ms 3.857ms jquery….min.js (line 14)
trigger 15 0.22% 0.094ms 16.052ms 1.07ms 0.09ms 3.864ms jquery….min.js (line 27)
ready 14 0.23% 0.097ms 15.976ms 1.141ms 0.096ms 3.871ms jquery….min.js (line 28)
trigger 292 4.35% 1.828ms 14.667ms 0.05ms 0.03ms 0.362ms jquery….min.js (line 27)
jQuery 1 1.7% 0.717ms 14.481ms 14.481ms 14.481ms 14.481ms jquery….min.js (line 12)
unique 294 13.5% 5.68ms 13.063ms 0.044ms 0.025ms 0.214ms jquery….min.js (line 26)
unique 9 2.64% 1.11ms 12.221ms 1.358ms 1.285ms 1.738ms jquery….min.js (line 26)
unique 8 0.56% 0.236ms 10.291ms 1.286ms 0.842ms 1.792ms jquery.js (line 24)
trigger 8 1.45% 0.612ms 10.055ms 1.257ms 0.832ms 1.758ms jquery.js (line 27)
jQuery 37 0.4% 0.167ms 8.933ms 0.241ms 0.01ms 2.707ms jquery….min.js (line 12)
jQuery 37 2.39% 1.006ms 8.766ms 0.237ms 0.004ms 2.7ms jquery….min.js (line 12)
size 9 0.28% 0.119ms 7.71ms 0.857ms 0.267ms 2.684ms jquery….min.js (line 14)
unique 9 0.28% 0.118ms 7.14ms 0.793ms 0.232ms 2.613ms jquery….min.js (line 26)
size 9 0.14% 0.06ms 7.022ms 0.78ms 0.223ms 2.597ms jquery….min.js (line 14)
unique 9 1.1% 0.461ms 6.962ms 0.774ms 0.218ms 2.591ms jquery….min.js (line 26)
trigger 8 0.45% 0.189ms 6.255ms 0.782ms 0.509ms 1.16ms jquery.js (line 27)
extend 8 14.42% 6.066ms 6.066ms 0.758ms 0.5ms 1.134ms jquery.js (line 18)
inArray 18 10.31% 4.335ms 4.335ms 0.241ms 0.003ms 2.258ms jquery….min.js (line 25)
nodeName 793 9.58% 4.028ms 4.028ms 0.005ms 0.003ms 0.05ms jquery….min.js (line 20)
unique 294 8.15% 3.43ms 3.43ms 0.012ms 0.003ms 0.042ms jquery….min.js (line 26)
unique 3 0.29% 0.124ms 3.318ms 1.106ms 0.337ms 1.866ms jquery….min.js (line 26)
trigger 3 0.52% 0.218ms 3.194ms 1.065ms 0.306ms 1.803ms jquery….min.js (line 27)
extend 13 5.83% 2.451ms 2.451ms 0.189ms 0.014ms 0.976ms jquery….min.js (line 19)
unique 9 4.92% 2.07ms 2.07ms 0.23ms 0.071ms 0.448ms jquery….min.js (line 26)
trigger 4 0.36% 0.152ms 2.057ms 0.514ms 0.048ms 1.033ms jquery….min.js (line 27)
unique 8 0.19% 0.082ms 2.027ms 0.253ms 0.192ms 0.37ms jquery.js (line 24)
size 8 0.16% 0.068ms 1.945ms 0.243ms 0.185ms 0.357ms jquery.js (line 14)
setupDirectTime 1 0.04% 0.018ms 1.926ms 1.926ms 1.926ms 1.926ms
calend…lect.js (line 70)
nodeName 12 0.31% 0.132ms 1.877ms 0.156ms 0ms 0.345ms jquery.js (line 19)
unique 8 0.53% 0.222ms 1.745ms 0.218ms 0.165ms 0.318ms jquery.js (line 24)
(?) 4 0.29% 0.124ms 1.67ms 0.418ms 0.286ms 0.638ms encounters.php (line 243)
(?) 4 0.2% 0.083ms 1.368ms 0.342ms 0.253ms 0.49ms encounters.php (line 244)
jQuery 16 0.91% 0.381ms 0.804ms 0.05ms 0ms 0.383ms jquery.js (line 12)
unique 2 0.23% 0.097ms 0.788ms 0.394ms 0.2ms 0.588ms jquery….min.js (line 26)
prop 4 0.94% 0.395ms 0.706ms 0.177ms 0.135ms 0.261ms jquery.js (line 21)
(?) 1 0.07% 0.028ms 0.645ms 0.645ms 0.645ms 0.645ms index…._topic= (line 546)
prop 4 0.37% 0.154ms 0.62ms 0.155ms 0.117ms 0.206ms jquery.js (line 21)
unique 2 0.11% 0.046ms 0.62ms 0.31ms 0.167ms 0.453ms jquery….min.js (line 26)
prop 4 0.93% 0.391ms 0.466ms 0.116ms 0.09ms 0.168ms jquery.js (line 21)
size 9 0.5% 0.209ms 0.451ms 0.05ms 0.023ms 0.104ms jquery….min.js (line 14)
prop 24 0.79% 0.333ms 0.425ms 0.018ms 0.008ms 0.036ms jquery.js (line 21)
jQuery 8 0.68% 0.287ms 0.423ms 0.053ms 0.023ms 0.112ms jquery.js (line 12)
prop 1 0.48% 0.201ms 0.381ms 0.381ms 0.381ms 0.381ms jquery….min.js (line 22)
unique 4 0.16% 0.068ms 0.311ms 0.078ms 0.056ms 0.12ms jquery.js (line 24)
unique 2 0.11% 0.046ms 0.266ms 0.133ms 0.09ms 0.176ms jquery….min.js (line 26)
prop 12 0.21% 0.09ms 0.243ms 0.02ms 0.013ms 0.034ms jquery.js (line 21)
(?) 1 0.04% 0.018ms 0.243ms 0.243ms 0.243ms 0.243ms index…._topic= (line 545)
size 10 0.52% 0.217ms 0.217ms 0.022ms 0.006ms 0.045ms jquery….min.js (line 14)
unique 1 0.1% 0.042ms 0.18ms 0.18ms 0.18ms 0.18ms jquery….min.js (line 26)
prop 7 0.3% 0.127ms 0.171ms 0.024ms 0.014ms 0.042ms jquery….min.js (line 22)
trigger 1 0.02% 0.008ms 0.165ms 0.165ms 0.165ms 0.165ms jquery….min.js (line 27)
trigger 1 0.03% 0.012ms 0.155ms 0.155ms 0.155ms 0.155ms jquery….min.js (line 27)
nodeName 16 0.36% 0.15ms 0.15ms 0.009ms 0.004ms 0.022ms jquery.js (line 19)
bindReady 1 0.1% 0.044ms 0.143ms 0.143ms 0.143ms 0.143ms jquery….min.js (line 28)
prop 4 0.12% 0.049ms 0.138ms 0.035ms 0.031ms 0.042ms jquery….min.js (line 22)
trigger 1 0.03% 0.011ms 0.136ms 0.136ms 0.136ms 0.136ms jquery….min.js (line 27)
trigger 1 0.02% 0.009ms 0.126ms 0.126ms 0.126ms 0.126ms jquery….min.js (line 27)
prop 1 0.05% 0.022ms 0.125ms 0.125ms 0.125ms 0.125ms jquery….min.js (line 22)
trigger 1 0.11% 0.045ms 0.117ms 0.117ms 0.117ms 0.117ms jquery….min.js (line 27)
ready 20 0.24% 0.103ms 0.103ms 0.005ms 0.004ms 0.01ms jquery….min.js (line 28)
prop 1 0.21% 0.089ms 0.103ms 0.103ms 0.103ms 0.103ms jquery….min.js (line 22)
inArray 24 0.22% 0.092ms 0.092ms 0.004ms 0.002ms 0.007ms jquery.js (line 23)
extend 12 0.19% 0.081ms 0.081ms 0.007ms 0.003ms 0.037ms jquery….min.js (line 19)
size 8 0.19% 0.079ms 0.079ms 0.01ms 0.004ms 0.014ms jquery.js (line 14)
extend 8 0.14% 0.057ms 0.057ms 0.007ms 0.004ms 0.01ms jquery.js (line 18)
attr 11 0.12% 0.05ms 0.05ms 0.005ms 0.004ms 0.008ms jquery….min.js (line 24)
inArray 7 0.1% 0.044ms 0.044ms 0.006ms 0.003ms 0.01ms jquery….min.js (line 25)
ready 6 0.08% 0.035ms 0.035ms 0.006ms 0.005ms 0.008ms jquery….min.js (line 28)
unique 6 0.07% 0.029ms 0.029ms 0.005ms 0.004ms 0.007ms jquery….min.js (line 26)
unique 5 0.05% 0.021ms 0.021ms 0.004ms 0.003ms 0.007ms jquery….min.js (line 26)
unique 2 0.04% 0.015ms 0.015ms 0.008ms 0.005ms 0.01ms jquery….min.js (line 26)
attr 1 0.01% 0.004ms 0.004ms 0.004ms 0.004ms 0.004ms jquery….min.js (line 24)
How can I use this to figure out why the calendar is loading so slow?
Sherwin
openmedpractice.com