MySQL performance tips from around the web

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 DELETE or UPDATE statement 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

• Convert BIGINTs to 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 VARCHARs: do <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 BLOB or TEXT column, it will always require a temporary table on disk. It doesn’t matter if you’re not even returning the BLOB or TEXT in the result set. Not at all.
• Along those lines, learn what “using filesort” means.

Don’t use MySQL for that query

Do reads, both full-text searches and selections by attribute (think tag queries in WordPress) on Sphinx instead of in MySQL.

Optimize your filesystem interactions

Monitis offers the following:

• Mount filesystem with noatime and nodirtime if available – no reason to update database file modification times for access.
• On Linux systems, use NOOP or DEADLINE IO scheduler – the CFQ and ANTICIPATORY scheduler have been shown to be slow vs NOOP and DEADLINE scheduler.
• Use innodb_flush_method=O_DIRECT to avoid a double buffer when writing.