Change table engine for database
Created on November 16, 2017. Copied from redmine (https://bugs.ligo.org/redmine/issues/5984)
Starting this issue just to have a place to record some research, notes, etc. on our database configuration. Currently, we use MySQL (effectively; moving to MariaDB shortly, in Debian 9) and we use MyISAM as a table engine. My understanding is that MyISAM is antiquated and really only around for legacy support. Note that MariaDB offers an updated alternative to MyISAM called Aria. But most things I've read seem to indicate that InnoDB is highly preferred and is a good general-purpose engine. The main issues I see with MyISAM is that it doesn't have transactions (I would think that is very important) and it technically doesn't have foreign keys, but Django seems to handle that sufficiently well on its own.
However, I note that Branson specifically switched from InnoDB to MyISAM in the following commit from 2014:
commit 2d4b3c483a4e40af8ff3ef3336bd9272b28ef01a
Author: Branson Stephens <branson.stephens@ligo.org>
Date: Sat Sep 27 16:10:37 2014 -0500
Committed changes to avoid new tables using INNODB and old ones using MYISAM. This leads to fk constraint errors.
So do we really want to go to InnoDB? I ran a few tests and noted that just running the initial migrations for a new 'gracedb' database took ~ 2 mins with InnoDB, compared to 10 secs with MyISAM. So speed might be an issue (or maybe there are just some knobs to tweak which would help). I don't think this is a top priority so I'm just jotting down some notes here for future reference. Note that these are based on brief research and are definitely not exhaustive.
InnoDB benefits:
- ACID transactions
- Row-level locking (only table-level with MyISAM)
- Foreign key constraints (technically don't exist in MyISAM)
- Automatic crash recovery
- Table compression (read/write)
- Spatial data types and indexes
MyISAM benefits:
- Fast COUNT(*) (when WHERE, GROUP BY, or JOIN is not used)
- Smaller disk footprint
- Very high table compression (read only)
If we end up wanting to switch to InnoDB, we need to do two things:
- Remove the line in settings/base.py which forces new tables to use MyISAM ('init_command': "SET storage_engine=MyISAM")
- Convert all tables from MyISAM to InnoDB:
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'gracedb' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE';
Note that this SQL command hasn't been tested and definitely should be!