This video from Google is awesome! It gives a good summary of MySQL performance tuning in 45 minutes.  For those who want to get the quick digest from this video. I have put down some notes below. Apart from this,  I also put down the background information for some topics to make it more completed in my blog. Enjoy!

 

Transcript:

Benchmarking Core Concept

1. Set a target with baseline as starting point. Change 1 thing at a time to see how it affects the performance. At the end, record the configuration and result for tracking later.
2. Disable query cache via giving it size=0 to avoid the cache affecting the result. However, it is OS cache can aslo skew the result. To minimize its impact, you can pump up # of runs.

Profiling Concept

1. Explain plan (Access type? path?)
2. Use the Slow Query Log (threshold to log, no index log) and mysqldumpslow
3. Concentrate on the stuff that gives you bigger impact
4. Use mytop to catch locking and long-running queries

Sources of Problems

1. Poor indexing choices
2. Inefficient or bloated schema design (don’t denormalize at the beginning)
3. Bad coding practices (MySql has some subqueries issue, in general using join is better)
4. Server variable not tuned properly (time consuming test)
5. Hardware and/or Network Bottlenecks (IO & CPU bound, Network latency)

Index Guidelines

1. Poor or missing index is fastest way to kill a system
2. Covering index (MySql can get all the information from the index record which is slimmer than data record. Use this information to complete whatever the queries are. The slimmer the index, the more you can fit into single index block, the fewer reach you going to do, the faster it is).
3. Ensure good selectivity on index fields. Cardinality = 1 (unique). Lower selective column can attach to other columns to become multi-column indexes.
4. On multi-column indexes, pay attention to order of fields within the index definition.
     - clustered index organization
     - PK (product_id, tag_id)
     - Innodb vs myism
5. Database grows, make sure distribution is good.
6. Remove redundancy indexes (mysql doesn’t check). Update key value and insert will touch indexes (overhead).

Database index is similar to the book index that helps you quickly locate the information you want. Without index, you need to do table scan to find out a set of records that match - O(n). However, to maintain a separate list of indexes’ values and keep them updated as your data change (insert/ update/ delete), you introduce overhead as well. So, we don’t index every column in a table. Some of the indexes have more than 1 columns (multi-column). Such indexes can improve the query speed if you often query all columns together in the WHERE clause or if a single column doesn’t have sufficient variety (not selective as >25% of the rows have the same value in this column - table scan is better for that). For example, you create multicolumn index for last_name & first_name that gives you fewer rows than just look at last name. If you create 2 indexes instead of multicolumn index, it will not be helpful as MySQL won’t use them at the same time. In fact, MySQL will ever use one index per table per query except for UNION. To choose the index, MySQL will pick the one that gives fewer rows back based on some statistics.

Schema Guidelines

1. Use the smallest data types needed -> narrower index records -> more records per block -> fewer reads -> faster
2. Choose a small clusting key since it is appended to each secondary index record
3. Don’t use surrogate keys when a naturally occuring primary key exists
4. Consider horizontally splitting many-columned table where some columns are rarely accessed or some columns has large size but others are more frequently accessed. Split and join by 1×1 is more efficient.
5. Consider vertically splitting

Coding Guideline

1. Use stored procedure for a big performance boost
2. Innodb doesn’t optimize select count(*) query b/c multiversioning (Use counter table - increment it when insert)
3. Isolate indexed fields on one side of equation. Use application to insert a day instead of using current day.
4. LIKE ‘%.com’ cannot use index (use reverse email address and put % on the right).
5. Set-based programming vs procedural programming
6. Don’t try to outhink the optimizator.

Get rid of correlated subquery

select p.name,
  (select MAX(price) FROM OrderItems WHERE product_id = p.product_id) AS max_sold_price
FROM Products p;vsselect p.name, MAX(oi.price) AS max_sold_price
FROM Products p
INNER JOIN OrderItem oi ON p.product_id = oi.product_id
GROUP BY p.name

Derived Table is better than Correlated Subquery

Server Parameters

1. per global vs per thread (sort buffer size)
2. Query Cache (off by default): READ intensive (turn it on) but not for write intensive
3. key_buffer_Size != innodb_buffer_pool_size (50-60% of machine memory should allocate)
4. Memory is cheapest, fastest, easiest way to better performance