Changing from MyISAM to InnoDB as default database engine?

[taniwallach]

There have been some posts/discussions in the past about changing the database storage engine used by WeBWorK from MyISAM to InnoDB. Some were by email.

Based on a post from @apizer in the forums (https://webwork.maa.org/moodle/mod/forum/discuss.php?d=5001) the decision to use MyISAM by default was made many years ago, as at the time OPL_update was extremely slow on InnoDB, but that is no longer the case as of tests he did in 2021. An older thread where the idea of moving to InnoDB was raised is at https://webwork.maa.org/moodle/mod/forum/discuss.php?d=3619

Even without changing the webwork code to use transactions and sticking with AutoCommit being on, it seems that there should be some benefit to the use of row locking instead of table locking, and the better data integrity support of InnoDB.

If there is no significant reduction in the performance of the typical database operations, trying out InnoDB on some production servers seems to be something that is reasonable to try. However, I am not aware of anyone who has posted any results of testing of the performance of WeBWorK with the different database engines.

I think that it might help to try to create some sort of script which can profile to some extent the performance of a sequence of database dependent operations and run it using both engines on machines which are not busy from other load.

Here is a rough idea or a sequences of operations to run:

1. Create a new course.
2. Import a classlist file with say 500 demo users (or run a loop to create 500 users).
3. Import say 10 set definition files into the course and assign all those assignments to all users.
4. "Score" all the assignments into a CSV file.

Does this seem to be a reasonable "benchmark" for the more database intensive operations inside courses?

@drgrice1 @dlglin @pstaabp @mgage @Alex-Jordan @drdrew42 Is one of you familiar enough with how to script actions like this and willing to create a script to do all this and time the steps and the total time used?