MySQL Scaling Pains

MySQL logo 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.

4 thoughts on “MySQL Scaling Pains

  1. Jeremy C. Wright

    Job offers? Oooh, and here was me using 😉 Too bad Yahoo’s not hiring IT Managers/Directors. Ah well, back to Monster and staring at those salaries wondering who in the world earns that much.

  2. Jeremy C. Wright

    Whoops, that was spammy, sorry.

    As I said in Jeremy’s blog, we’ll be looking at implementing a MySQL system right around v5, as it’ll have all the features we currently use of SQL Server, Clipper and Oracle.

    Can’t wait til the time where we have more choice!

  3. Jeremy Zawodny's blog

    MySQL Scaling Pains — slides now on-line

    After a bit of a delay, I’ve posted the slides to the MySQL Scaling Pains talk I gave at OSCON last Friday. They’re available in HTML and PDF. The PDF sucks and I’m not sure if I should blame OpenOffice or ps2pdf for that. If you’re a Yahoo engineer, e…

Comments are closed.