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.”
<a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html">innodb_file_per_table</a>supposedly fixes that.
- If you don’t use
<a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html">innodb_file_per_table</a>, 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
<a href="http://dba.stackexchange.com/questions/2640/what-is-the-performance-impact-of-using-char-vs-varchar-on-a-fixed-size-field/2643#2643">ALTER TABLE tblname ROW_FORMAT=FIXED;</a>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.