Uploaded bymysqlops
4,376 views

Percona Live 2012PPT: MySQL Query optimization

The document discusses techniques for optimizing MySQL queries. It begins by explaining how to use EXPLAIN to view a query's execution plan and identify opportunities for improvement. Examples demonstrate how adding appropriate indexes can speed up queries by reducing the number of rows examined. The use of composite indexes, covering indexes, and index column order are also addressed. More advanced profiling techniques are presented to further analyze query performance beyond what EXPLAIN shows.

Embed presentation

1 / 139
2 / 139
3 / 139
4 / 139
5 / 139
6 / 139
7 / 139
8 / 139
9 / 139
10 / 139
11 / 139
12 / 139
13 / 139
14 / 139
15 / 139
16 / 139
17 / 139
18 / 139
19 / 139
20 / 139
21 / 139
22 / 139
23 / 139
24 / 139
25 / 139
26 / 139
27 / 139
28 / 139
29 / 139
30 / 139
31 / 139
32 / 139
33 / 139
34 / 139
35 / 139
36 / 139
37 / 139
38 / 139
39 / 139
40 / 139
41 / 139
42 / 139
43 / 139
44 / 139
45 / 139
46 / 139
47 / 139
48 / 139
49 / 139
50 / 139
51 / 139
52 / 139
53 / 139
54 / 139
55 / 139
56 / 139
57 / 139
58 / 139
59 / 139
60 / 139
61 / 139
62 / 139
63 / 139
64 / 139
65 / 139
66 / 139
67 / 139
68 / 139
69 / 139
70 / 139
71 / 139
72 / 139
73 / 139
74 / 139
75 / 139
76 / 139
77 / 139
78 / 139
79 / 139
80 / 139
81 / 139
82 / 139
83 / 139
84 / 139
85 / 139
86 / 139
87 / 139
88 / 139
89 / 139
90 / 139
91 / 139
92 / 139
93 / 139
94 / 139
95 / 139
96 / 139
97 / 139
98 / 139
99 / 139
100 / 139
101 / 139
102 / 139
103 / 139
104 / 139
105 / 139
106 / 139
107 / 139
108 / 139
109 / 139
110 / 139
111 / 139
112 / 139
113 / 139
114 / 139
115 / 139
116 / 139
117 / 139
118 / 139
119 / 139
120 / 139
121 / 139
122 / 139
123 / 139
124 / 139
125 / 139
126 / 139
127 / 139
128 / 139
129 / 139
130 / 139
131 / 139
132 / 139
133 / 139
134 / 139
135 / 139
136 / 139
137 / 139
138 / 139
139 / 139
MySQL Query Optimization Kenny Gryp <kenny.gryp@percona.com> Percona Live Washington DC / 2012-01-11
MySQL Query Optimization Basic Query Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 2
MySQL Query Optimization The number one goal is to have faster queries. The process is: We first ask MySQL what its intended execution plan is. If we don't like it, we make a change, and try again... More Information: High Performance MySQL, 2nd Edition: http://shop.oreilly.com/product/9780596101718.do EXPLAIN! http://dev.mysql.com/doc/refman/5.5/en/using- explain.html www.percona.com 3
MySQL Query Optimization Basic Query Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 4
First Example www.percona.com 5
Find the Title Bambi 3.09s www.percona.com 6
Find the Title Bambi ALL means tablescan In this case a sort is required because of the order by, but not for all rows, only matching rows Additional filtering may be possible before passing to sort. 3.09s www.percona.com 7
Specify Index Length www.percona.com 8
Size of Key? Length of index is limited: 1000 bytes MyISAM 767 bytes InnoDB UTF-8 uses up to 3 bytes (3 bytes are used in determining) Variable length strings (VARCHAR, TEXT...): add 1 or 2 bytes for length www.percona.com 9
We must revisit... Much Faster! 0.00s www.percona.com 10
We must revisit... Using = for comparison, but not primary key lookup. Identified title as a candidate index, chose to use it. Size of the index used (in bytes) Anticipated number of rows to be examined dropped considerably. 0.00s www.percona.com 11
Other ways of accessing Better type of At most one matching row. In InnoDB the primary key is often much faster than all other keys. 0.00s www.percona.com 12
Range Scan Type is range. BETWEEN, IN() and < > are also ranges. Anticipated number of rows to be examined has increased - we are not specific enough. Ignore the time with EXPLAIN. Only look at the time for a query. 0.00s www.percona.com 13
Why’s that a range? We're looking for titles between BambA and BambZ* When we say index in MySQL, we mean trees. That is, B-Tree/B+Tree/T-Tree. Pretend they're all the same (for simplification) There's no radically different indexing methods in MySQL unless you play storage engine Bingo**. * In reality the range is a little wider ** The memory & ndb storage engine supports hash indexes www.percona.com 14
What’s that? www.percona.com 15
Could this be a range? 3.2s www.percona.com 16
No, we can’t traverse. Do we head left or right here? www.percona.com 17
LIKE ‘Z%’ 0.05s www.percona.com 18
LIKE ‘T%’ 3.13s www.percona.com 19
LIKE ‘The %’ 3.07s www.percona.com 20
MySQL is (reasonably) smart. It dynamically samples the data to choose which is the better choice - or in some cases uses static statistics*. This helps the optimizer choose: Which indexes will be useful. Which indexes should be avoided. Which is the better index when there is more than one. * To refresh statistics run ANALYZE TABLE table_name; www.percona.com 21
Why avoid indexes? B-Trees work like humans search a phone book; Use an index if you want just a few rows. Scan cover-to-cover if you want a large percentage. www.percona.com 22
Why avoid indexes (cont.) We benchmarked this on a different schema: Table scan has a relatively fixed cost (red line). The index has completely different effectiveness depending on how much it can filter. Hopefully MySQL switches at the right point. www.percona.com 23
What you should take away: Data is absolutely critical. Development environments should contain sample data exported from production systems. Input values are absolutely critical. Between two seemingly identical queries, execution plans may be very different. See also: http://www.mysqlperformanceblog.com/2009/10/16/how-not-to-find-unused-indexes/ www.percona.com 24
Anticipated Number Of Rows This number of rows is a guess. It keeps changing between examples. 3.41s www.percona.com 25
Statistics Sampling InnoDB only keeps statistics samples in memory - and not on disk*. Sampling is performed when a table is first opened, and estimates are based on an estimate from sampling 8 random pages. This number is used whether the table have 10 rows or 10 million rows. In InnoDB plugin this is now configurable with innodb_stats_sample_pages . The setting is global, and will apply to all tables. * In XtraDB 12 statistics can be retained with innodb_use_sys_stats_table=1. * MySQL 5.6: innodb_stats_persistent_sample_pages www.percona.com 26
Statistics (cont.) Statistics are automatically regenerated on most meta-data commands: SHOW TABLE STATUS Disable with innodb_stats_on_metadata=0 SHOW INDEX (5.1 and above). Information Schema commands. As well as: When the table size changes by more than 1/16th. If more than 2,000,000,000 rows have been inserted. Disable with innodb_stats_auto_update=0 (XtraDB only). www.percona.com 27
Improve this Query 3.41s www.percona.com 28
We’re Spoiled for Choice. www.percona.com 29
Index on production_year 3.53s www.percona.com 30
Might work if... 0.92s www.percona.com 31
Index on title(50) 0.02s www.percona.com 32
Comparing the two: ★ mysql> EXPLAIN SELECT * from title WHERE title = 'Pilot' AND production_year BETWEEN 2006 and 2009G www.percona.com 33
Composite Indexes. What is better? INDEX py_t (production_year, title) INDEX t_py (title, production_year) www.percona.com 34
Index on py_t http://www.mysqlperformanceblog.com/2010/01/09/getting-around-optimizer-limitations-with-an-in-list/ 0.02s www.percona.com 35
Index on t_py 0.00s www.percona.com 36
Recommendations Index over multiple columns if it can improve filtering. i.e. GOOD: Only some pilots made between 2006-2009. BAD: All pilots made between 2006-2009. www.percona.com 37
Recommendations (cont.) Don't know what order to specify the columns? RULE: Think how to filter the fastest. Use that order left to right. EXCEPTION: If there's a range (><, BETWEEN, %). Those always go to the RIGHT. After a a column is used for rangescan, groupby, you cannot use the next column in the composite index http://www.mysqlperformanceblog.com/2010/01/09/getting-around-optimizer-limitations-with-an-in-list/ www.percona.com 38
A quick sidetrack... So far indexes have only been used for filtering. This is the most typical case - don’t forget it. There are also some other ways that MySQL can use an index To avoid having to sort. To prevent temporary tables. To avoid reading rows. .. www.percona.com 39
The first example again 3.13s www.percona.com 40
Index prevents sort 0.00s www.percona.com 41
Temporary Table www.percona.com 42
“Loose index scan” ALTER TABLE title ADD INDEX py (production_year); www.percona.com 43
Retrieving only limited columns Query: SELECT person_id FROM cast_info WHERE person_role_id = 35721; www.percona.com 44
Retrieving only limited columns: ALTER TABLE cast_info ADD INDEX (person_role_id); www.percona.com 45
Covering Index Optimization: ALTER TABLE cast_info ADD INDEX person_role_id_person_ id(person_role_id, www.percona.com 46
Key_len in EXPLAIN key_len: 152 How much bytes of the index is being used for the query? In composite indexes, can be used to determine how many columns will be used to _filter_ on. www.percona.com 47
Key Length Example key_len is only for WHERE clause, not the covering part www.percona.com 48
MySQL Query Optimization Basic Query Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 49
The limitations of EXPLAIN EXPLAIN shows MySQL’s intentions - there is no post execution analysis. How many rows actually had to be sorted? Was that temporary table created on disk? Did the LIMIT 10 result in a quick match, resulting in fewer rows scanned? .. we don’t know. www.percona.com 50
More Advanced Combine EXPLAIN with other MySQL diagnostics: SHOW SESSION STATUS Recommended to run before and after the query. Available in MySQL 5.0+ SHOW PROFILES Available in 5.0 (limited), 5.1. Breaks down the time taken on various steps of query execution. Huge amount of skew in any numbers it reports under Linux. Slow Query Log Extended Statistics (Percona Server) Will let you know examined rows, temp table on disk, sort on disk, how many IOPS in InnoDB etc. www.percona.com 51
Find the actor that stared in the mysql-5141> EXPLAIN select STRAIGHT_JOIN count(*) as c, person_id from cast_info FORCE INDEX(person_id) inner join title on(cast_info.movie_id=title.id) where title.kind_id = 1 GROUP BY cast_info.person_id ORDERmost movies. by c DESC LIMIT 1G *************************** 1. row *************************** id: 1 select_type: SIMPLE MySQL says that only 8 table: cast_info rows were examined in 5.1.41 type: index possible_keys: NULL key: person_id key_len: 8 ref: NULL rows: 8 Extra: Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: title type: eq_ref possible_keys: PRIMARY,title_kind_id_exists key: PRIMARY key_len: 4 ref: imdb.cast_info.movie_id rows: 1 Extra: Using where 2 rows in set (0.00 sec) www.percona.com 16m52
This is the output from 5.0.89 www.percona.com 53
Double Checking “The number of times the first entry in an index was read” “The number of requests to read a row based on a key.” “The number of requests to read the next row in key order.” “The number of requests to read the next row in the data file.” “The number of requests to insert a row in a table.” http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html www.percona.com 54
SHOW PROFILES SET profiling = 1; .. run query .. SHOW PROFILES; | Query_ID | Duration | Query | 1 | 211.21064300 | select STRAIGHT_JOIN count(*) as c, person_id FROM cast_info FORCE INDEX(person_id) INNER JOIN title ON (cast_info.movie_id=title.id) WHERE title.kind_id = 1 GROUP BY cast_info.person_id ORDER by c DESC LIMIT 1 | show profile for query 1; www.percona.com 55
SHOW PROFILES (cont.) mysql> show profile for query 1; +------------------------------+------------+ | Status | Duration | .. +------------------------------+------------+ | Copying to tmp table | 113.862209 | | starting | 0.002133 | | converting HEAP to MyISAM | 0.200272 | | checking permissions | 0.000009 | | Copying to tmp table on disk | 96.506704 | | checking permissions | 0.000009 | | Sorting result | 0.634087 | | Opening tables | 0.000035 | | Sending data | 0.000047 | | System lock | 0.000022 | | end | 0.000006 | | init | 0.000033 | | removing tmp table | 0.004839 | | optimizing | 0.000020 | | end | 0.000016 | | statistics | 0.000032 | | query end | 0.000004 | | preparing | 0.000031 | | freeing items | 0.000064 | | Creating tmp table | 0.000032 | | logging slow query | 0.000004 | | Sorting for group | 0.000021 | | logging slow query | 0.000003 | | executing | 0.000005 | | cleaning up | 0.000006 | .. +------------------------------+------------+ 25 rows in set (0.00 sec) www.percona.com 56
Slow Log Statistics SET GLOBAL long_query_time = 0; SET GLOBAL log_slow_verbosity = ‘full’; This was executed on a machine with entirely cold # Time: 100924 13:58:47 # User@Host: root[root] @ localhost [] caches. # Thread_id: 10 Schema: imdb Last_errno: 0 Killed: 0 # Query_time: 399.563977 Lock_time: 0.000110 Rows_sent: 1 Rows_examined: 46313608 Rows_affected: 0 Rows_read: 1 # Bytes_sent: 131 Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 25194923 # InnoDB_trx_id: 1403 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes # Filesort: Yes Filesort_on_disk: Yes Merge_passes: 5 # InnoDB_IO_r_ops: 1064749 InnoDB_IO_r_bytes: 17444847616 InnoDB_IO_r_wait: 26.935662 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 65329 SET timestamp=1285336727; select STRAIGHT_JOIN count(*) as c, person_id FROM cast_info FORCE INDEX(person_id) INNER JOIN title ON (cast_info.movie_id=title.id) WHERE title.kind_id = 1 GROUP BY cast_info.person_id ORDER by c DESC LIMIT 1; www.percona.com 57
MySQL Query Optimization Basic Query Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 58
Join Analysis Filter out as much as possible first, you can only do this by looking at WHERE clause www.percona.com 59
Join Analysis ALTER TABLE name ADD INDEX (name(50)); www.percona.com 60
Join Analysis ALTER TABLE person_info ADD INDEX (person_id); www.percona.com 61
The order you see these tables mentioned is the order MySQL has decided to join on. www.percona.com 4m2s 62
Filter out as much as possible first, you can only do this by looking at WHERE clause www.percona.com 4m2s 63
First Index: mysql> ALTER TABLE char_name ADD index name_idx (name(50)); www.percona.com 64
Filter out as much as possible first, you can only do this by looking at WHERE clause The order changed. cast_info was previously first! 1m48s www.percona.com 65
Second Index: mysql> ALTER TABLE cast_info ADD INDEX person_role_id_person_id(person_role_id, person_id); www.percona.com 66
TIP: Using a covering index means that we retrieve all data directly from the index. www.percona.com 0.00s 67
Join Methods You need to filter as fast as possible. Here's why: MySQL only uses one join method - a nested loop join. www.percona.com 68
Sample Query Find all actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
Sample Query Find all actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
Sample Query Find all actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
Sample Query Find all actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
Sample Query Find all actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
If that query is common When you can’t filter enough on one table, bring some of the other filters from the other tables to the first one: Actors: id first_name last_name start_date finish_date 1 Sean Connery 1962 1971 2 George Lazenby 1969 1969 3 Roger Moore 1973 1985 4 Timothy Dalton 1987 1989 5 Pierce Brosnan 1995 2002 6 Daniel Craig 2006 2011 www.percona.com 70
MySQL Query Optimization Basic Query Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 71
Subquery Analysis Will it fix it if we add an index on title.kind_id? 4.84s www.percona.com 72
With index on title.kind_id No! It doesn’t. Why is this? 4.9s www.percona.com 73
Scalar Subquery Change to using equality, it works! but only when kind is unique! 0.07s www.percona.com 74
Solving via Join It’s okay to have multiple kind’s specified using this syntax. ALTER TABLE title ADD KEY (kind_id); 0.06s www.percona.com 75
Should We Completely Avoid Them? •No, Benchmark! •“Delayed Join” http://www.mysqlperformanceblog.com/2007/04/06/using-delayed-join-to-optimize- count-and-limit-queries/ www.percona.com 76
MySQL Query Optimization Basic Query Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 77
Next Problem (cont.) The problem with this schema, is there's just a couple of outliers with really long names: www.percona.com 78
Two ways to solve this: 1. Pick a good length to get a lot of uniqueness: www.percona.com 79
Two ways to solve this: 1. Pick a good length to get a lot of uniqueness: www.percona.com 80
Two ways to solve this: 1. Pick a good length to get a lot of uniqueness: 96% uniqueness, but only 20 chars instead of 300+ Looks pretty good to me: ALTER TABLE title ADD index (name(20)) www.percona.com 81
Option 2: Emulate a Hash Index Is possible only with MEMORY engine: ALTER TABLE table ADD INDEX USING HASH (title); www.percona.com 82
Option 2: Emulate a Hash Index A good hashing algorithm has good distribution. How good is this? www.percona.com 83
Option 2: Hash Index (cont.) Query needs to be transformed slightly to: SELECT * FROM title WHERE title_crc32=crc32(‘my_title’) AND title=’my_title’; All updates/inserts need to also update the value of title_crc32: Can be easily done via the application, or a trigger if write load is very low. www.percona.com 84
Pros/Cons Prefix Index: Hash Index: ★ Pro: ★ Pro: Very Good when there is not Built in to MySQL/no magic much uniqueness until very required. far into the string. ★ Cons: ★ Cons: Equality searches only. Not very effective when the Requires ugly magic to work start of the string is not very with collations/ case unique. sensitivity. www.percona.com 85
Things are looking good!? Please don’t take away that adding indexes == only secret to performance. The story is a lot more complicated. We have to get around optimizer limitations, and a lack of index/join options. www.percona.com 86
Optimizer Hints Optimizer decision making is all about tradeoffs. MySQL wants to pick the best plan, but it can’t be exhaustive in deciding if it takes too long. If MySQL is off by a lot, you may want to provide a hint: USE INDEX FORCE INDEX IGNORE INDEX STRAIGHT_JOIN See: http://dev.mysql.com/doc/refman/5.5/en/index-hints.html www.percona.com 87
Optimizer Hints, Should We? www.percona.com 88
More features & workarounds EXPLAIN only works for SELECT: convert UPDATE/ DELETE to SELECT (feature added in 5.6) The IN() list workaround http://www.mysqlperformanceblog.com/2010/01/09/getting- around-optimizer-limitations-with-an-in-list/ Index Merge http://dev.mysql.com/doc/refman/5.1/en/index-merge- optimization.html www.percona.com 89
MySQL Query Optimization Basic Query Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 90
CREATE TABLE www.percona.com 91
SHOW FIELDS www.percona.com 92
SHOW INDEXES www.percona.com 93
Day One Advice (1) Keep it simple - store atomic types in each field. This means storing first name and last name as two separate fields. Don’t try and get tricky with how you store the data. i.e. this field means it’s a phone number unless this other field is set to something. www.percona.com 94
Day One Advice (2) Use appropriate data types - If you’re not sure about the length, varchar(100) is still much better than varchar(255). Use an Integer for a number. Decimal for precision numbers, float for non-precision numbers, etc. If integers don’t have to be negative, use unsigned. www.percona.com 95
Day One Advice (3) Plan how you will be accessing the data. If you know that you have to do 4 expensive joins to execute a common query - it might not be the best solution. It’s okay to have redundancy in the database from very early on. One example is pre-generating the ‘average score’ on IMDB titles. www.percona.com 96
Clustered Index Everything in InnoDB is an index: Data is stored in a clustered index organized by the primary key. In the absence of a primary key, the first unique not null key is selected*. Other indexes are stored in secondary indexes. * In the absence of a unique key, a hidden 6 byte key is created. www.percona.com 97
What is a clustered index? First lets look at how MyISAM stores data*: Staff.MYI Staff.MYD ID First Name 8 1 lePeter 4 12 2 leVadim 2 6 10 14 7 leFred 1 3 5 7 9 11 13 15 4 leEwen Data is stored “roughly” in insertion order, with no 5 leBaron guarantees, i.e. .. .. Deleted rows may be filled * Illustrating B-Tree as Binary Tree for simplicity with newer records. www.percona.com 98
What is a clustered index (cont.) A MyISAM primary key lookup looks something like this: Staff.MYI Staff.MYD ID First Name 8 1 lePeter 4 12 2 leVadim 2 6 10 14 7 leFred 1 3 5 7 9 11 13 15 4 leEwen 5 leBaron .. .. www.percona.com 99
What is a clustered index (cont.) A MyISAM primary key lookup looks something like this: Staff.MYI Staff.MYD ID First Name 8 the index to find the Traverse address of the row we 1 lePeter 4 12 are looking for. 2 leVadim 2 6 10 14 7 leFred 1 3 5 7 9 11 13 15 4 leEwen 5 leBaron .. .. www.percona.com 99
What is a clustered index (cont.) A MyISAM primary key lookup looks something like this: Staff.MYI Staff.MYD ID First Name 8 the index to find the Traverse address of the row we 1 lePeter 4 12 are looking for. 2 leVadim 2 6 10 14 address in the the Lookup 7 leFred 1 3 5 7 9 11 13 15data file. 4 leEwen 5 leBaron .. .. www.percona.com 99
What is a clustered index (cont.) ★ An InnoDB Primary Key lookup looks like this: Staff.ibd 12 0xACDC 4 0xACDC 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. * Illustrating B+Tree as Binary Tree for simplicity. www.percona.com 100
What is a clustered index (cont.) ★ An InnoDB Primary Key lookup looks like this: Staff.ibd 12 0xACDC Traverse the index to find the 4 0xACDC full row. 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. * Illustrating B+Tree as Binary Tree for simplicity. www.percona.com 100
What is a clustered index (cont.) ★ An InnoDB Primary Key lookup looks like this: Staff.ibd 12 0xACDC Traverse the index to find the 4 0xACDC full row. 12 0xACDC 22 0xACDC leVadim, ..., ... 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. * Illustrating B+Tree as Binary Tree for simplicity. www.percona.com 100
What is a clustered index (cont.) ★ An InnoDB Primary Key lookup looks like this: Staff.ibd 12 0xACDC Traverse the index to find the 4 0xACDC full row. 12 0xACDC Stop here. 22 0xACDC leVadim, ..., ... 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. * Illustrating B+Tree as Binary Tree for simplicity. www.percona.com 100
What is a clustered index (cont.) ★ A secondary key lookup looks like this: extension_number 8 4 12 12 0xACDC 2 6 10 14 1 3 5 7 9 11 13 15 4 0xACDC 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. www.percona.com 101
What is a clustered index (cont.) ★ A secondary key lookup looks like this: extension_number 8 Traverse the index to find the value of the primary 4 12 key. 12 0xACDC 2 6 10 14 1 3 5 7 9 11 13 15 4 0xACDC 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. www.percona.com 101
What is a clustered index (cont.) ★ A secondary key lookup looks like this: extension_number 8 Traverse the index to find the value of the primary 4 12 key. 12 0xACDC Traverse the primary key to find the full row. 2 6 10 14 1 3 5 7 9 11 13 15 4 0xACDC 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. www.percona.com 101
Clustered Index (cont.) This design has some interesting consequences: Primary key lookups are very fast. Inserting data in order is fast - out of order can be very slow, and cause fragmentation. Secondary indexes can become very large if you have a large primary key. www.percona.com 102
Clustered Index (cont.) In practical terms this means: Don’t use GUIDs for InnoDB tables! Never piggy-back the primary key index into the end of a composite index or covering index - it is already included for free. www.percona.com 103
Some MySQL(InnoDB) Specifics Primary Keys: Always specify one. Keep it short. Try and make it your primary access method. Keep insertion incremental. Composite Keys: Don’t ever include the primary key index as part of a covering index. www.percona.com 104
Our results (typical case) Inserting 250K ‘Real’ Names CREATE TABLE uuid_users ( PRIMARY KEY, 130 emailaddress varchar(100), firstname varchar(20), lastname varchar(20), birthday varchar(10), 98 occupation varchar(70), INDEX(emailaddress), INDEX(lastname, firstname), 65 INDEX(occupation) ) ENGINE=InnoDB; 33 The UUID primary 0 key makes Data Size (in MB) the table about 65% Integer UUID larger. www.percona.com 105
Our results (worst case) CREATE TABLE mydata ( Inserting Random Integers PRIMARY KEY, col1 INT NOT NULL, col2 INT NOT NULL, 300 col3 INT NOT NULL, col4 INT NOT NULL, col5 INT NOT NULL, 225 INDEX (col1), INDEX (col2), INDEX (col3), 150 INDEX (col4), INDEX (col5) ) ENGINE=InnoDB; 75 The UUID primary 0 Data Size (in MB) key makes the table almost x3! Integer UUID www.percona.com 106
Hot column on a wide table CREATE TABLE users ( ID INTEGER, first_name VARCHAR(60), last_name VARCHAR(60), email VARCHAR(100), .. phone_number varchar(20), last_login_date DATE ); www.percona.com 107
Hot column on a wide table Solutions & Workarounds: Move user_id and last_login_date to another table (good for reads and writes). Use a covering index (better for situations where read heavy). www.percona.com 108
Hot column on a wide table Another example of this problem is with the ‘view count’ on an item. For this, writing to memcached and only pushing down to MySQL on every nth write may be required. Denormalization might not buy you enough time. www.percona.com 109
Over-indexed tables Infrequently used indexes can be responsible for decreasing write capacity. more data in buffer pool more disk IO more time to update For reads, the optimizer has more choices to make and a more difficult decision process. www.percona.com 110
Under-indexed Tables Under-indexed tables can result in too many rows needing to be examined after an index has been used - or in the worst case, no index used. This can cause contention on what contents you are able to keep in memory - and it will likely increase the size of your working set. www.percona.com 111
What makes a good schema? www.percona.com 112
What is good? It all depends on the queries you send to it. i.e. if you can’t add a very effective index, you need to make changes. www.percona.com 113
Best way to Design Schema Use a program where you can map out each of the objects on an ER diagram. i.e. MySQL Workbench. Think ahead and see if there are any common access patterns which do not fit well. i.e. I always want to know the total amount of the invoice without having to sum up all the invoice items. Export the ER diagram to SQL. www.percona.com 114
Can you make schema better? It is very hard to retrofit into an Application. For some obvious bad-choices, the ‘band aid’ approach may work. This command shows the most optimal data type: SELECT * FROM title PROCEDURE ANALYSE(1,1)G www.percona.com 115
PROCEDURE ANALYZE *************************** 1. row *************************** Field_name: imdb.title.id Min_value: 1 Max_value: 1543720 Min_length: 1 Max_length: 7 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 771860.5411 Std: 891266.7873 Optimal_fieldtype: MEDIUMINT(7) UNSIGNED NOT NULL *************************** 2. row *************************** Field_name: imdb.title.title Min_value: # 1997 Honda Accord: Gauges Upgrade - Max_value: Þröng sýn Min_length: 1 Max_length: 334 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 16.4844 Std: NULL Optimal_fieldtype: TEXT NOT NULL www.percona.com 116
MySQL Query Optimization Basic Query Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 117
Identifying Bad Queries Slowlog Logging in the Application *-Proxy MySQL Query Analyzer http://www.mysql.com/products/enterprise/query.html pt-query-digest http://www.percona.com/doc/percona-toolkit/2.0/pt-query- digest.html www.percona.com 118
pt-query-digest generate reports from slow query log pt-query-digest /path/to/slow.log binlog files processlist postgresql log files general log (not so useful) tcpdump files that captured traffic from: mysql, memcached, http store reports in db: --review,--review-history enhanced filtering capabilities '$event->{fingerprint} =~ m/^select/' www.percona.com 119
pt-query-digest # 834.7s user time, 9.1s system time, 302.78M rss, 392.96M vsz # Current date: Mon Nov 29 09:47:43 2010 # Hostname: servername # Files: STDIN # Overall: 670.66k total, 1.73k unique, 955.33 QPS, 3.08x concurrency ____ # Time range: 2010-11-29 09:14:29.955239 to 09:26:11.979320 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 2163s 0 3s 3ms 2ms 29ms 89us # Rows affecte 18.58k 0 146 0.03 0 0.49 0 # Query size 121.29M 6 21.55k 189.64 363.48 328.74 97.36 # Warning coun 438.18k 0 25.60k 0.67 0 122.19 0 # Boolean: # No good inde 0% yes, 99% no # No index use 10% yes, 89% no www.percona.com 120
# Profile # Rank Query ID Response time Calls R/Call Apdx V/M Item mk-query-digest # ==== ================== ================ ====== ======== ==== ===== ==== # 1 0x3928FBFF36663F33 1349.6240 62.4% 11976 0.1127 1.00 0.03 SELECT loan_officer_states # 2 0x8A539A15CDC891EB 114.9014 5.3% 437 0.2629 1.00 0.50 SELECT processing_assigned # 3 0xFA5D75AB1925777C 92.9441 4.3% 791 0.1175 1.00 0.06 SELECT security_dashboard # 4 0x6F1DB5CAB019DB16 77.5712 3.6% 43 1.8040 0.65 0.73 SELECT # 5 0xDFEC78D47187A0CD 67.1673 3.1% 296 0.2269 1.00 0.17 SELECT history assigned # 6 0x5D51E5F01B88B79E 49.0330 2.3% 15630 0.0031 1.00 0.00 ADMIN CONNECT # 7 0xD704F6F4D36804AB 43.4990 2.0% 274 0.1588 1.00 0.12 SELECT user_agents # 8 0x7EC8CF8EAFC26907 30.0898 1.4% 416 0.0723 1.00 0.07 SELECT security_dashboard # 9 0x599BEF84DBA12853 19.6506 0.9% 13424 0.0015 1.00 0.01 UPDATE user_sessions # 10 0x19EE1A1A48A2B249 18.8828 0.9% 54835 0.0003 1.00 0.00 SELECT leads contact_info # 11 0xDD930BC5FC65A135 18.6386 0.9% 54975 0.0003 1.00 0.00 SELECT history # 12 0x277A0E5B9646746B 16.2016 0.7% 55280 0.0003 1.00 0.00 SELECT history # 13 0x522C69BD415338C6 13.5388 0.6% 300 0.0451 1.00 0.02 SELECT history assigned # 14 0xA018F3BA9E66B42B 13.5138 0.6% 41 0.3296 1.00 0.00 SELECT new_rate_locks # 15 0x59F9E8645FFF4A16 12.7311 0.6% 55331 0.0002 1.00 0.00 SELECT realtor_leads # 16 0xEE18B363E8DB0222 10.6596 0.5% 161 0.0662 1.00 0.11 SELECT # 17 0xDF78E27C3290E5F2 10.2883 0.5% 345 0.0298 1.00 0.01 SELECT history lo_history # 18 0x0C82802FC73439D3 10.0459 0.5% 9 1.1162 0.67 0.20 SELECT users help_history # 19 0x5462226BD2AF82D9 7.1391 0.3% 75 0.0952 1.00 0.16 SELECT tasks task_note # 20 0x177159F6BEA4126A 6.7048 0.3% 55342 0.0001 1.00 0.01 SELECT nb_alert_notes # MISC 0xMISC 179.8054 8.3% 350684 0.0005 NS 0.0 <1713 ITEMS> www.percona.com 121
pt-query-digest # Query 1: 17.06 QPS, 1.92x concurrency, ID 0x3928FBFF36663F33 at byte 141746 # This item is included in the report because it matches --limit. # Scores: Apdex = 1.00 [1.0], V/M = 0.03 # Time range: 2010-11-29 09:14:30.052415 to 09:26:11.914796 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 11976 # Exec time 62 1350s 25ms 395ms 113ms 219ms 54ms 91ms # Rows affecte 0 39 0 35 0.00 0 0.32 0 # Query size 23 28.75M 2.46k 2.46k 2.46k 2.38k 0 2.38k # Warning coun 11 51.51k 0 12.80k 4.40 0 233.99 0 # Boolean: # No index use 99% yes, 0% no # String: # Databases # Errors none (273/99%), #1064 (1/0%) # Hosts 172.20.101.178 # Users dbuser www.percona.com 122
pt-query-digest # Query_time distribution # 1us # 10us #################################### # 100us ########### # 1ms ## # 10ms # # 100ms #################################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS LIKE 'user_agents'G # SHOW CREATE TABLE `user_agents`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT user_agent_id, search_engine FROM user_agents WHERE user_agent='Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.0.3705)'G www.percona.com 123
# Item 1: 3.41 QPS, 0.97x concurrency, ID 0xABCE5AD2A2DD1BA1 at byte 28812466 # This item is included in the report because it matches --limit. # Scores: Apdex = 0.97 [1.0], V/M = 19.02 # # mk-query-digest Query_time sparkline: | ^______| Time range: 2011-04-05 16:12:13 to 16:14:45 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 519 # Exec time 2 148s 11us 33s 285ms 53ms 2s 26us # Lock time 0 5ms 0 334us 9us 66us 32us 0 # Rows sent 0 41 0 1 0.08 0.99 0.27 0 # Rows examine 1 4.97M 0 445.49k 9.80k 5.73k 49.33k 0 # Rows affecte 0 2 0 1 0.00 0 0.06 0 # Rows read 1 2.01M 0 250.47k 3.96k 1.96 27.94k 0.99 # Bytes sent 0 241.20k 11 8.01k 475.89 918.49 689.98 258.32 # Merge passes 0 0 0 0 0 0 0 0 # Tmp tables 0 15 0 1 0.03 0 0.17 0 # Tmp disk tbl 0 3 0 1 0.01 0 0.08 0 # Tmp tbl size 0 4.78k 0 4.78k 9.43 0 211.60 0 # Query size 0 100.95k 19 2.71k 199.17 363.48 206.60 151.03 # InnoDB: # IO r bytes 0 0 0 0 0 0 0 0 # IO r ops 0 0 0 0 0 0 0 0 # IO r wait 0 0 0 0 0 0 0 0 # pages distin 1 67.99k 0 10.64k 1.26k 3.88k 2.47k 31.70 # queue wait 0 0 0 0 0 0 0 0 # rec lock wai 0 0 0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 7% yes, 92% no # QC Hit 78% yes, 21% no # Tmp table 2% yes, 97% no # Tmp table on 0% yes, 99% no www.percona.com 124
pt-query-digest # Tmp tbl size 0 4.78k 0 4.78k 9.43 0 211.60 0 # Query size 0 100.95k 19 2.71k 199.17 363.48 206.60 151.03 # InnoDB: # IO r bytes 0 0 0 0 0 0 0 0 # IO r ops 0 0 0 0 0 0 0 0 # IO r wait 0 0 0 0 0 0 0 0 # pages distin 1 67.99k 0 10.64k 1.26k 3.88k 2.47k 31.70 # queue wait 0 0 0 0 0 0 0 0 # rec lock wai 0 0 0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 7% yes, 92% no # QC Hit 78% yes, 21% no # Tmp table 2% yes, 97% no # Tmp table on 0% yes, 99% no www.percona.com 125
Is It Worth it? There’s always room for improvement Ask yourself: Is the change going to have a benefit? How much effort does it take to get how much gain? Benchmark! Instrument! http://www.percona.com/redir/files/white-papers/goal-driven-performance-optimization.pdf www.percona.com 126
MySQL Query Optimization Basic Query Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 127
Kenny Gryp <kenny.gryp@percona.com> @gryp We're Hiring! www.percona.com/about-us/careers/

More Related Content

PDF
How to Design Indexes, Really
byKarwin Software Solutions LLC
 
PDF
Advanced MySQL Query Tuning
byAlexander Rubin
 
PDF
Query Optimization with MySQL 5.6: Old and New Tricks - Percona Live London 2013
byJaime Crespo
 
PDF
More mastering the art of indexing
byYoshinori Matsunobu
 
PPTX
MySQL Indexing - Best practices for MySQL 5.6
byMYXPLAIN
 
PDF
MySQL Query And Index Tuning
byManikanda kumar
 
PDF
MySQL/MariaDB Proxy Software Test
byI Goo Lee
 
PPTX
Mongo DB 성능최적화 전략
byJin wook
 
How to Design Indexes, Really
byKarwin Software Solutions LLC
 
Advanced MySQL Query Tuning
byAlexander Rubin
 
Query Optimization with MySQL 5.6: Old and New Tricks - Percona Live London 2013
byJaime Crespo
 
More mastering the art of indexing
byYoshinori Matsunobu
 
MySQL Indexing - Best practices for MySQL 5.6
byMYXPLAIN
 
MySQL Query And Index Tuning
byManikanda kumar
 
MySQL/MariaDB Proxy Software Test
byI Goo Lee
 
Mongo DB 성능최적화 전략
byJin wook
 

What's hot

ODP
PostgreSQL Administration for System Administrators
byCommand Prompt., Inc
 
PDF
MySQL Indexing : Improving Query Performance Using Index (Covering Index)
byHemant Kumar Singh
 
PDF
Performance tuning in sql server
byAntonios Chatzipavlis
 
DOCX
MySQL_SQL_Tunning_v0.1.3.docx
byNeoClova
 
PPTX
Spark Shuffle Deep Dive (Explained In Depth) - How Shuffle Works in Spark
byBo Yang
 
PDF
Apache Calcite Tutorial - BOSS 21
byStamatis Zampetakis
 
PDF
MySQL: Indexing for Better Performance
byjkeriaki
 
PDF
MySQL 상태 메시지 분석 및 활용
byI Goo Lee
 
PPTX
Maria db 이중화구성_고민하기
byNeoClova
 
PPTX
Postgresql Database Administration Basic - Day1
byPoguttuezhiniVP
 
PPTX
Indexing with MongoDB
byMongoDB
 
PDF
How to Analyze and Tune MySQL Queries for Better Performance
byoysteing
 
PDF
AWS 환경에서 MySQL BMT
byI Goo Lee
 
PDF
MyRocks Deep Dive
byYoshinori Matsunobu
 
PDF
How does PostgreSQL work with disks: a DBA's checklist in detail. PGConf.US 2015
byPostgreSQL-Consulting
 
PPTX
Optimizing MySQL Queries
byAchievers Tech
 
PDF
Cassandra Introduction & Features
byDataStax Academy
 
PPTX
MySQL_MariaDB-성능개선-202201.pptx
byNeoClova
 
PPTX
Microsoft SQL Server internals & architecture
byKevin Kline
 
PDF
What is in a Lucene index?
bylucenerevolution
 
PostgreSQL Administration for System Administrators
byCommand Prompt., Inc
 
MySQL Indexing : Improving Query Performance Using Index (Covering Index)
byHemant Kumar Singh
 
Performance tuning in sql server
byAntonios Chatzipavlis
 
MySQL_SQL_Tunning_v0.1.3.docx
byNeoClova
 
Spark Shuffle Deep Dive (Explained In Depth) - How Shuffle Works in Spark
byBo Yang
 
Apache Calcite Tutorial - BOSS 21
byStamatis Zampetakis
 
MySQL: Indexing for Better Performance
byjkeriaki
 
MySQL 상태 메시지 분석 및 활용
byI Goo Lee
 
Maria db 이중화구성_고민하기
byNeoClova
 
Postgresql Database Administration Basic - Day1
byPoguttuezhiniVP
 
Indexing with MongoDB
byMongoDB
 
How to Analyze and Tune MySQL Queries for Better Performance
byoysteing
 
AWS 환경에서 MySQL BMT
byI Goo Lee
 
MyRocks Deep Dive
byYoshinori Matsunobu
 
How does PostgreSQL work with disks: a DBA's checklist in detail. PGConf.US 2015
byPostgreSQL-Consulting
 
Optimizing MySQL Queries
byAchievers Tech
 
Cassandra Introduction & Features
byDataStax Academy
 
MySQL_MariaDB-성능개선-202201.pptx
byNeoClova
 
Microsoft SQL Server internals & architecture
byKevin Kline
 
What is in a Lucene index?
bylucenerevolution
 

Viewers also liked

PPTX
How (not) to kill your MySQL infrastructure
byMiklos Szel
 
PDF
Zurich2007 MySQL Query Optimization
byHiệp Lê Tuấn
 
PPT
Building High Performance MySql Query Systems And Analytic Applications
byguest40cda0b
 
PDF
MySQL Query Tuning for the Squeemish -- Fossetcon Orlando Sep 2014
byDave Stokes
 
PDF
56 Query Optimization
byMYXPLAIN
 
PDF
MYSQL Query Anti-Patterns That Can Be Moved to Sphinx
byPythian
 
PDF
Mysql query optimization
byBaohua Cai
 
PDF
Query Optimization with MySQL 5.6: Old and New Tricks
byMYXPLAIN
 
PPTX
Tunning sql query
byvuhaininh88
 
PDF
MySQL Query tuning 101
bySveta Smirnova
 
PDF
Advanced MySQL Query and Schema Tuning
byMYXPLAIN
 
PDF
MySQL Query Optimization
byMorgan Tocker
 
PPT
My sql optimization
byPrelovacMedia
 
PDF
Webinar 2013 advanced_query_tuning
by晓 周
 
PDF
MySQL Query Optimization.
byRemote MySQL DBA
 
PDF
ProxySQL - High Performance and HA Proxy for MySQL
byRené Cannaò
 
PDF
Query Optimization with MySQL 5.7 and MariaDB 10: Even newer tricks
byJaime Crespo
 
PDF
MySQL Query Optimization (Basics)
byKarthik .P.R
 
PDF
Sql query patterns, optimized
byKarwin Software Solutions LLC
 
PDF
PostgreSQL: Data analysis and analytics
byHans-Jürgen Schönig
 
How (not) to kill your MySQL infrastructure
byMiklos Szel
 
Zurich2007 MySQL Query Optimization
byHiệp Lê Tuấn
 
Building High Performance MySql Query Systems And Analytic Applications
byguest40cda0b
 
MySQL Query Tuning for the Squeemish -- Fossetcon Orlando Sep 2014
byDave Stokes
 
56 Query Optimization
byMYXPLAIN
 
MYSQL Query Anti-Patterns That Can Be Moved to Sphinx
byPythian
 
Mysql query optimization
byBaohua Cai
 
Query Optimization with MySQL 5.6: Old and New Tricks
byMYXPLAIN
 
Tunning sql query
byvuhaininh88
 
MySQL Query tuning 101
bySveta Smirnova
 
Advanced MySQL Query and Schema Tuning
byMYXPLAIN
 
MySQL Query Optimization
byMorgan Tocker
 
My sql optimization
byPrelovacMedia
 
Webinar 2013 advanced_query_tuning
by晓 周
 
MySQL Query Optimization.
byRemote MySQL DBA
 
ProxySQL - High Performance and HA Proxy for MySQL
byRené Cannaò
 
Query Optimization with MySQL 5.7 and MariaDB 10: Even newer tricks
byJaime Crespo
 
MySQL Query Optimization (Basics)
byKarthik .P.R
 
Sql query patterns, optimized
byKarwin Software Solutions LLC
 
PostgreSQL: Data analysis and analytics
byHans-Jürgen Schönig
 

Similar to Percona Live 2012PPT: MySQL Query optimization

PPTX
Работа с индексами - лучшие практики для MySQL 5.6, Петр Зайцев (Percona)
byOntico
 
PDF
MySQL optimisation Percona LeMug.fr
bycyruss666
 
PPTX
Alkin Tezuysal "MySQL Query Optimization Best Practices and Indexing"
byFwdays
 
PPTX
Mysql query optimization best practices and indexing
byAlkin Tezuysal
 
PDF
How to Design Indexes, Really
byMYXPLAIN
 
PDF
Introduction into MySQL Query Tuning for Dev[Op]s
bySveta Smirnova
 
PDF
query optimization
byDimara Hakim
 
PDF
MySQL 优化
byYUCHENG HU
 
PDF
Scaling MySQL Strategies for Developers
byJonathan Levin
 
PDF
Tools and Techniques for Index Design
byMYXPLAIN
 
ODP
San diegophp
byDave Stokes
 
PDF
U C2007 My S Q L Performance Cookbook
byguestae36d0
 
PDF
Quick Wins
byHighLoad2009
 
PDF
Fosdem 2012 practical_indexing
byscombaudon
 
PDF
MySQL Query Optimisation 101
byFederico Razzoli
 
PDF
Zurich2007 MySQL Query Optimization
byHiệp Lê Tuấn
 
PDF
Percona toolkit
byKarwin Software Solutions LLC
 
PDF
Practical my sql performance optimization
byMarian Marinov
 
PDF
Goldilocks and the Three MySQL Queries
byDave Stokes
 
PPT
MySQL Performance Secrets
byOSSCube
 
Работа с индексами - лучшие практики для MySQL 5.6, Петр Зайцев (Percona)
byOntico
 
MySQL optimisation Percona LeMug.fr
bycyruss666
 
Alkin Tezuysal "MySQL Query Optimization Best Practices and Indexing"
byFwdays
 
Mysql query optimization best practices and indexing
byAlkin Tezuysal
 
How to Design Indexes, Really
byMYXPLAIN
 
Introduction into MySQL Query Tuning for Dev[Op]s
bySveta Smirnova
 
query optimization
byDimara Hakim
 
MySQL 优化
byYUCHENG HU
 
Scaling MySQL Strategies for Developers
byJonathan Levin
 
Tools and Techniques for Index Design
byMYXPLAIN
 
San diegophp
byDave Stokes
 
U C2007 My S Q L Performance Cookbook
byguestae36d0
 
Quick Wins
byHighLoad2009
 
Fosdem 2012 practical_indexing
byscombaudon
 
MySQL Query Optimisation 101
byFederico Razzoli
 
Zurich2007 MySQL Query Optimization
byHiệp Lê Tuấn
 
Percona toolkit
byKarwin Software Solutions LLC
 
Practical my sql performance optimization
byMarian Marinov
 
Goldilocks and the Three MySQL Queries
byDave Stokes
 
MySQL Performance Secrets
byOSSCube
 

More from mysqlops

PDF
The simplethebeautiful
bymysqlops
 
PPT
Oracle数据库分析函数详解
bymysqlops
 
PDF
Percona Live 2012PPT：mysql-security-privileges-and-user-management
bymysqlops
 
PDF
Percona Live 2012PPT： introduction-to-mysql-replication
bymysqlops
 
PDF
Percona Live 2012PPT: MySQL Cluster And NDB Cluster
bymysqlops
 
PDF
Pldc2012 innodb architecture and internals
bymysqlops
 
PPSX
DBA新人的述职报告
bymysqlops
 
PDF
分布式爬虫
bymysqlops
 
PPSX
MySQL应用优化实践
bymysqlops
 
PPT
eBay EDW元数据管理及应用
bymysqlops
 
PPT
基于协程的网络开发框架的设计与实现
bymysqlops
 
PPT
eBay基于Hadoop平台的用户邮件数据分析
bymysqlops
 
PPSX
对MySQL DBA的一些思考
bymysqlops
 
PPT
QQ聊天系统后台架构的演化与启示
bymysqlops
 
PPT
腾讯即时聊天IM1.4亿在线背后的故事
bymysqlops
 
PDF
分布式存储与TDDL
bymysqlops
 
PDF
MySQL数据库生产环境维护
bymysqlops
 
PDF
Memcached
bymysqlops
 
PDF
DevOPS
bymysqlops
 
PDF
MySQL数据库开发的三十六条军规
bymysqlops
 
The simplethebeautiful
bymysqlops
 
Oracle数据库分析函数详解
bymysqlops
 
Percona Live 2012PPT：mysql-security-privileges-and-user-management
bymysqlops
 
Percona Live 2012PPT： introduction-to-mysql-replication
bymysqlops
 
Percona Live 2012PPT: MySQL Cluster And NDB Cluster
bymysqlops
 
Pldc2012 innodb architecture and internals
bymysqlops
 
DBA新人的述职报告
bymysqlops
 
分布式爬虫
bymysqlops
 
MySQL应用优化实践
bymysqlops
 
eBay EDW元数据管理及应用
bymysqlops
 
基于协程的网络开发框架的设计与实现
bymysqlops
 
eBay基于Hadoop平台的用户邮件数据分析
bymysqlops
 
对MySQL DBA的一些思考
bymysqlops
 
QQ聊天系统后台架构的演化与启示
bymysqlops
 
腾讯即时聊天IM1.4亿在线背后的故事
bymysqlops
 
分布式存储与TDDL
bymysqlops
 
MySQL数据库生产环境维护
bymysqlops
 
Memcached
bymysqlops
 
DevOPS
bymysqlops
 
MySQL数据库开发的三十六条军规
bymysqlops
 

Recently uploaded

PDF
[BDD 2025 - Artificial Intelligence] Building AI Systems That Users (and Comp...
byWintari Yasmin
 
PDF
[BDD 2025 - Mobile Development] Mobile Engineer and Software Engineer: Are we...
bywintari3
 
PDF
PCCC25（設立25年記念PCクラスタシンポジウム）：エヌビディア合同会社 テーマ２「NVIDIA BlueField-4 DPU」
byPC Cluster Consortium
 
PDF
Cheryl Hung, Vibe Coding Auth Without Melting Down! isaqb Software Architectu...
byCheryl Hung
 
PPTX
Connecting the unconnectable: Exploring LoRaWAN for IoT
byasasiraarthur
 
PPTX
Support, Monitoring, Continuous Improvement & Scaling Agentic Automation [3/3]
byUiPathCommunity
 
PPTX
UFCD 0797 - SISTEMAS OPERATIVOS_Unidade Completa.pptx
byscribddobruno
 
PDF
Lets Build a Serverless Function with Kiro
byChris Bingham
 
PDF
[BDD 2025 - Full-Stack Development] The Modern Stack: Building Web & AI Appli...
byWintari Yasmin
 
PDF
How Much Does It Cost to Build an eCommerce Website in 2025.pdf
byPixlogix Infotech
 
PDF
10 Best Automation QA Testing Software Tools in 2025.pdf
byRohitBhandari66
 
PDF
[DevFest Strasbourg 2025] - NodeJs Can do that !!
bymoassiongbon
 
PDF
Crane Accident Prevention Guide: Key OSHA Regulations for Safer Operations
bySharpEagle Technology
 
PDF
Integrating AI with Meaningful Human Collaboration
byCadabra Studio
 
PDF
Open Source Post-Quantum Cryptography - Matt Caswell
byAll Things Open
 
PDF
[BDD 2025 - Full-Stack Development] Digital Accessibility: Why Developers nee...
bywintari3
 
PDF
5 Common Supply Chain Attacks and How They Work | CyberPro Magazine
byCyberPro Magazine
 
PDF
How Much Does It Cost To Build Software
bycollinmishell2
 
PDF
[BDD 2025 - Full-Stack Development] Agentic AI Architecture: Redefining Syste...
byWintari Yasmin
 
PPTX
Guardrails in Action - Ensuring Safe AI with Azure AI Content Safety.pptx
byUdaiappa Ramachandran
 
[BDD 2025 - Artificial Intelligence] Building AI Systems That Users (and Comp...
byWintari Yasmin
 
[BDD 2025 - Mobile Development] Mobile Engineer and Software Engineer: Are we...
bywintari3
 
PCCC25（設立25年記念PCクラスタシンポジウム）：エヌビディア合同会社 テーマ２「NVIDIA BlueField-4 DPU」
byPC Cluster Consortium
 
Cheryl Hung, Vibe Coding Auth Without Melting Down! isaqb Software Architectu...
byCheryl Hung
 
Connecting the unconnectable: Exploring LoRaWAN for IoT
byasasiraarthur
 
Support, Monitoring, Continuous Improvement & Scaling Agentic Automation [3/3]
byUiPathCommunity
 
UFCD 0797 - SISTEMAS OPERATIVOS_Unidade Completa.pptx
byscribddobruno
 
Lets Build a Serverless Function with Kiro
byChris Bingham
 
[BDD 2025 - Full-Stack Development] The Modern Stack: Building Web & AI Appli...
byWintari Yasmin
 
How Much Does It Cost to Build an eCommerce Website in 2025.pdf
byPixlogix Infotech
 
10 Best Automation QA Testing Software Tools in 2025.pdf
byRohitBhandari66
 
[DevFest Strasbourg 2025] - NodeJs Can do that !!
bymoassiongbon
 
Crane Accident Prevention Guide: Key OSHA Regulations for Safer Operations
bySharpEagle Technology
 
Integrating AI with Meaningful Human Collaboration
byCadabra Studio
 
Open Source Post-Quantum Cryptography - Matt Caswell
byAll Things Open
 
[BDD 2025 - Full-Stack Development] Digital Accessibility: Why Developers nee...
bywintari3
 
5 Common Supply Chain Attacks and How They Work | CyberPro Magazine
byCyberPro Magazine
 
How Much Does It Cost To Build Software
bycollinmishell2
 
[BDD 2025 - Full-Stack Development] Agentic AI Architecture: Redefining Syste...
byWintari Yasmin
 
Guardrails in Action - Ensuring Safe AI with Azure AI Content Safety.pptx
byUdaiappa Ramachandran
 

Percona Live 2012PPT: MySQL Query optimization

  • 1.
    MySQL Query Optimization Kenny Gryp <kenny.gryp@percona.com> Percona Live Washington DC / 2012-01-11
  • 2.
    MySQL Query Optimization BasicQuery Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 2
  • 3.
    MySQL Query Optimization Thenumber one goal is to have faster queries. The process is: We first ask MySQL what its intended execution plan is. If we don't like it, we make a change, and try again... More Information: High Performance MySQL, 2nd Edition: http://shop.oreilly.com/product/9780596101718.do EXPLAIN! http://dev.mysql.com/doc/refman/5.5/en/using- explain.html www.percona.com 3
  • 4.
    MySQL Query Optimization BasicQuery Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 4
  • 5.
    First Example www.percona.com 5
  • 6.
    Find the TitleBambi 3.09s www.percona.com 6
  • 7.
    Find the TitleBambi ALL means tablescan In this case a sort is required because of the order by, but not for all rows, only matching rows Additional filtering may be possible before passing to sort. 3.09s www.percona.com 7
  • 8.
    Specify Index Length www.percona.com 8
  • 9.
    Size of Key? Lengthof index is limited: 1000 bytes MyISAM 767 bytes InnoDB UTF-8 uses up to 3 bytes (3 bytes are used in determining) Variable length strings (VARCHAR, TEXT...): add 1 or 2 bytes for length www.percona.com 9
  • 10.
    We must revisit... Much Faster! 0.00s www.percona.com 10
  • 11.
    We must revisit... Using = for comparison, but not primary key lookup. Identified title as a candidate index, chose to use it. Size of the index used (in bytes) Anticipated number of rows to be examined dropped considerably. 0.00s www.percona.com 11
  • 12.
    Other ways ofaccessing Better type of At most one matching row. In InnoDB the primary key is often much faster than all other keys. 0.00s www.percona.com 12
  • 13.
    Range Scan Type is range. BETWEEN, IN() and < > are also ranges. Anticipated number of rows to be examined has increased - we are not specific enough. Ignore the time with EXPLAIN. Only look at the time for a query. 0.00s www.percona.com 13
  • 14.
    Why’s that arange? We're looking for titles between BambA and BambZ* When we say index in MySQL, we mean trees. That is, B-Tree/B+Tree/T-Tree. Pretend they're all the same (for simplification) There's no radically different indexing methods in MySQL unless you play storage engine Bingo**. * In reality the range is a little wider ** The memory & ndb storage engine supports hash indexes www.percona.com 14
  • 15.
    What’s that? www.percona.com 15
  • 16.
    Could this bea range? 3.2s www.percona.com 16
  • 17.
    No, we can’ttraverse. Do we head left or right here? www.percona.com 17
  • 18.
    LIKE ‘Z%’ 0.05s www.percona.com 18
  • 19.
    LIKE ‘T%’ 3.13s www.percona.com 19
  • 20.
    LIKE ‘The %’ 3.07s www.percona.com 20
  • 21.
    MySQL is (reasonably)smart. It dynamically samples the data to choose which is the better choice - or in some cases uses static statistics*. This helps the optimizer choose: Which indexes will be useful. Which indexes should be avoided. Which is the better index when there is more than one. * To refresh statistics run ANALYZE TABLE table_name; www.percona.com 21
  • 22.
    Why avoid indexes? B-Treeswork like humans search a phone book; Use an index if you want just a few rows. Scan cover-to-cover if you want a large percentage. www.percona.com 22
  • 23.
    Why avoid indexes(cont.) We benchmarked this on a different schema: Table scan has a relatively fixed cost (red line). The index has completely different effectiveness depending on how much it can filter. Hopefully MySQL switches at the right point. www.percona.com 23
  • 24.
    What you shouldtake away: Data is absolutely critical. Development environments should contain sample data exported from production systems. Input values are absolutely critical. Between two seemingly identical queries, execution plans may be very different. See also: http://www.mysqlperformanceblog.com/2009/10/16/how-not-to-find-unused-indexes/ www.percona.com 24
  • 25.
    Anticipated Number OfRows This number of rows is a guess. It keeps changing between examples. 3.41s www.percona.com 25
  • 26.
    Statistics Sampling InnoDBonly keeps statistics samples in memory - and not on disk*. Sampling is performed when a table is first opened, and estimates are based on an estimate from sampling 8 random pages. This number is used whether the table have 10 rows or 10 million rows. In InnoDB plugin this is now configurable with innodb_stats_sample_pages . The setting is global, and will apply to all tables. * In XtraDB 12 statistics can be retained with innodb_use_sys_stats_table=1. * MySQL 5.6: innodb_stats_persistent_sample_pages www.percona.com 26
  • 27.
    Statistics (cont.) Statistics areautomatically regenerated on most meta-data commands: SHOW TABLE STATUS Disable with innodb_stats_on_metadata=0 SHOW INDEX (5.1 and above). Information Schema commands. As well as: When the table size changes by more than 1/16th. If more than 2,000,000,000 rows have been inserted. Disable with innodb_stats_auto_update=0 (XtraDB only). www.percona.com 27
  • 28.
    Improve this Query 3.41s www.percona.com 28
  • 29.
    We’re Spoiled forChoice. www.percona.com 29
  • 30.
    Index on production_year 3.53s www.percona.com 30
  • 31.
    Might work if... 0.92s www.percona.com 31
  • 32.
    Index on title(50) 0.02s www.percona.com 32
  • 33.
    Comparing the two: ★ mysql> EXPLAIN SELECT * from title WHERE title = 'Pilot' AND production_year BETWEEN 2006 and 2009G www.percona.com 33
  • 34.
    Composite Indexes. What isbetter? INDEX py_t (production_year, title) INDEX t_py (title, production_year) www.percona.com 34
  • 35.
    Index on py_t http://www.mysqlperformanceblog.com/2010/01/09/getting-around-optimizer-limitations-with-an-in-list/ 0.02s www.percona.com 35
  • 36.
    Index on t_py 0.00s www.percona.com 36
  • 37.
    Recommendations Index overmultiple columns if it can improve filtering. i.e. GOOD: Only some pilots made between 2006-2009. BAD: All pilots made between 2006-2009. www.percona.com 37
  • 38.
    Recommendations (cont.) Don't know what order to specify the columns? RULE: Think how to filter the fastest. Use that order left to right. EXCEPTION: If there's a range (><, BETWEEN, %). Those always go to the RIGHT. After a a column is used for rangescan, groupby, you cannot use the next column in the composite index http://www.mysqlperformanceblog.com/2010/01/09/getting-around-optimizer-limitations-with-an-in-list/ www.percona.com 38
  • 39.
    A quick sidetrack... Sofar indexes have only been used for filtering. This is the most typical case - don’t forget it. There are also some other ways that MySQL can use an index To avoid having to sort. To prevent temporary tables. To avoid reading rows. .. www.percona.com 39
  • 40.
    The first exampleagain 3.13s www.percona.com 40
  • 41.
    Index prevents sort 0.00s www.percona.com 41
  • 42.
    Temporary Table www.percona.com 42
  • 43.
    “Loose index scan” ALTER TABLE title ADD INDEX py (production_year); www.percona.com 43
  • 44.
    Retrieving only limitedcolumns Query: SELECT person_id FROM cast_info WHERE person_role_id = 35721; www.percona.com 44
  • 45.
    Retrieving only limitedcolumns: ALTER TABLE cast_info ADD INDEX (person_role_id); www.percona.com 45
  • 46.
    Covering Index Optimization: ALTER TABLE cast_info ADD INDEX person_role_id_person_ id(person_role_id, www.percona.com 46
  • 47.
    Key_len in EXPLAIN key_len:152 How much bytes of the index is being used for the query? In composite indexes, can be used to determine how many columns will be used to _filter_ on. www.percona.com 47
  • 48.
    Key Length Example key_len is only for WHERE clause, not the covering part www.percona.com 48
  • 49.
    MySQL Query Optimization BasicQuery Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 49
  • 50.
    The limitations ofEXPLAIN EXPLAIN shows MySQL’s intentions - there is no post execution analysis. How many rows actually had to be sorted? Was that temporary table created on disk? Did the LIMIT 10 result in a quick match, resulting in fewer rows scanned? .. we don’t know. www.percona.com 50
  • 51.
    More Advanced Combine EXPLAINwith other MySQL diagnostics: SHOW SESSION STATUS Recommended to run before and after the query. Available in MySQL 5.0+ SHOW PROFILES Available in 5.0 (limited), 5.1. Breaks down the time taken on various steps of query execution. Huge amount of skew in any numbers it reports under Linux. Slow Query Log Extended Statistics (Percona Server) Will let you know examined rows, temp table on disk, sort on disk, how many IOPS in InnoDB etc. www.percona.com 51
  • 52.
    Find the actor that stared in the mysql-5141> EXPLAIN select STRAIGHT_JOIN count(*) as c, person_id from cast_info FORCE INDEX(person_id) inner join title on(cast_info.movie_id=title.id) where title.kind_id = 1 GROUP BY cast_info.person_id ORDERmost movies. by c DESC LIMIT 1G *************************** 1. row *************************** id: 1 select_type: SIMPLE MySQL says that only 8 table: cast_info rows were examined in 5.1.41 type: index possible_keys: NULL key: person_id key_len: 8 ref: NULL rows: 8 Extra: Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: title type: eq_ref possible_keys: PRIMARY,title_kind_id_exists key: PRIMARY key_len: 4 ref: imdb.cast_info.movie_id rows: 1 Extra: Using where 2 rows in set (0.00 sec) www.percona.com 16m52
  • 53.
    This is theoutput from 5.0.89 www.percona.com 53
  • 54.
    Double Checking “The number of times the first entry in an index was read” “The number of requests to read a row based on a key.” “The number of requests to read the next row in key order.” “The number of requests to read the next row in the data file.” “The number of requests to insert a row in a table.” http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html www.percona.com 54
  • 55.
    SHOW PROFILES SET profiling= 1; .. run query .. SHOW PROFILES; | Query_ID | Duration | Query | 1 | 211.21064300 | select STRAIGHT_JOIN count(*) as c, person_id FROM cast_info FORCE INDEX(person_id) INNER JOIN title ON (cast_info.movie_id=title.id) WHERE title.kind_id = 1 GROUP BY cast_info.person_id ORDER by c DESC LIMIT 1 | show profile for query 1; www.percona.com 55
  • 56.
    SHOW PROFILES (cont.) mysql> show profile for query 1; +------------------------------+------------+ | Status | Duration | .. +------------------------------+------------+ | Copying to tmp table | 113.862209 | | starting | 0.002133 | | converting HEAP to MyISAM | 0.200272 | | checking permissions | 0.000009 | | Copying to tmp table on disk | 96.506704 | | checking permissions | 0.000009 | | Sorting result | 0.634087 | | Opening tables | 0.000035 | | Sending data | 0.000047 | | System lock | 0.000022 | | end | 0.000006 | | init | 0.000033 | | removing tmp table | 0.004839 | | optimizing | 0.000020 | | end | 0.000016 | | statistics | 0.000032 | | query end | 0.000004 | | preparing | 0.000031 | | freeing items | 0.000064 | | Creating tmp table | 0.000032 | | logging slow query | 0.000004 | | Sorting for group | 0.000021 | | logging slow query | 0.000003 | | executing | 0.000005 | | cleaning up | 0.000006 | .. +------------------------------+------------+ 25 rows in set (0.00 sec) www.percona.com 56
  • 57.
    Slow Log Statistics SETGLOBAL long_query_time = 0; SET GLOBAL log_slow_verbosity = ‘full’; This was executed on a machine with entirely cold # Time: 100924 13:58:47 # User@Host: root[root] @ localhost [] caches. # Thread_id: 10 Schema: imdb Last_errno: 0 Killed: 0 # Query_time: 399.563977 Lock_time: 0.000110 Rows_sent: 1 Rows_examined: 46313608 Rows_affected: 0 Rows_read: 1 # Bytes_sent: 131 Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 25194923 # InnoDB_trx_id: 1403 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes # Filesort: Yes Filesort_on_disk: Yes Merge_passes: 5 # InnoDB_IO_r_ops: 1064749 InnoDB_IO_r_bytes: 17444847616 InnoDB_IO_r_wait: 26.935662 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 65329 SET timestamp=1285336727; select STRAIGHT_JOIN count(*) as c, person_id FROM cast_info FORCE INDEX(person_id) INNER JOIN title ON (cast_info.movie_id=title.id) WHERE title.kind_id = 1 GROUP BY cast_info.person_id ORDER by c DESC LIMIT 1; www.percona.com 57
  • 58.
    MySQL Query Optimization BasicQuery Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 58
  • 59.
    Join Analysis Filter out as much as possible first, you can only do this by looking at WHERE clause www.percona.com 59
  • 60.
    Join Analysis ALTER TABLE name ADD INDEX (name(50)); www.percona.com 60
  • 61.
    Join Analysis ALTER TABLE person_info ADD INDEX (person_id); www.percona.com 61
  • 62.
    The order yousee these tables mentioned is the order MySQL has decided to join on. www.percona.com 4m2s 62
  • 63.
    Filter out asmuch as possible first, you can only do this by looking at WHERE clause www.percona.com 4m2s 63
  • 64.
    First Index: mysql> ALTERTABLE char_name ADD index name_idx (name(50)); www.percona.com 64
  • 65.
    Filter out asmuch as possible first, you can only do this by looking at WHERE clause The order changed. cast_info was previously first! 1m48s www.percona.com 65
  • 66.
    Second Index: mysql> ALTERTABLE cast_info ADD INDEX person_role_id_person_id(person_role_id, person_id); www.percona.com 66
  • 67.
    TIP: Using acovering index means that we retrieve all data directly from the index. www.percona.com 0.00s 67
  • 68.
    Join Methods You needto filter as fast as possible. Here's why: MySQL only uses one join method - a nested loop join. www.percona.com 68
  • 69.
    Sample Query Findall actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
  • 70.
    Sample Query Findall actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
  • 71.
    Sample Query Findall actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
  • 72.
    Sample Query Findall actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
  • 73.
    Sample Query Findall actors that were active between 1960 and 1970: Actors: Movies: id first_name last_name id name year 1 Sean Connery 1 Dr. No 1962 2 George Lazenby 2 From Russia with Love 1963 3 Roger Moore 3 Goldfinger 1964 4 Timothy Dalton 3 You only live twice 1967 5 Pierce Brosnan 5 On Her Majesty's Secret Service 1969 6 Daniel Craig .. .. .. www.percona.com 69
  • 74.
    If that queryis common When you can’t filter enough on one table, bring some of the other filters from the other tables to the first one: Actors: id first_name last_name start_date finish_date 1 Sean Connery 1962 1971 2 George Lazenby 1969 1969 3 Roger Moore 1973 1985 4 Timothy Dalton 1987 1989 5 Pierce Brosnan 1995 2002 6 Daniel Craig 2006 2011 www.percona.com 70
  • 75.
    MySQL Query Optimization BasicQuery Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 71
  • 76.
    Subquery Analysis Will it fix it if we add an index on title.kind_id? 4.84s www.percona.com 72
  • 77.
    With index ontitle.kind_id No! It doesn’t. Why is this? 4.9s www.percona.com 73
  • 78.
    Scalar Subquery Change to using equality, it works! but only when kind is unique! 0.07s www.percona.com 74
  • 79.
    Solving via Join It’s okay to have multiple kind’s specified using this syntax. ALTER TABLE title ADD KEY (kind_id); 0.06s www.percona.com 75
  • 80.
    Should We CompletelyAvoid Them? •No, Benchmark! •“Delayed Join” http://www.mysqlperformanceblog.com/2007/04/06/using-delayed-join-to-optimize- count-and-limit-queries/ www.percona.com 76
  • 81.
    MySQL Query Optimization BasicQuery Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 77
  • 82.
    Next Problem (cont.) The problem with this schema, is there's just a couple of outliers with really long names: www.percona.com 78
  • 83.
    Two ways tosolve this: 1. Pick a good length to get a lot of uniqueness: www.percona.com 79
  • 84.
    Two ways tosolve this: 1. Pick a good length to get a lot of uniqueness: www.percona.com 80
  • 85.
    Two ways tosolve this: 1. Pick a good length to get a lot of uniqueness: 96% uniqueness, but only 20 chars instead of 300+ Looks pretty good to me: ALTER TABLE title ADD index (name(20)) www.percona.com 81
  • 86.
    Option 2: Emulatea Hash Index Is possible only with MEMORY engine: ALTER TABLE table ADD INDEX USING HASH (title); www.percona.com 82
  • 87.
    Option 2: Emulatea Hash Index A good hashing algorithm has good distribution. How good is this? www.percona.com 83
  • 88.
    Option 2: HashIndex (cont.) Query needs to be transformed slightly to: SELECT * FROM title WHERE title_crc32=crc32(‘my_title’) AND title=’my_title’; All updates/inserts need to also update the value of title_crc32: Can be easily done via the application, or a trigger if write load is very low. www.percona.com 84
  • 89.
    Pros/Cons Prefix Index: Hash Index: ★ Pro: ★ Pro: Very Good when there is not Built in to MySQL/no magic much uniqueness until very required. far into the string. ★ Cons: ★ Cons: Equality searches only. Not very effective when the Requires ugly magic to work start of the string is not very with collations/ case unique. sensitivity. www.percona.com 85
  • 90.
    Things are lookinggood!? Please don’t take away that adding indexes == only secret to performance. The story is a lot more complicated. We have to get around optimizer limitations, and a lack of index/join options. www.percona.com 86
  • 91.
    Optimizer Hints Optimizer decisionmaking is all about tradeoffs. MySQL wants to pick the best plan, but it can’t be exhaustive in deciding if it takes too long. If MySQL is off by a lot, you may want to provide a hint: USE INDEX FORCE INDEX IGNORE INDEX STRAIGHT_JOIN See: http://dev.mysql.com/doc/refman/5.5/en/index-hints.html www.percona.com 87
  • 92.
    Optimizer Hints, ShouldWe? www.percona.com 88
  • 93.
    More features &workarounds EXPLAIN only works for SELECT: convert UPDATE/ DELETE to SELECT (feature added in 5.6) The IN() list workaround http://www.mysqlperformanceblog.com/2010/01/09/getting- around-optimizer-limitations-with-an-in-list/ Index Merge http://dev.mysql.com/doc/refman/5.1/en/index-merge- optimization.html www.percona.com 89
  • 94.
    MySQL Query Optimization BasicQuery Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 90
  • 95.
    CREATE TABLE www.percona.com 91
  • 96.
    SHOW FIELDS www.percona.com 92
  • 97.
    SHOW INDEXES www.percona.com 93
  • 98.
    Day One Advice(1) Keep it simple - store atomic types in each field. This means storing first name and last name as two separate fields. Don’t try and get tricky with how you store the data. i.e. this field means it’s a phone number unless this other field is set to something. www.percona.com 94
  • 99.
    Day One Advice(2) Use appropriate data types - If you’re not sure about the length, varchar(100) is still much better than varchar(255). Use an Integer for a number. Decimal for precision numbers, float for non-precision numbers, etc. If integers don’t have to be negative, use unsigned. www.percona.com 95
  • 100.
    Day One Advice(3) Plan how you will be accessing the data. If you know that you have to do 4 expensive joins to execute a common query - it might not be the best solution. It’s okay to have redundancy in the database from very early on. One example is pre-generating the ‘average score’ on IMDB titles. www.percona.com 96
  • 101.
    Clustered Index Everything in InnoDB is an index: Data is stored in a clustered index organized by the primary key. In the absence of a primary key, the first unique not null key is selected*. Other indexes are stored in secondary indexes. * In the absence of a unique key, a hidden 6 byte key is created. www.percona.com 97
  • 102.
    What is aclustered index? First lets look at how MyISAM stores data*: Staff.MYI Staff.MYD ID First Name 8 1 lePeter 4 12 2 leVadim 2 6 10 14 7 leFred 1 3 5 7 9 11 13 15 4 leEwen Data is stored “roughly” in insertion order, with no 5 leBaron guarantees, i.e. .. .. Deleted rows may be filled * Illustrating B-Tree as Binary Tree for simplicity with newer records. www.percona.com 98
  • 103.
    What is aclustered index (cont.) A MyISAM primary key lookup looks something like this: Staff.MYI Staff.MYD ID First Name 8 1 lePeter 4 12 2 leVadim 2 6 10 14 7 leFred 1 3 5 7 9 11 13 15 4 leEwen 5 leBaron .. .. www.percona.com 99
  • 104.
    What is aclustered index (cont.) A MyISAM primary key lookup looks something like this: Staff.MYI Staff.MYD ID First Name 8 the index to find the Traverse address of the row we 1 lePeter 4 12 are looking for. 2 leVadim 2 6 10 14 7 leFred 1 3 5 7 9 11 13 15 4 leEwen 5 leBaron .. .. www.percona.com 99
  • 105.
    What is aclustered index (cont.) A MyISAM primary key lookup looks something like this: Staff.MYI Staff.MYD ID First Name 8 the index to find the Traverse address of the row we 1 lePeter 4 12 are looking for. 2 leVadim 2 6 10 14 address in the the Lookup 7 leFred 1 3 5 7 9 11 13 15data file. 4 leEwen 5 leBaron .. .. www.percona.com 99
  • 106.
    What is aclustered index (cont.) ★ An InnoDB Primary Key lookup looks like this: Staff.ibd 12 0xACDC 4 0xACDC 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. * Illustrating B+Tree as Binary Tree for simplicity. www.percona.com 100
  • 107.
    What is aclustered index (cont.) ★ An InnoDB Primary Key lookup looks like this: Staff.ibd 12 0xACDC Traverse the index to find the 4 0xACDC full row. 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. * Illustrating B+Tree as Binary Tree for simplicity. www.percona.com 100
  • 108.
    What is aclustered index (cont.) ★ An InnoDB Primary Key lookup looks like this: Staff.ibd 12 0xACDC Traverse the index to find the 4 0xACDC full row. 12 0xACDC 22 0xACDC leVadim, ..., ... 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. * Illustrating B+Tree as Binary Tree for simplicity. www.percona.com 100
  • 109.
    What is aclustered index (cont.) ★ An InnoDB Primary Key lookup looks like this: Staff.ibd 12 0xACDC Traverse the index to find the 4 0xACDC full row. 12 0xACDC Stop here. 22 0xACDC leVadim, ..., ... 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. * Illustrating B+Tree as Binary Tree for simplicity. www.percona.com 100
  • 110.
    What is aclustered index (cont.) ★ A secondary key lookup looks like this: extension_number 8 4 12 12 0xACDC 2 6 10 14 1 3 5 7 9 11 13 15 4 0xACDC 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. www.percona.com 101
  • 111.
    What is aclustered index (cont.) ★ A secondary key lookup looks like this: extension_number 8 Traverse the index to find the value of the primary 4 12 key. 12 0xACDC 2 6 10 14 1 3 5 7 9 11 13 15 4 0xACDC 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. www.percona.com 101
  • 112.
    What is aclustered index (cont.) ★ A secondary key lookup looks like this: extension_number 8 Traverse the index to find the value of the primary 4 12 key. 12 0xACDC Traverse the primary key to find the full row. 2 6 10 14 1 3 5 7 9 11 13 15 4 0xACDC 12 0xACDC 2 0xACDC 6 0xACDC 10 0xACDC 14 0xACDC 1 .. 3 .. 5 .. 7 .. 9 .. 11 .. 13 .. 15 .. www.percona.com 101
  • 113.
    Clustered Index (cont.) Thisdesign has some interesting consequences: Primary key lookups are very fast. Inserting data in order is fast - out of order can be very slow, and cause fragmentation. Secondary indexes can become very large if you have a large primary key. www.percona.com 102
  • 114.
    Clustered Index (cont.) Inpractical terms this means: Don’t use GUIDs for InnoDB tables! Never piggy-back the primary key index into the end of a composite index or covering index - it is already included for free. www.percona.com 103
  • 115.
    Some MySQL(InnoDB) Specifics PrimaryKeys: Always specify one. Keep it short. Try and make it your primary access method. Keep insertion incremental. Composite Keys: Don’t ever include the primary key index as part of a covering index. www.percona.com 104
  • 116.
    Our results (typicalcase) Inserting 250K ‘Real’ Names CREATE TABLE uuid_users ( PRIMARY KEY, 130 emailaddress varchar(100), firstname varchar(20), lastname varchar(20), birthday varchar(10), 98 occupation varchar(70), INDEX(emailaddress), INDEX(lastname, firstname), 65 INDEX(occupation) ) ENGINE=InnoDB; 33 The UUID primary 0 key makes Data Size (in MB) the table about 65% Integer UUID larger. www.percona.com 105
  • 117.
    Our results (worstcase) CREATE TABLE mydata ( Inserting Random Integers PRIMARY KEY, col1 INT NOT NULL, col2 INT NOT NULL, 300 col3 INT NOT NULL, col4 INT NOT NULL, col5 INT NOT NULL, 225 INDEX (col1), INDEX (col2), INDEX (col3), 150 INDEX (col4), INDEX (col5) ) ENGINE=InnoDB; 75 The UUID primary 0 Data Size (in MB) key makes the table almost x3! Integer UUID www.percona.com 106
  • 118.
    Hot column ona wide table CREATE TABLE users ( ID INTEGER, first_name VARCHAR(60), last_name VARCHAR(60), email VARCHAR(100), .. phone_number varchar(20), last_login_date DATE ); www.percona.com 107
  • 119.
    Hot column ona wide table Solutions & Workarounds: Move user_id and last_login_date to another table (good for reads and writes). Use a covering index (better for situations where read heavy). www.percona.com 108
  • 120.
    Hot column ona wide table Another example of this problem is with the ‘view count’ on an item. For this, writing to memcached and only pushing down to MySQL on every nth write may be required. Denormalization might not buy you enough time. www.percona.com 109
  • 121.
    Over-indexed tables Infrequentlyused indexes can be responsible for decreasing write capacity. more data in buffer pool more disk IO more time to update For reads, the optimizer has more choices to make and a more difficult decision process. www.percona.com 110
  • 122.
    Under-indexed Tables Under-indexedtables can result in too many rows needing to be examined after an index has been used - or in the worst case, no index used. This can cause contention on what contents you are able to keep in memory - and it will likely increase the size of your working set. www.percona.com 111
  • 123.
    What makes agood schema? www.percona.com 112
  • 124.
    What is good? Itall depends on the queries you send to it. i.e. if you can’t add a very effective index, you need to make changes. www.percona.com 113
  • 125.
    Best way toDesign Schema Use a program where you can map out each of the objects on an ER diagram. i.e. MySQL Workbench. Think ahead and see if there are any common access patterns which do not fit well. i.e. I always want to know the total amount of the invoice without having to sum up all the invoice items. Export the ER diagram to SQL. www.percona.com 114
  • 126.
    Can you makeschema better? It is very hard to retrofit into an Application. For some obvious bad-choices, the ‘band aid’ approach may work. This command shows the most optimal data type: SELECT * FROM title PROCEDURE ANALYSE(1,1)G www.percona.com 115
  • 127.
    PROCEDURE ANALYZE *************************** 1.row *************************** Field_name: imdb.title.id Min_value: 1 Max_value: 1543720 Min_length: 1 Max_length: 7 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 771860.5411 Std: 891266.7873 Optimal_fieldtype: MEDIUMINT(7) UNSIGNED NOT NULL *************************** 2. row *************************** Field_name: imdb.title.title Min_value: # 1997 Honda Accord: Gauges Upgrade - Max_value: Þröng sýn Min_length: 1 Max_length: 334 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 16.4844 Std: NULL Optimal_fieldtype: TEXT NOT NULL www.percona.com 116
  • 128.
    MySQL Query Optimization BasicQuery Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 117
  • 129.
    Identifying Bad Queries Slowlog Loggingin the Application *-Proxy MySQL Query Analyzer http://www.mysql.com/products/enterprise/query.html pt-query-digest http://www.percona.com/doc/percona-toolkit/2.0/pt-query- digest.html www.percona.com 118
  • 130.
    pt-query-digest generate reports from slow query log pt-query-digest /path/to/slow.log binlog files processlist postgresql log files general log (not so useful) tcpdump files that captured traffic from: mysql, memcached, http store reports in db: --review,--review-history enhanced filtering capabilities '$event->{fingerprint} =~ m/^select/' www.percona.com 119
  • 131.
    pt-query-digest # 834.7s user time, 9.1s system time, 302.78M rss, 392.96M vsz # Current date: Mon Nov 29 09:47:43 2010 # Hostname: servername # Files: STDIN # Overall: 670.66k total, 1.73k unique, 955.33 QPS, 3.08x concurrency ____ # Time range: 2010-11-29 09:14:29.955239 to 09:26:11.979320 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 2163s 0 3s 3ms 2ms 29ms 89us # Rows affecte 18.58k 0 146 0.03 0 0.49 0 # Query size 121.29M 6 21.55k 189.64 363.48 328.74 97.36 # Warning coun 438.18k 0 25.60k 0.67 0 122.19 0 # Boolean: # No good inde 0% yes, 99% no # No index use 10% yes, 89% no www.percona.com 120
  • 132.
    # Profile # RankQuery ID Response time Calls R/Call Apdx V/M Item mk-query-digest # ==== ================== ================ ====== ======== ==== ===== ==== # 1 0x3928FBFF36663F33 1349.6240 62.4% 11976 0.1127 1.00 0.03 SELECT loan_officer_states # 2 0x8A539A15CDC891EB 114.9014 5.3% 437 0.2629 1.00 0.50 SELECT processing_assigned # 3 0xFA5D75AB1925777C 92.9441 4.3% 791 0.1175 1.00 0.06 SELECT security_dashboard # 4 0x6F1DB5CAB019DB16 77.5712 3.6% 43 1.8040 0.65 0.73 SELECT # 5 0xDFEC78D47187A0CD 67.1673 3.1% 296 0.2269 1.00 0.17 SELECT history assigned # 6 0x5D51E5F01B88B79E 49.0330 2.3% 15630 0.0031 1.00 0.00 ADMIN CONNECT # 7 0xD704F6F4D36804AB 43.4990 2.0% 274 0.1588 1.00 0.12 SELECT user_agents # 8 0x7EC8CF8EAFC26907 30.0898 1.4% 416 0.0723 1.00 0.07 SELECT security_dashboard # 9 0x599BEF84DBA12853 19.6506 0.9% 13424 0.0015 1.00 0.01 UPDATE user_sessions # 10 0x19EE1A1A48A2B249 18.8828 0.9% 54835 0.0003 1.00 0.00 SELECT leads contact_info # 11 0xDD930BC5FC65A135 18.6386 0.9% 54975 0.0003 1.00 0.00 SELECT history # 12 0x277A0E5B9646746B 16.2016 0.7% 55280 0.0003 1.00 0.00 SELECT history # 13 0x522C69BD415338C6 13.5388 0.6% 300 0.0451 1.00 0.02 SELECT history assigned # 14 0xA018F3BA9E66B42B 13.5138 0.6% 41 0.3296 1.00 0.00 SELECT new_rate_locks # 15 0x59F9E8645FFF4A16 12.7311 0.6% 55331 0.0002 1.00 0.00 SELECT realtor_leads # 16 0xEE18B363E8DB0222 10.6596 0.5% 161 0.0662 1.00 0.11 SELECT # 17 0xDF78E27C3290E5F2 10.2883 0.5% 345 0.0298 1.00 0.01 SELECT history lo_history # 18 0x0C82802FC73439D3 10.0459 0.5% 9 1.1162 0.67 0.20 SELECT users help_history # 19 0x5462226BD2AF82D9 7.1391 0.3% 75 0.0952 1.00 0.16 SELECT tasks task_note # 20 0x177159F6BEA4126A 6.7048 0.3% 55342 0.0001 1.00 0.01 SELECT nb_alert_notes # MISC 0xMISC 179.8054 8.3% 350684 0.0005 NS 0.0 <1713 ITEMS> www.percona.com 121
  • 133.
    pt-query-digest # Query 1: 17.06 QPS, 1.92x concurrency, ID 0x3928FBFF36663F33 at byte 141746 # This item is included in the report because it matches --limit. # Scores: Apdex = 1.00 [1.0], V/M = 0.03 # Time range: 2010-11-29 09:14:30.052415 to 09:26:11.914796 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 11976 # Exec time 62 1350s 25ms 395ms 113ms 219ms 54ms 91ms # Rows affecte 0 39 0 35 0.00 0 0.32 0 # Query size 23 28.75M 2.46k 2.46k 2.46k 2.38k 0 2.38k # Warning coun 11 51.51k 0 12.80k 4.40 0 233.99 0 # Boolean: # No index use 99% yes, 0% no # String: # Databases # Errors none (273/99%), #1064 (1/0%) # Hosts 172.20.101.178 # Users dbuser www.percona.com 122
  • 134.
    pt-query-digest # Query_time distribution # 1us # 10us #################################### # 100us ########### # 1ms ## # 10ms # # 100ms #################################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS LIKE 'user_agents'G # SHOW CREATE TABLE `user_agents`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT user_agent_id, search_engine FROM user_agents WHERE user_agent='Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.0.3705)'G www.percona.com 123
  • 135.
    # Item 1: 3.41 QPS, 0.97x concurrency, ID 0xABCE5AD2A2DD1BA1 at byte 28812466 # This item is included in the report because it matches --limit. # Scores: Apdex = 0.97 [1.0], V/M = 19.02 # # mk-query-digest Query_time sparkline: | ^______| Time range: 2011-04-05 16:12:13 to 16:14:45 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 519 # Exec time 2 148s 11us 33s 285ms 53ms 2s 26us # Lock time 0 5ms 0 334us 9us 66us 32us 0 # Rows sent 0 41 0 1 0.08 0.99 0.27 0 # Rows examine 1 4.97M 0 445.49k 9.80k 5.73k 49.33k 0 # Rows affecte 0 2 0 1 0.00 0 0.06 0 # Rows read 1 2.01M 0 250.47k 3.96k 1.96 27.94k 0.99 # Bytes sent 0 241.20k 11 8.01k 475.89 918.49 689.98 258.32 # Merge passes 0 0 0 0 0 0 0 0 # Tmp tables 0 15 0 1 0.03 0 0.17 0 # Tmp disk tbl 0 3 0 1 0.01 0 0.08 0 # Tmp tbl size 0 4.78k 0 4.78k 9.43 0 211.60 0 # Query size 0 100.95k 19 2.71k 199.17 363.48 206.60 151.03 # InnoDB: # IO r bytes 0 0 0 0 0 0 0 0 # IO r ops 0 0 0 0 0 0 0 0 # IO r wait 0 0 0 0 0 0 0 0 # pages distin 1 67.99k 0 10.64k 1.26k 3.88k 2.47k 31.70 # queue wait 0 0 0 0 0 0 0 0 # rec lock wai 0 0 0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 7% yes, 92% no # QC Hit 78% yes, 21% no # Tmp table 2% yes, 97% no # Tmp table on 0% yes, 99% no www.percona.com 124
  • 136.
    pt-query-digest # Tmp tbl size 0 4.78k 0 4.78k 9.43 0 211.60 0 # Query size 0 100.95k 19 2.71k 199.17 363.48 206.60 151.03 # InnoDB: # IO r bytes 0 0 0 0 0 0 0 0 # IO r ops 0 0 0 0 0 0 0 0 # IO r wait 0 0 0 0 0 0 0 0 # pages distin 1 67.99k 0 10.64k 1.26k 3.88k 2.47k 31.70 # queue wait 0 0 0 0 0 0 0 0 # rec lock wai 0 0 0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 7% yes, 92% no # QC Hit 78% yes, 21% no # Tmp table 2% yes, 97% no # Tmp table on 0% yes, 99% no www.percona.com 125
  • 137.
    Is It Worthit? There’s always room for improvement Ask yourself: Is the change going to have a benefit? How much effort does it take to get how much gain? Benchmark! Instrument! http://www.percona.com/redir/files/white-papers/goal-driven-performance-optimization.pdf www.percona.com 126
  • 138.
    MySQL Query Optimization BasicQuery Tuning Beyond EXPLAIN Optimizing JOINs Subquery Other Optimizations Table Schema Identifying Bad Queries www.percona.com 127
  • 139.
    Kenny Gryp <kenny.gryp@percona.com> @gryp We're Hiring! www.percona.com/about-us/careers/