MySQL Queries slow after 4.1.2(7) -> 4.2.0 update

system · February 10, 2015, 2:39pm

epicstar wrote on Tuesday, February 10, 2015:

OS: Ubuntu 14.04.1 (updated from 12.04 before running the deb package to update to 4.2.0)

Anybody else experiencing some decent slowness with mysql queries after updating? I’m good at Unix stuff but I don’t have much experience with MySQL and how the updates affects the tables from 4.1.2 -> 4.2.0. What info do you need here in order to get the info you guys need to help me troubleshoot this?

system · February 10, 2015, 3:12pm

fsgl wrote on Tuesday, February 10, 2015:

Good to hear that another user was able to upgrade their Ubuntu Package.

There have been other glitches, unrelated to MySQl query speed, reported with 4.2.0.

Try Rod’s suggestion to see if of help.

These parameters are quite different from those in the Wiki.

system · February 11, 2015, 2:31pm

epicstar wrote on Wednesday, February 11, 2015:

I just changed. I’ll see what the users will say today…

EDIT: It’s way less sluggish now, but it’s still slower than the update (it’s definitely DB calls). Maybe it’s a mysql setting that changed…

system · February 11, 2015, 8:38pm

fsgl wrote on Wednesday, February 11, 2015:

Since 4.2.0 has a whole bunch of stuff added on for MU2 (which is not completed yet), one can expect that more resources are required.

Hopefully with Rod’s recommendation it would be enough so that the clients can work with a minimum of fuss.

system · February 12, 2015, 4:02pm

epicstar wrote on Thursday, February 12, 2015:

ah ic… Rod’s recommendations did not actually help the problem and the employees are furious at me for even attempting to update. I’m just going to take the slack but I really want to get this fixed. But honestly I don’t know where to start…

system · February 12, 2015, 5:11pm

epicstar wrote on Thursday, February 12, 2015:

I’m thinking that it’s possible that the audit log encryption is contributing to this slowdown.

system · February 12, 2015, 6:06pm

fsgl wrote on Thursday, February 12, 2015:

Sorry to hear that people are so impatient & short-tempered.

I would suggest that you revert back to 4.1.2(7) to mollify the mob for now.

Get a backup of 4.2.0 before the reversion on your own server for diagnostic purposes.

What’s in the error logs?

If Brady or one of the developers don’t respond in a few days, we can direct his attention to this post via email.

system · February 12, 2015, 7:23pm

epicstar wrote on Thursday, February 12, 2015:

You’re using Ubuntu though correct? If so, you had an update to 14.04.1 right? Just wondering…

system · February 12, 2015, 9:21pm

cmswest wrote on Thursday, February 12, 2015:

What does the output of this entered at the command line show?

mysqladmin -u root -p status

Also, do you have anything in your syslog, (/var/log/syslog) that relates to mysql?

system · February 12, 2015, 9:43pm

epicstar wrote on Thursday, February 12, 2015:

it seems my root password is somehow incorrect. I’m not sure why… but I’ll fiddle with it later. Also, looking at the syslog, there are some things in the syslog for mysql. There were some “terminated” messages with MYSQL but that was most likely me restarting the service. Here are most of the messages though:

Feb 12 16:29:39 kuan-System-Product-Name /etc/mysql/debian-start[2207]: Upgrading MySQL tables if necessary.
Feb 12 16:29:40 kuan-System-Product-Name NetworkManager[1284]: <info> WiFi hardware radio set enabled
Feb 12 16:29:40 kuan-System-Product-Name acpid: client connected from 2189[0:0]
Feb 12 16:29:40 kuan-System-Product-Name acpid: 1 client rule loaded
Feb 12 16:29:40 kuan-System-Product-Name /etc/mysql/debian-start[2211]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Feb 12 16:29:40 kuan-System-Product-Name /etc/mysql/debian-start[2211]: Looking for 'mysql' as: /usr/bin/mysql
Feb 12 16:29:40 kuan-System-Product-Name /etc/mysql/debian-start[2211]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Feb 12 16:29:40 kuan-System-Product-Name /etc/mysql/debian-start[2211]: This installation of MySQL is already upgraded to 5.5.41, use --force if you still need to run mysql_upgrade
Feb 12 16:29:40 kuan-System-Product-Name /etc/mysql/debian-start[2226]: Checking for insecure root accounts.
Feb 12 16:29:40 kuan-System-Product-Name /etc/mysql/debian-start[2231]: Triggering myisam-recover for all MyISAM tables

system · February 12, 2015, 10:51pm

fsgl wrote on Thursday, February 12, 2015:

Running Linux Mint 17 on test computer, so it almost does not count. Upgrade was flawless, but then only 1 fake patient in that copy.

Production on Windows 7. Had two problems unrelated to your own since upgrade to 4.2.0.

When Ubuntu went from 13 to 14, Canonical relocated the web directory to var/www/html resulting in great consternation for the developers.

The upgrade to 4.2.0 should work regardless of the version of Ubuntu. Brady will correct, if this is inaccurate.

system · February 13, 2015, 2:56am

cmswest wrote on Friday, February 13, 2015:

Maybe you’re using your sudo password and not your mysql root password?

Do you also see InnoDB entries in syslog?

system · February 13, 2015, 2:56am

cmswest wrote on Friday, February 13, 2015:

Did you enable the audit log encryption under admin->globals->logging?

system · February 13, 2015, 4:21am

bradymiller wrote on Friday, February 13, 2015:

correct, the upgrade for ubuntu package should work regardless of ubuntu version.
-brady

