V5.0.1(6) slow patient load times on Windows Server

Management and Administration
,
1

Upgraded from 5.0.0 to v5.0.1(6) running on XAMPP v3.2.2 on Windows Server 2016 and noticed in testing that it is loading patients very slowly. It will take 5-7 seconds to load a patient’s dashboards and encounters.

This shouldn’t be a hardware issue as the server VM has 4 Xeon cores and 8GB of ram and the issue only started after the upgrade.

In trouble shooting:

  1. Checked to make sue the sqlconf.php is the same as before.
  2. I took a look at my.ini and it seems fine, though I tried upping the buffer as high as 1GB without any affect on speed.
  3. Tried indexing all of the XAMPP folders in Windows Server.

This question may be semi relevant to the earlier post, but the issue about default buffer in my.ini seems to have been corrected: V5.0.0 too slow on Windows

I looked at the error logs in MySQL and there aren’t any related errors.

Not sure if rellevant, but in the appache logs there is the same set of errors for every patient I load:
\controllers\C_Document.class.php on line 653, referer: http://restore.emr/interface/main/tabs/main.php
[Sat Oct 20 07:37:20.679365 2018] [:error] [pid xxxx:tid xxxx] [client IP ADDRESS] PHP Warning: fopen(C:/xampp/htdocs/openemr/sites/default/documents//): failed to open stream: No such file or directory in C:\xampp\htdocs\openemr\controllers\C_Document.class.php on line 633, referer: http://restore.emr/interface/main/tabs/main.php
[Sat Oct 20 07:37:20.679365 2018] [:error] [pid xxxx:tid xxxx] [client IP ADDRESS] PHP Warning: fpassthru() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\openemr\controllers\C_Document.class.php on line 653, referer: http://restore.emr/interface/main/tabs/main.php

Any suggestions of what to look into would be greatly appreciated.

2

Two quick questions?

  1. What is the CPU load on the system at the time of slow loading?
  2. How is teh IO? is the system thrashing either for data swap or scheduling swap?
    I am hoping at least one must be very high! That is either IO bound or CPU bound.
    -ViSolve OpenEMR support team
3

Hi ViSolve,

Thank you for your reply!

I pulled up the system Task Manager and ran some patient requests in the EMR to see the system load:

  1. Here you can see me logging in and loading 3 patients. It looks like it has a spike for loading the patient, then a spike to load the encounters list tab.

image
image675×587 82.9 KB

  1. Here is the IO info. Nothing crazy high.

image
image681×595 88.2 KB

I am beginning to think that the slowdown may be because the new version is auto-loading the complete encounter lists for patients, which can be several hundred; but would like to rule out other causes if there is something I could accelerate.

4

Thank you.
Though, we can see the spike and I dont think you are CPU bound.
I am guessing you are IO bound but did not see the disk IO acivity here.
You may want to dig some more on that and look for idle cpu time during the load as that
may give a clue if the CPU is waiting for the IO? on a side note, are you running anything else on the system as I see at least 1000 threads and hence the question.

5

The VM is a dedicated server for OpenEMR hosting, all those threads are the windows services running. The physical server hosts other VMs and shares a gigabit connection. I can give the EMR server a dedicated gigabit line to the network and see if that helps. Is the current thought that 5.0.1 loads more data at once than 5.0.0 and is being slowed down by a network bottleneck?

The disk activity doesn’t show up as it is a virtual disk running on top of a virtual harddrive on a RAID controller with an SSD array, I ran a quick benchmark on the VM for you if it helps:

image

7

I don’t think this is unique to your system. I have had the same issue on my windows system. I followed all the suggestions on the previous thread with only marginal improvement.

8

It’s got to be either Windows itself, your browser, Apache and PHP, or MySQL. All of those can be independently observed.

9

At the end of the day I decided to take it off of Windows Server 2016 and migrate to Ubuntu 18.04. I followed this post: https://sourceforge.net/p/openemr/discussion/202505/thread/c5ebd143/ . Now it is loading more than 2x faster with the same hardware configuration. It could be the linux based system performs better with OpenEMR, or it could be the clean installation vs the multiple updates on the previous version has “cleaned” it up somehow. Either way, we’re up and running and staff hasn’t notice any difference besides the faster performance, which they are happy about.

10

Did anyone ever figure out what might be the problem? I’m running in to the same problems with the same error msg.

11

hi, think there might be a related fix in patch 7

do you have any recurrent appointments in your calendar, and what are your admin->global->calendar settings for displaying appointments in the main patient summary screen?

12

I do have recurrent apts. however, that patch was applied to my code. It seems to be most prevalent in the patient searching and demographics where it can take 5 to 9 seconds to load. However, its a couple secs load time on almost everything . Should I make my own thread for this?

13

sure, try changing
Recurrent Appointment Display Widget
and then
Appointments - Patient Summary - Number to Display
to see if behavior changes

14

I have had this problem for years and have noted it getting worse. Given the behavior, I have assumed it was loading all encounters to get to the end of the list to print the correct/current superbill. Right now, we have a patient who has 152 encounters and I think the request is timing out before it can finish because I get a blank pop up every time from multiple accounts and computers. Like most, we use win server 2008r2. I will post more on the problem as I investigate - any thoughts would be appreciated.

15

you could try changing
max_execution_time = 60
to 90 in the xampp php settings file i.e. c:\xampp\php56\php.ini

16

Stephen - you are awesome… but I already set that to 60 (line 372) but when staff will allow me to tinker, I may try 90 since staff was able to print the superbill last week - though they said it took a long time.

I also tried setting the appointment display widget to 5 (from 20) but no change in behavior.

It pulls the encounter info from globals and its pretty straight forward mysql, which concerns me that mysql may be the lagging point…and that puts me at the drowning point of my technical depth.

Also, I copied openemr over to my laptop and the behavior is consistent.

Is there a way to archive records? Like move encounter records older than x to another db or have openemr pull a “working memory” db containing more recent records that the server can quietly and periodically update to the “long term” (present version) db…sorry - speculation beyond the current issue…

I will post more later when I am able to work on it.

17

have you thought about upgrading hardware and migrating to linux?

btw those are nice feature requests

18

I have thought about migrating to Linux but that’s a bigger undertaking than I have time for atm.

I fixed a few undefined errors that show up in the logs but when it comes to just loading the superbill via the pop up the tab it times out with no errors on the patient with large encounter numbers and not with the patient having few encounters. There’s a lot about mysql being slow on windows but every setting I found to check you have already fixed! I ran out of time today to extend the time out beyond a minute but when I check performance during that minute I do not see any extra high load on the disk, memory, or cpu. I read something about COMMIT problems with Windows. I am going to try tracing some code - I found at least one time-consuming nest of ifs…we will see if…

19

assuming you’ve already seen this post

20

lol. Wish I had and I recreated the wheel. I thought I read all of that thread! Brady is awesome!

I read up on a lot. In short, the sample files, eg, my.ini-small/medium/large do not refer to business size but mysql usage, so those were useful. The threads Brady showed in the other post would likely have fixed the problem but I’ll share what I did because staff are really happy today with what I did. Initially, I got the superbill to load in 2.5 minutes and this morning it loaded in 20 seconds, then office staff said it loaded for them in 2 sec.

innodb_buffer_pool_size = 3000M ( or 3G)
Some places say you should set this to 50-80% of your total RAM but if you try to go >=4G then you get an error in mysql_error.log telling you you cannot go over 4G with 32-bit applications. I found I cannot go over 3G without getting that error.

innodb_log_file_size = 1G
I read it was suggested this value be 25% of the buffer pool size, this is 33%

A stack exchange post suggested setting buffer size to 128M from 8M, so:
innodb_log_buffer_size = 128M

And lastly, from the my.ini-large file and some overstack posts:
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M

There were 1-2 other changes I made but I do not think they led to meaningful changes. I will update if anything whacky happens as the day progresses.

Thank you Stephen for your support!

3 Likes
21

Hello,
I have been investigating the lack of speed a lot and I have found that there are missing indexes in the “openemr_postcalendar_events” table.
The query is in /interface/main/calendar/PostCalendar/pnuserapi.php, fuction & postcalendar_userapi_pcQueryEvents (& args)

In my tests this query was made:

SELECT DISTINCT a.pc_eid, a.pc_informant, a.pc_catid, a.pc_title, a.pc_time, a.pc_hometext, a.pc_eventDate,
a.pc_duration, a.pc_endDate, a.pc_startTime, a.pc_recurrtype, a.pc_recurrfreq, a.pc_recurrspec, a.pc_topic,
a.pc_alldayevent, a.pc_location, a.pc_conttel, a.pc_contname, a.pc_contemail, a.pc_website, a.pc_fee, a.pc_sharing,
a.pc_prefcatid, b.pc_catcolor, b.pc_catname, b.pc_catdesc, a.pc_pid, a.pc_apptstatus, a.pc_aid,
concat (u.fname, ‘’, u.lname) as provider_name, concat (pd.lname, ‘,’, pd.fname) as patient_name,
concat (u2.fname, ‘’, u2.lname) as owner_name, DOB as patient_dob, a.pc_facility, pd.pubpid,
ins.title as insurance_name, tg.group_name, tg.group_type, tg.group_status
FROM (openemr_postcalendar_events AS a) LEFT JOIN openemr_postcalendar_categories AS b ON b.pc_catid = a.pc_catid
LEFT JOIN users as or ON a.pc_aid = u.id
LEFT JOIN users as u2 ON a.pc_aid = u2.id
LEFT JOIN patient_data as pd ON a.pc_pid = pd.pid
LEFT JOIN list_options as ins ON pd.iid = ins.option_id and ins.list_id = ‘Secure_Suppliers’
LEFT JOIN therapy_groups as tg ON a.pc_gid = tg.group_id
WHERE a.pc_eventstatus = 1
AND ((a.pc_endDate> = ‘2019-09-17’ AND a.pc_eventDate <= ‘2019-09-17’)
OR (a.pc_endDate = ‘0000-00-00’ AND a.pc_eventDate> = ‘2019-09-17’ AND a.pc_eventDate <= ‘2019-09-17’))
AND a.pc_aid in (0.6)
GROUP BY a.pc_eid
ORDER BY a.pc_startTime ASC, a.pc_catid ASC, a.pc_duration ASC

You can change dates, pd_aid (user id) …

Without index it takes 0.610 seconds, it goes through all the records of the openemr_postcalendar_events table, about 60,000 records.

Adding these indexes:

ALTER TABLE openemr.openemr_postcalendar_events ADD INDEXidx_openemr_postcalendar_events_eventList (pc_eventstatus, pc_endDate, pc_eventDate, pc_aid) USING BTREE;

ALTER TABLE openemr.openemr_postcalendar_events ADD INDEXidx_openemr_postcalendar_events_pc_pid (pc_pid) USING BTREE;

It takes 0.016 seconds to run only 110 records.

Could you try to see if it improves calendar performance?

Could I have lost these indexes?

Thank you

PS: Sorry if it’s not well written, my native language is Spanish