MySQL Performance Tuning

MySQL Performance Tuning with Jay Pipes (MySQL Users Conference Workshop).

Standing room only — who’dve thunk performance tuning was so important!?!?!? (< / sarcasm>). Seriously though, there was a lot of typing happening.

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.

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.

Usual problem sources:
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

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.

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.

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.

MySQL Performance Tuning with Jay Pipes (MySQL Users Conference Workshop).

Standing room only — who’dve thunk performance tuning was so important!?!?!? (< / sarcasm>). Seriously though, there was a lot of typing happening.

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.

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.

Usual problem sources:
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

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.

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.

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.