1 / 35

Optimizing MySQL To Let People Argue

Learn how to optimize MySQL and tackle the challenges of working with legacy tables. Identify and solve problems, improve performance, and avoid common mistakes. This presentation covers index optimization, upgrading considerations, server configuration tricks, and caching strategies. Jeremy Bingham, an experienced MySQL developer, shares his insights and tips.

rafaelp
Download Presentation

Optimizing MySQL To Let People Argue

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Jeremy Bingham, OSCON 2011 Optimizing MySQL To Let People Argue

  2. Working With What You’re Given, or, “The Legacy Tables”

  3. Working With What You’re Given, or, “The Legacy Tables” • Lots of things that work on a small scale fall apart with millions of pages a day.

  4. Working With What You’re Given, or, “The Legacy Tables” • Lots of things that work on a small scale fall apart with millions of pages a day. • Just identify the problems!

  5. Working With What You’re Given, or, “The Legacy Tables” • Lots of things that work on a small scale fall apart with millions of pages a day. • Just identify the problems! Simple, right?

  6. Working With What You’re Given, or, “The Legacy Tables” • Lots of things that work on a small scale fall apart with millions of pages a day. • Just identify the problems! Simple, right? • Improvements possible, but you do have to look back.

  7. Real Tables, Real Problems

  8. Real Tables, Real Problems • Look for bad indexes. • Stories used a 20 byte VARCHAR as a primary key. • Comments used a 20 byte VARCHAR + an integer as a primary key. • Converted both to use integers. • Had to keep the old indexes around, of course.

  9. Real Tables, Real Problems • This will sound really dumb…

  10. Real Tables, Real Problems • This will sound really dumb… • … check the indexes are actually there. For some reason, no one ever bothered to add an index on nicknames on the user table.

  11. The Three Tools I’ve Used Most

  12. EXPLAIN • Probably the single most useful tool at your disposal

  13. Slow Query Log • If something’s in there a bunch of times, it’s probably a sign.

  14. SHOW PROCESSLIST; • And its brother, SHOW FULL PROCESSLIST; • Can be deceptive; sometimes what seem to be problem queries are actually fine, but are binding up because of some other query. EXPLAIN helps here.

  15. Upgrade?

  16. Pros of Upgrading • New features • Get beyond limitations of previous versions • We were held back by a lot of MySQL 3.23isms

  17. Cons of Upgrading

  18. Cons of Upgrading • Something can go horribly wrong.

  19. Cons of Upgrading • Something can go horribly wrong. • Like your data corrupting itself randomly after the upgrade.

  20. Let us configure our servers in accordance with the MySQL lifestyle. Configuration

  21. Actually read the docs, and don’t just go off the ancient sample configs. They leave out a lot of options, or leave in deprecated ones. They’re in Sumerian anyway. Configuration tricks we’ve used • Turn off XA transactions • innodb_flush_log_at_trx_commit = 0 • Per table InnoDB files • Large Pages • Work those buffer pool settings according to your procs and RAM.

  22. Flog the Hardware

  23. 2008 • Master/slave 16GB RAM 8 core with dedicated /tmp, 6x73GB RAID 10, tuned XFS • 2010 • Master/slave 32GB RAM, quad core, SSD

  24. Or, my Christmas comes every two years, but lasts between six and ten months. Election Season

  25. Each cycle builds on the last, but is special and unique

  26. Redesigning everything and trying to get it right, but still making sure old stuff works. Taking Opportunities

  27. Using What I’ve Learned • Cracking tables apart • Judicious partitioning • Where applicable, anyway • You’ll need lots of experimentation and thought with MySQL’s partitioning • Ferret out poorly thought out primary keys

  28. Using What I’ve Learned • Trim out truly unneeded columns and tables • Move less used columns off to the side • Better foreign keys and join tables • Stat tables, to avoid certain painful queries • Lots of testing with a full copy of the data

  29. The Most Important Thing • Cache, cache, cache! • Cache pages to disk • Great in 2006, blew up horribly during the Iowa caucuses in 2008 • Started storing pages in memcached for anonymous users in 2008

  30. More Caching • Cache fragments and collections for logged in folks too. • Particularly intense queries can be run from a cron so they’re always available. • Slower updates can be kicked to a background daemon.

  31. Caching’s Terrible Downside

  32. Never underestimate how much people will gripe if something’s not exactly up to date. Somehow, they’ll notice.

  33. What in God’s name was I thinking?

  34. Jeremy Bingham jeremy@dailykos.com Twitter: @captain_tenille http://time.to.pullthepl.ug Photo Credits: Siegfried, public Domain PDP-10, Michael L. Umbricht Stone axe, Didier Descouens Hammer, Evan-Amos Adze, Luigi Zanasi Teamsters strike in 1934, US Government work

  35. Questions?

More Related