system · February 13, 2015, 4:30am

bradymiller wrote on Friday, February 13, 2015:

Hi,

Are there any specific areas in OpenEMR that are slower? What are your server specs? How many patients are in the clinic? Are there any other processes running on your server taking up cpu/memory (ie. ‘top’ command)(for example, if your using swap memory then things will slow down severely)? Are they running any AMC/CQM/Standard Clinical Rules reports during clinic hours?

As I recall the only real addition to the overhead of mysql queries in 4.2.0 is the log encryption, which by default is turned off and even if turned on only should affect insert and update queries. To troubleshoot you could turn this off/on in addition to the actual log engine (in same globals section) to see if any difference in performance.

-brady
OpenEMR

system · February 13, 2015, 4:23pm

epicstar wrote on Friday, February 13, 2015:

No… I’m definitely using my mysql root password. I’ll check for InnoDB entries when I have the time.

system · February 13, 2015, 4:48pm

epicstar wrote on Friday, February 13, 2015:

We’re not doing the AMC/CQM/SCR rules during clinical hours (… sorry for my noobiness but what’s the point of doing that besides for reporting meaningful use…). The only thing I can see that’s different is that Ubuntu 14.04.1 is using MySQL 5.5-41 whereas Ubuntu 12.04 had an obviously different previous version (not sure which). I’ve been using top to look at running processes and as far as I can tell, nothing… 0 GBs are being used in Swap. When I changed mysql settings per percora recommendations though, the CPU usage of mysqld shoots up dramatically which is good bc previously it wouldn’t shoot up too much. No multiple sites either… just one practice.

Here are the server specs (I made it myself):

Intel Xeon E3-1245 (Sandy Bridge 3.3 GHz)

ASUS P8B

2x Seagate Barracuda ST31000524AS 1TB 7200 RPM (RAID-1 under the motherboard)
2x Kingston 4GB DDR3 1333 (8GB total)
Antec EarthWatts 500W
Dell Sonicwall Firewall

We started using OpenEMR at 4.0 and started on the Ubuntu LTS before 12.04.

The only thing I can see being the hardware bottlenecks are the motherboard RAID and HDDs. Perhaps one hard drive maybe went out or perhaps the motherboard is making its emulated RAID Controller relearn? But does emulated RAID do the relearn cycle? I will have to benchmark my read speeds on the hard drives though to get a sense of what is going on but like I said, the slowness has happened after I upgraded to 14.04.1. Perhaps though maybe the motherboard is overdue for a BIOS update (it’s in UEFI btw). Or maybe perhaps I should just look into replacing the hard drives in RAID 1 with Samsung 850 EVOs (1 TB).

As for where the slowness is coming from the program:

It takes time to get a patient by PID (if there is a lot of data on the patient, it can take up to 30 seconds to a minute, less if the patient is newish)

Medication retrieval is slow (we use the MI2/ZHhealthcare erx) which ranges from 1-3 minutes depending on the amount of medications - this is where the employees are trying to kill me since the report history is only loaded after this (hint… percora recommendations sped this up to 1-3 minutes!)
patient notes takes 2 minutes
Vitals take 3-5 minutes
Clinical Decision Module will take 3-5 minutes to load (usually clinical reminders and vitals finish at the same time)

We don’t use the fee sheets though since the person that bills refuses to learn the fee sheet/electronic submissions after multiple attempts to get him to do it… which really hurt us when the PQRS deadline hit (we’re taking that penalty T_T).

I remember we had to erase things in the compiled folder of openemr, but I haven’t heard instructions for that since patch 6 of 4.1.2. I ctrl-c’d the openemr package in the middle of the installation since I thought it wasn’t doing anything. So it was actually the 2nd time that it fully installed (it wasn’t printing any progress but I should’ve top’d while it was installing). I remember 14.04.1 was complaining about the 4.1.2-2.deb package (it crashed the updater until I used the 4.2.0 debian package). After the updates though I notice we have an extra 200 GB on the drive that wasn’t there before. Maybe the deb package gave out identical tables and columns in mysql from the two dumps? I highly doubt it’s possible…

I’ll have to see what’s going on with my mysql root password though. I save my passwords in Keepass… Honestly I’m not too happy that Oracle took on MySQL though.

Also… the globals don’t stick for the Audit Logging Encryption, so I assume it is always enabling encryption…

system · February 13, 2015, 7:16pm

epicstar wrote on Friday, February 13, 2015:

Playing around with openemr/phpmyadmin right now, and I’m getting really (and I mean realllyyy) fast query calls doing normal SELECT WHERE statements. At this point maybe I’m probably wrong in thinking that this is a problem of mysql and perhaps it’s most likely not going to be MySQL but something that links MySQL with the OpenEMR GUI. I would think the next problem would be mysqli, but phpmyadmin uses that… Then again, I haven’t used views or copied the statements from the php code itself in OpenEMR.

I guess my next step would be to reinstall the openemr module… I can just rerun the deb package correct? This weekend though I’ll have to find if there are error logs with the OpenEMR scripts though…

system · February 15, 2015, 10:47am

bradymiller wrote on Sunday, February 15, 2015:

Hi,

I wouldn’t rerun the deb package (it is only meant to install/upgrade and can not reinstall/downgrade). And note that removing it will delete the database and all the OpenEMR data. It’s likely not a debian/ubuntu package issue, but we can check the logs to ensure that. Paste/attach the file in /etc/openemr/ and /var/log/openemr/ and can take a look to ensure nothing strange happened.