MySQL Scaling Pains

Jeremy Zawodny spoke Friday morning about MySQL Scaling Pains.

I'm still just waking up, so here are some abbreviated notes.

• Security administration (don't just GRANT ALL PRIVILEGES ON *.* TO someuser, but think seriously about delegating privileges to separate users)
• Size Limits (MyISAM default 4GB limit can be modified, you just need to know the magic incantation)
• Lock Contention - consider using InnoDB instead of MyISAM if you have as many readers as writers. MyISAM tends to work fine when you've got 90-95% readers and just a few writers (or vice-versa) but you can run into lock contention when there are lots of both. InnoDB doesn't fix locking problems; it actually introduces some problems of its own.
• ALTER TABLE is slow. Requires an exclusive write lock on the entire table, all queries will back up until it finishes. Plan ahead.
• Disks often tend to be the bottleneck. You can add all of the CPU power in the world and it won't matter if it's waiting on a slow disk. Low seek times are more important than high transfer rates. RAID can help. If you have time, benchmark different disk combinations (suggested a tool called Bonnie++).
• Load balancers. If you use one, choose the correct algorithm. Sometimes the "least connections" algorithm can make things worse. Often a simple "round-robin" algorithm works just great.
• Handling many connections. Setting wait_timeout to a lower value will force idle connections to disconnect. Sometimes this can improve overall efficiency.
• Data partitioning by servers (i.e. putting 1/Nth of your data on each of N clusters of servers). Instead of a single "users" table, you have 4 different tables ("users_abcdefg", "users_hijklmn", "users_opqrstu", "users_vwxyz") and the application needs to look at the first letter of the key to figure out which table to query.
• Full-Text search is neat, but it has its limits. First, be sure to use 4.x, not 3.23. Also, it's not as flexible as other software.

Zawodny also inserted a small Yahoo! advertisement in his slides; Yahoo! is hiring engineers. His incentive is twofold. (1) Smart folks tend to go to OSCON, so it's a targeted audience, and (2) if you send him (or me) your resume we can get the employee referral bonus if you end up getting hired.