Jeremy Zawodny spoke Friday
morning about MySQL
I’m still just waking up, so here are some abbreviated notes.
- Security administration (don’t just
GRANT ALL PRIVILEGES ON *.* TO, but think seriously about delegating privileges to
- 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 TABLEis 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
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.