Migrate all SQL tables from MyISAM to InnoDB

zach2good commented 3 years ago

I affirm:

[x] that I agree to Topaz Next's Limited Contributor License Agreement, as written on this date
[x] that I have read the Contributing Guide and the Code of Conduct
[x] that I've tested my code since the last commit in the PR, and will test after any later commits

Temporary:

[x] that I understand there are large refactoring efforts going on right now, that these efforts touch every single Lua script and binding, and that my pull request might get put on hold to ensure there are not any conflicts with the refactoring work

We were discussing earlier all the things that are historically wrong with the server that nobody has gotten around to fixing. We eventually got onto our DB access patterns and locking, and all of that good stuff. We then looked at the db engines, and what engines are good for what.

After a little digging (and a little advice from Noct Souls and Eden) we decided it's probably best for us to migrate everything to InnoDB, which supports row-level locking, where MyISAM was table-level.

This isn't a silver bullet for any specific problem, but it's good practice and should easy delays a bit.

Also removed ROW_FORMAT=FIXED because it wasn't playing nice with InnoDB

Sources

https://dev.mysql.com/doc/refman/5.7/en/internal-locking.html

char_vars.sql
 ENGINE=MyISAM DEFAULT CHARSET=utf8;

MyISAM is optimized for environments with heavy read operations, and few writes, or none at all. A typical area in which one could prefer MyISAM is data warehouse because it involves queries on very big tables, and the update of such tables is done when the database is not in use (usually by night).

InnoDB
MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications.

I saw somewhere that MyISAM is deprecated, or on the way to being, and InnoDB is the default engine, so it has to be at least acceptable 🤷

zach2good commented 3 years ago

TODO: Go through these points for safety, and do a bunch of testing: https://mariadb.com/kb/en/converting-tables-from-myisam-to-innodb/

zach2good commented 3 years ago

DB Import is painfully slow, gotta work through this: https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html

topaz-next / topaz

Migrate all SQL tables from MyISAM to InnoDB #2564