MySQL performance tips from around the web

Gospel: use InnoDB, never MyISAM

It seems everybody on StackExchange is singing from the same gospel:

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="">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.