WeBWorK Main Forum

MyISAM vs. InnoDB

MyISAM vs. InnoDB

by Andras Balogh -
Number of replies: 3

Questions:

1. Does anybody use InnoDB instead of MyISAM with WeBWorK?
2. With no visible initial performance observed between the two could a significant difference show up later with thousands of users and hundreds of courses? 

Background: 

I just installed 2.15 on an ubuntu 20.04.1 on a Microsoft Azur Hyper-V and with both database engines and both with --sync_binlog=0 and 1 the initial OPL-update takes 3 hours. Either there is no significant difference or it is negligible to some other performance issues. 

Loading the OPL Directory in the Library Browser takes about 4 seconds either way, and there are no other issues observed so far.

I hesitate to go back to MyISAM from InnoDB. 

The installation manual mentions "(at least on some hardware) InnoDB seems to be 50-100 times slower than MyISAM". But for example the wikipedia page for MyISAM mentions that "Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency."


In reply to Andras Balogh

Re: MyISAM vs. InnoDB

by Nathan Wallach -
I have not tried InnoDB for course tables, but I think there are some very strong reasons why we should consider making the change:
  1. Data integrity (InnoDB supports transactions; ACID),
  2. Write efficiency (InnoDB does row locking rather than table locking),
  3. support for "foreign keys"
  4. I suspect that using InnoDB + ACID transactions will make "clustering" WeBWorK easier and WW more scalable.

Many sites report that the "speed differences" are not longer as big as they were when InnoDB was a very new technology.

I suspect that some code changes will be needed to take proper advantage of the support for transactions, and certainly to take advantage of "foreign keys".

I'm willing to work with others on looking into this and doing testing. If anything moves ahead, I would recommend the discussion move to GitHub.   https://github.com/openwebwork/webwork2

There was a discussion about this back in 2015:

  • https://github.com/openwebwork/webwork2/pull/591


References:

In reply to Nathan Wallach

Re: MyISAM vs. InnoDB

by Heath Loder -
I am also interested in using InnoDB for course tables. I am willing to offer assistance as well, but my experience with Perl and Github are minimal. If nothing more, I'd like to at least make it known that there are other users that are interested in this. I didn't want to start a discussion at GitHub yet because I'm not sure I'm the best representative to start the discussion, especially after seeing the plethora of resources that you have recently combed through that can justify the reasonings better than I can.
In reply to Heath Loder

Re: MyISAM vs. InnoDB

by Arnold Pizer -
When InnoDB became the default MySQL engine about 10 years ago I soon saw that OPL_update was taking 50-100 times longer than before (on my hardware) so I switched the default engine to MyISAM in my install instructions. Since then people have specified MyISAM for virtually all WeBWorK database tables (actually I believe all except the OPL_global_statistics table). This is now done in two places in the config files, one for course database tables and the other for the OPL tables.

Recently I tried running OPL_update on a virtual server with the latest version of MySQL (8.0.23), Ubuntu 20.04, WeBWorK 2.15, etc. using both MyISAM and InnoDB. Using the MyISAM engine, it took 3 hr and 7 min. Using the InnoDB engine, it took 1 hr and 3 min. Not surprisingly, things have changed a lot in ten years.

I think we should give serious thought to making InnoDB the default engine.

Arnie