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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">