Blog by Sumana Harihareswara, Changeset founder

03 Feb 2007, 20:11 p.m.

To Optimize Response Speed On MySQL, Follow These Instructions

Hi, reader. I wrote this in 2007 and it's now more than five years old. So it may be very out of date; the world, and I, have changed a lot since I wrote it! I'm keeping this up for historical archive purposes, but the me of today may 100% disagree with what I said then. I rarely edit posts after publishing them, but if I do, I usually leave a note in italics to mark the edit and the reason. If this post is particularly offensive or breaches someone's privacy, please contact me.

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.