InnoDB vs. MyISAM

OpenEMR Development
81

osverdlov wrote on Sunday, May 29, 2016:

I too have 16.04 . Our problem would be users with old MySQL as well as people use small host providers with limited memory for MySQL.

82

sunsetsystems wrote on Sunday, May 29, 2016:

This certainly underscores the need for more testing of the performance impact of the change to InnoDB, and performance fixes as needed. I’m especially interested in any reports that can run for a long time.

83

bradymiller wrote on Monday, May 30, 2016:

Hi,

In the ongoing pull request, I just placed a commit that basically converts all *TEXT sql columns in the codebase to use NULL as default and also converts them all in the upgrade script via the information_schema:


(this was much simpler in the upgrade script, which basically uses 1 function to do this rather than changing specific tables and columns)

-brady
OpenEMR

84

bradymiller wrote on Monday, May 30, 2016:

Hi,

For both the INNODB and TEXT stuff, there is extensive ongoing and planned testing before this makes it into the codebase; this is being tracked in a comment field near the top of the pull request:

-brady
OpenEMR

85

bradymiller wrote on Sunday, June 05, 2016:

Hi,

The InnoDB conversion and TEXT field standardization is testing well and appears ready for commit into the main codebase. Code can be reviewed here:

Here’s the list out of things that were tested:

  1. (DONE) Optimized install took 3 minutes (prior to optimization, it took greater than 60 minutes).
  2. (DONE) Globals save took 2 minutes; a bit long, but acceptable for now(possible optimization is to combine all the inserts into 1 query).
  3. (DONE) ICD9 import took 1 minute; acceptable.
  4. (DONE) ICD10 import took 2 minutes; acceptable.
  5. (DONE) RxNorm import took about 90 minutes after optimization (note it is 14 million rows of data!); acceptable and added a warning that it can take a lot of time.
  6. (DONE) SNOMED import took 4 minutes; acceptable.
  7. (DONE) RXCUI import took 20 seconds after optimization (note was > 3 hours without optimization); acceptable.
  8. (DONE) Test installing and using all forms in interface/forms; passed.
  9. (DONE) Install CAMOS form takes 2 minutes; acceptable.
    10.(DONE) Test upgrade(both TEXT standardization and INNODB conversions) with all forms and rxnorm/snomed/rxcui/icd9/icd10 installed; it took 13 minutes, which is acceptable.
  10. (DONE) Test CDR reports; passed.
  11. (DONE) Test admin->Layouts save; passed.
  12. (DONE) Issue warning in upgrade script and documentation that when upgrading to 4.3.1 or greater that it can take anywhere from 3 minutes to several hours (and will see a whitescreen until is is completed).

-brady
OpenEMR

86

sunsetsystems wrote on Sunday, June 05, 2016:

I have to disagree about 2 minutes for globals save being acceptable.

What does “install CAMOS form” entail?

Rod
http://www.sunsetsystems.com/

87

sunsetsystems wrote on Sunday, June 05, 2016:

Also have to say I’m surprised by some of these numbers, and fear for how this might affect response times in a busy production environment.

Rod
http://www.sunsetsystems.com/

88

sunsetsystems wrote on Sunday, June 05, 2016:

One more thought. If the engine for all tables is switched back to MyISAM, does this code still work? I’m thinking it probably will, and it will be wise to retain this option in case of performance complaints and to provide a way for users to measure for themselves how performance is affected.

Rod
http://www.sunsetsystems.com/

89

osverdlov wrote on Sunday, June 05, 2016:

Yes the code will still work in MyISAM.

Users will have three options to deal with possible performance loss:

1 move back to MyISAM
2 tune InnoDB parameters in MySQL server
3 report it so we can fix in code.

90

osverdlov wrote on Sunday, June 05, 2016:

This bug affects MyISAM mode as well. That may be due to logging.

91

osverdlov wrote on Monday, June 06, 2016:

Rod, check this PR for faster globals.


For each parameter there were 3 INSERTs .

92

sunsetsystems wrote on Monday, June 06, 2016:

Very nice, thanks! I made a couple of minor code comments. Did not test.

Rod
http://www.sunsetsystems.com/

93

bradymiller wrote on Tuesday, June 07, 2016:

Hi,

To clarify:
1.All testing was done on OS Xubuntu 16.04.

2.“install CAMOS form” is when install the CAMOS form database items within the Adminsitration-Forms GUI.

3.Regarding globals. The data on my Xubuntu 16.04 is the following:
MyISAM globals save was 43 seconds.
InnoDB globals save was 141 seconds (2 minutes 21 seconds).

A very odd thing about the globals save is that it is slow no matter what (MyISAM or InnoDB) when using an OS with a gui(Xubuntu 16.04 and Mint 16 is all I have tested), however is super fast(several seconds at most) when use a non-gui OS(at least MyISAM; I have not tested InnoDB); it’s even more ironic, because I am testing the non-gui OS in a virtual machine that uses a gui OS as the host…(meaning the appliance guest is much faster than the host OS…)
(note that interestingly the install script produces very similar results whereby slow on OS with gui and fast on OS without gui)

As Oleg is working on, globals requires optimization (even with MyISAM). Should we consider this a showstopper(ie. need this fix before proceeding) or would it be acceptable to bring this InnoDB/TEXT code into the codebase at this time?

thanks,
-brady
OpenEMR

94

osverdlov wrote on Tuesday, June 07, 2016:

Fixed. You’re welcome to test.

95

osverdlov wrote on Tuesday, June 07, 2016:

As I mentioned, every time user clicks SAVE in Globals , the system would begin to DELETE/INSERT all the parameters. For each INSERT there were 2 more INSERTs to audit log.

I would not consider it a show stopper.

Check PR 134 for optimized performance of Globals saving.

96

sunsetsystems wrote on Tuesday, June 07, 2016:

Brady,

I expect your performance differences are related to available memory and MySQL server settings. This may be helpful:

Oleg seems to have a fix for globals save performance, so if that tests well then I suggest committing that concurrently with the InnoDB support.

Rod
http://www.sunsetsystems.com/

97

sunsetsystems wrote on Tuesday, June 07, 2016:

Also check out mysqltuner as described here:

https://help.ubuntu.com/lts/serverguide/mysql.html#mysql-advanced

sudo apt-get install mysqltuner
sudo mysqltuner

Rod
http://www.sunsetsystems.com/

98

bradymiller wrote on Thursday, June 09, 2016:

Hi,

Drumroll…

The globals optimization, InnoDB migration, and sql TEXT standardization code has just been added to the codebase. :slight_smile:

Thank you Matrix!

-brady
OpenEMR

99

osverdlov wrote on Thursday, June 09, 2016:

You’re welcome :slight_smile: Thank you for discussions and for the insights and for the help.

Can we assign target release for this feature?

100

bradymiller wrote on Thursday, June 09, 2016:

Hi Oleg,

This will go into the next official release.

Because of the joys of Meaningful Use, we can’t fully predict what the version name of the release will be. It should be 4.3.1 if we get Full Certification for meaningful use, which is highly likely. However, if in the unlikely case we don’t have Full Certification yet at the next release, then it would end up being called version 4.2.3…

-brady
OpenEMR