Cogito, Ergo Sumana for 2007 February 3 (entry 0)

# 03 Feb 2007, 09:11PM EST: To Optimize Response Speed On MySQL, Follow These Instructions: Just watched Jay Pipes's 45-minute Google TechTalk, "Performance Tuning Best Practices for MySQL". A few points I took to heart:

• Even if you're using InnoDB for all your tables, MySQL is still using MyISAM for internal stuff, so don't think you don't have to tune that stuff in your config file too. Fog Creek Software has heard of folks using InnoDB instead of MyISAM for some tables for performance gains (almost everyone in the Google audience also favored InnoDB), but InnoDB doesn't support fulltext search, so the BugEvents table is a bad candidate for InnoDB. (A table type comparison, a comparison from the MySQL team, and one from the DreamHost folks. It freaks me out that ACID/data integrity might be a "feature" you'd trade off against performance, but I'm not MySpace. Then again, even they use SQL Server.)

• The top sources of performance problems, according to Pipes, in order of severity/frequency:
  1. Poor Indexing Choices
  2. Inefficient or Bloated Schema Design
  3. Bad Coding Practices
  4. Server Variables Not Tuned Properly
  5. Hardware and/or Network Bottlenecks

  "Bad Coding Practices" means the SQL queries your app is submitting to the DB. Pipes encourages us to think in sets, not procedural for-loops and the like, when creating our queries.

• If there's already an index that MySQL automatically creates and checks, based on the primary key, don't manually create a duplicate index on the primary key! That just wastes time on every write while optimizing nothing.

• Dan Racanelli taught me this when we were at Salon together, if I recall correctly: use EXPLAIN to check whether your join or whatever is going to be a huge performance hog. Pipes also reminds us about the Slow Query Log.

I've just listed a few of the best tips. If you deal with MySQL for fun or profit, check out the talk.