Gospel: use InnoDB, never MyISAM
It seems everybody on StackExchange is singing from the same gospel:
- “[How can I] prevent queries from waiting for table level lock?” Answer: use InnoDB.
- The major advantages of InnoDB over MyISAM.
- “Even in a read-intesive system, just one
UPDATEstatement will quickly nullify whatever benefits MyISAM has.”
- The main differences between InnoDB and MyISAM, including cache sizing recommendations.
- “How do you tune MySQL for a heavy InnoDB workload?” Some discussion in the answers, and a link to Jenny Chen.
- “Any gotchas at all with converting from MyISAM to InnoDB?”
- Commands to convert MyISAM tables to InnoDB.
- In a rare StackExchange answer that doesn’t claim MyISAM is the work of Satan, we learn “InnoDB tablespaces tend to get very bloaty.”
innodb_file_per_tablesupposedly fixes that.
- If you don’t use
innodb_file_per_table, you’ll suffer great pain as your InnoDB file grows and grows.
Use smaller INTs and fixed-length CHARs
INTs: “[I could start] a side business making WordPress faster by converting all those
BIGINTs for those who only to expect 4,294,967,295 posts.”
- Performance implications of
ALTER TABLE tblname ROW_FORMAT=FIXED;to improve performance at the cost of larger files on disk.
- Don’t attempt to join a table that has a
TEXTcolumn, it will always require a temporary table on disk. It doesn’t matter if you’re not even returning the
TEXTin the result set. Not at all.
- Along those lines, learn what “using filesort” means.
Don’t use MySQL for that query
Optimize your filesystem interactions
- Mount filesystem with
nodirtimeif available – no reason to update database file modification times for access.
- On Linux systems, use
DEADLINE IOscheduler – the
ANTICIPATORYscheduler have been shown to be slow vs
innodb_flush_method=O_DIRECTto avoid a double buffer when writing.