PerformanceTuningWithJayPipes
Notes by Sheeri Kritzer
Standing room only — who would have thought performance tuning was so important!?!?!? (< / sarcasm>). Seriously though, there was a lot of typing happening.
Contents |
[edit] Benchmark
Get a baseline Give yourself a target (”what’s good enough?”) Change one thing at a time Record everything (even the ‘trivial’ stuff) Disable the query cache.
[edit] Profiling
Profiling a currently running system (vs. benchmarking, on test) EXPLAIN SELECT slow query logs (mysqldumpslow) low hanging fruit (you figure out what they are, if you’re a DBA it might be putting an index, if you’re a developer maybe it’s changing a query) and diminishing returns mytop to catch excessive locking/contention and long-running queries.
[edit] Usual problem sources:
[edit] Bad indexing choices
too many indexes missing indexes Look for covering indexes (indexes where all the info is in the index, so it never has to go to the data for it, only the index) Order of fields in multi-column index Ensure good selectivity on index fields (if there is bad selectivity, the optimizer won’t use the index. Bad selectivity is records that are “true” or “false”; “active” or “closed”. If most of the records are one or a few types and others aren’t used much, then it will just do a table scan, if you’re looking for the many data type (ie, “closed” orders when the database is large). Removing redundant indexes (look for column + multi-column indexes. No need to index “lastname” and “lastname, firstname”) InnoDB puts the primary key in each index
[edit] Not using storage engines effectively, bloated/inefficient schema
Use appropriate datatypes. Do you need a BIGINT for the autoincrement? Index records are narrower with smaller datatypes. Consider horizontally splitting multi-column tables. If you use some fields but not others, make 2 tables, and index the heck out of the one used more. :) Vertically splitting using MERGE tables or partitioning InnoDB: choose the smallest possible primary key because it’s appended to EACH secondary index. Also ALWAYS GIVE A PRIMARY KEY because InnoDB is going to put one in. Don’t use surrogate keys if a natural primary key occurs.
[edit] Bad coding practices
Break things down into the smallest chunks possible (that’s a good practice). Break large SELECT statements into smaller queries — are you doing an intersect? a union?) Use Stored Procedures for BIG performance boost (26%) InnoDB: use counter tables — ie make a separate table that updates count when a row is inserted or deleted. Isolate index fields on one side of the table if you have a function. If you want orders from last 7 days, WHERE TO_DAYS(order_created) - TO_DAYS(CURRENT_DATE()) >= 7 can be improved to: WHERE order_created >= CURRENT_DATE() = INTERVAL 7 DAY or, even better, put the CURRENT_DATE() into a variable (MySQL or otherwise) and just use that, so the query can be cached. Use calculated fields: For example, wildcard on left of string can’t use index. WHERE email LIKE "%.mysql.com" won’t use index. But if you add another field, reverse_email, and add the REVERSE(email) into it, and then WHERE reverse_email LIKE "moc.lqsym.%" Join hints like STRAIGHT JOIN (tells the optimizer what order to use and ignore optimization) should not be used, if they are, they should be re-examined regularly to make sure they’re still valid. Convert correlated subqueries to a standard join. Use INNER or LEFT JOIN instead. Or, use a derived table — a subquery in the FROM clause.
[edit] Server variables not tuned properly — although Jay stated that you should tune the application FIRST (ie, queries) and THEN the server
Know what’s global vs. per thread Make small changes and test (ie, one var at a time) Usually temporary solution Query cache defaults to size 0. Increase if you need to! key_buffer_size for MyISAM only, innodb_buffer_pool_size is for InnoDB Put more memory in. Cheapest, fastest, and easiest way to boost performance
Hardware/network bottlenecks
Trivia: MySQL engineers represent 12 countries.
[edit] A Video
Jay Pipes also gave this talk at Google, as part of the EngEdu program. Visit Performance Tuning Best Practices for MySQL via Google Video.