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.