I could give you guys statistics on my database size and stuff, but I actually fixed the problem. I had to use iotop and loaded a patient realtime. The problem wasn’t on your guys’ ends but on my server configuration instead. I found out that jdb2 was running 99% of the IO while fetching the mysql data (mysqld was running at 4%). The problem is that I have my db on EXT4, and mysql on EXT4 is a huge no-no.
Get a separate SSD (or 2 for RAID 1) just for the MySQL db
As a result, in order for full resiliency to happen, I’ll just have to wait until I can do that.
It begs the question of what type of partition I should move the mysql db to when I get the new hardware. I suggest in a wiki to say that ext4 is not recommended for the mysql db (and suggest the appropriate partition types and hardware for a real server).
This is good information to know. I am still curious how big the database is (ie. how many patients). Also still thrown off how it was not an issue in your prior OpenEMR/ubuntu version; for example, is a newer mysql version bug/issue exacerbating this issue?
Here’s an older article discussing this and comparing ext4 to other file systems: http://themech.net/2011/10/struggling-with-mysql-performance-problems-under-linux/
(appears ext3 was recommended and appears best to place mysql database on separate mount with ext3 and keep the other stuff in the more modern ext4)
This is just one article and would be best to do more research on current articles etc. Thoughts?
Changing file systems, mounting etc. are out of the realm of most basic users trying the software out, but definitely agree this information should be on the OpenEMR wiki. We could make a separate wiki page “Optimizing Performance”(or something like that) which is linked in all the install/upgrade wiki instructions (akin to how the Security page is linked to them all).
Also still thrown off how it was not an issue in your prior OpenEMR/ubuntu version; for example, is a newer mysql version bug/issue exacerbating this issue?
I’d think it could be that 12.04 → 14.04 changed the mysql version (between here is when Oracle started maintaining MySQL!!!). Another one could be the default Linux kernel (3.13.x in ubuntu 14.04 and linux mint 17.x) isn’t coping well with the Intel virtual raid from the motherboard. Other than that, the only other reason I can see a problem is that one of the hard drives just failed during the update (highly… unlikely).
From what I do know is that when Oracle was rumored to maintain MySQL, people decided to move to other dbs like PostGres, etc.
I’ll give you guys statistics at the weekend since I’d have to remote into it (usually I don’t look at the servers unless it’s urgent).
On the note of hardware, I wouldn’t recommend separating the partitions on a hard drive since space is very important in the field like OpenEMR. I’d suggest a hard drive for the OS and another for hardware entity for the SQL DB. Of course though… SSDs are the best and I’d think straying away from XAMPP would be best if you’re doing some optimization.