I have read for years that MyISAM was the preferred DB engine for MySQL/MariaDB when you have a lots of read operations vs lots of insert/update/deletes. I have stuck with MyISAM for tables that are heavy on the SELECT side.
I recently had a about 5 out of 20 tables get corrupted which I could not bring back to life using the various repair methods. Turns out, each table that was corrupted/died was MyISAM. Can't remember if every MyISAM table was corrupted or not. But, none of the InnoDB tables were damaged as far as I can tell.
As I have been eating/breathing and sleeping MariaDB for the last few days working through recovering and improving our database infrastructure (replicas, backups etc), I have noticed that InnoDB is now the default and preferred engine for MariaDB (which wasn't always the case).
I also read that InnoDB tables are much less likely to die a terrible death like mine recently did. When InnoDB tables die, they are easier to recover.
With all that being said, I am seriously considering converting ALL of my remaining MyISAM tables to InnoDB.
My questions are:
- Should I expect anything to blow up on a live system which relies on these tables (websites, mobile app back ends etc) or is it more likely everything will just keep on chugging after the conversion?
- When it comes to the performance, is it likely to get better or worse on a system that is heavy on the SELECT side of things? It may still be worth it to convert for future stability of the tables even if there is a small performance hit, I just want to know what I'm in for so I can possibly increase the RAM and CPU resources if needed.
- Will converting these tables break or changes replication in any way?
- Final question... Is there really any good reason for me to keep any of my tables in MyISAM these days? It doesn't sound like there is.
Thanks in advance for any assistance with these items.
CodePudding user response：
Yes, it was always a trade-off between stability and performance. But if you are using MariaDB why not use Aria which is supposed to give you the best of both worlds?
Should I expect anything to blow on a live system which relies on these tables
Yes - you should always plan for recovery from corruption, ransomware, meteor strikes. InnoDB might be more resillient than MyISAM but it doesn't solve all issues.
is it likely to get better or worse on a system that is heavy on the SELECT side of things?
erm....it won't be faster. Whether there will be a noticeable drop in performance is a very complicated question and dependent on lots and lots of factors. If you're doing an export/import migration then it should be trivial to go back.
Will converting these tables break or changes replication in any way?
Only if you try to do the conversion while running replication at the same time.
Do set innodb_file_per_table before the migration.