1. No category

When and How to Take
Advantage of New Optimizer
Features in MySQL 5.6
Øystein Grøvlen
Senior Principal Software Engineer, MySQL
Oracle
Program Agenda
 Improvements for disk-bound queries
 Subquery improvements
 Index condition pushdown
 Misc. optimizer improvements
2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
MySQL 5.6:
Improvements for Disk-Bound Queries
 Main idea: Sort keys retrieved from index before accessing table
 Benefits:
– Read more rows from a page while it is still in buffer pool
– Increased benefits from prefetching pages into the buffer pool
– Sequential instead of random disk access?
 Range scan:
– Disk Sweep Multi-Range Read (DS-MRR)
 Index lookup (Ref access):
– Batched Key Access
3
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
MySQL 5.5: Data Access without DS-MRR
Index
Index
scan
4
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Random access
Table
MySQL 5.6: Data Access with DS-MRR
InnoDB Example
Index
Collect
PKs in
buffer
Sweepread rows
Sort
Index
scan
PKs in
index order
5
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
PKs in
PK order
Table
MySQL 5.5 vs MySQL 5.6:
DBT-3 Queries using DS-MRR
Query Execution Time Relative to MySQL 5.5
100 %
DBT-3, Scale 10 (23 GB)
80 %
60 %
innodb_buffer_pool_size= 1 GB
(disk-bound)
40 %
read_rnd_buffer_size = 4 MB
20 %
0%
Q3
Q4
MySQL 5.5
6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Q10
Q14
MySQL 5.6
Q15
DS-MRR
Usage
 Default: Cost-based choice for tables larger than
innodb_buffer_pool_size
(Otherwise: off)
 Force MRR on:
set optimizer_switch = ’mrr_cost_based=off’;
 Force MRR off:
set optimizer_switch = ’mrr=off’;
 Configurable size for buffer used to sort keys:
read_rnd_buffer_size (Default: 256 kB)
7
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
DS-MRR
EXPLAIN
mysql> explain select l_suppkey, sum(l_extendedprice * (1 - l_discount))
from lineitem where l_shipdate >= '1996-07-01' and l_shipdate <
date_add('1996-07-01', interval '90' day) group by l_suppkey\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lineitem
type: range
possible_keys: i_l_shipdate
key: i_l_shipdate
key_len: 4
ref: NULL
rows: 4354466
Extra: Using index condition; Using MRR; Using temporary; Using
filesort
1 row in set (0.00 sec)
8
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
DS-MRR: Sort Buffer Size Matters
innodb_buffer_pool_size= 1 GB
(disk-bound)
Varying read_rnd_buffer_size
optimizer_switch settings:
MRR Off:
mrr=off
MRR Cost-based:
mrr=on,mrr_cost_based=on
MRR Always on:
mrr=on,mrr_cost_based=off
9
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
250
Query Time (Minutes)
DBT-3, Query 15
Scale 10 (23 GB)
205
200
150
100
50
4
0
0,25
0,5
MRR Off
1
2
4
8
16
MRR Sort Buffer Size (MB)
MRR Cost-based
32
MRR Always On
64
MySQL 5.6: Batched Key Access (BKA)
DS-MRR Applied to Join Buffering
Index
Table1
Collect
PKs in
buffer
Sweepread rows
Sort
Join buffer
10
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
PKs in join
buffer order
PKs in
PK order
Table2
MySQL 5.5 vs MySQL 5.6: Queries using BKA
Query Execution Time Relative to MySQL 5.5
DBT-3, Scale 10 (23 GB)
innodb_buffer_pool_size= 1 GB
(disk-bound)
join_buffer_size = 4 MB
optimizer_switch =
’batched_key_access=on,
mrr_cost_based=off’
200 %
175 %
150 %
125 %
100 %
75 %
50 %
25 %
0%
Q2
Q5
Q8
Q9 Q11 Q13 Q17 Q19 Q20 Q21
MySQL 5.5
11
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
MySQL 5.6
Batched Key Access
Usage
 Default: Off
 Force BKA on:
set optimizer_switch =
’batched_key_access=on,mrr_cost_based=off’;
 Configurable size for buffering keys to sort:
join_buffer_size (Default: 256 kB)
12
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Batched Key Access
EXPLAIN
mysql> explain select sum(l_extendedprice* (1 - l_discount)) as revenue from
lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#22’ and
l_quantity >= 6 and p_size between 1 and 5;
+----+-------------+----------+------+---------------------------------+-| id | select_type | table
| type | possible_keys
|
key
| key_len | ref
| rows
|
Extra
|
+----+-------------+----------+------+---------------------------------+-| 1 | SIMPLE
| part
| ALL | PRIMARY
|
NULL
| NULL
| NULL
| 200000 | Using
where
|
| 1 | SIMPLE
| lineitem | ref | i_l_suppkey_partkey,i_l_partkey |
i_l_suppkey_partkey | 5
| dbt3.part.p_partkey |
15 | Using where;
Using join buffer (Batched Key Access) |
+----+-------------+----------+------+---------------------------------+-2 rows in set (0.00 sec)
13
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Batched Key Access: Buffer Size Matters
DBT-3, Query 2
Scale 10 (23 GB)
innodb_buffer_pool_size= 1 GB
(disk-bound)
Varying join_buffer_size
optimizer_switch =
’batched_key_access=on,
mrr_cost_based=off’
Query Time (Minutes)
70
60
59.6
50
40
30
20
10
1.4
0
16k
64k
256k
1M
4M
Join Buffer Size (Bytes)
BKA Off
14
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
BKA On
16M
64M
MySQL 5.6: Subquery Improvements
Optimize IN subqueries
select o_orderdate, o_totalprice
from orders
where o_orderkey in (select l_orderkey
from lineitem
where l_quantity > 49);
 New optimizations in MySQL 5.6:
– Subquery Materialization
– Semi-join
15
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Subquery Materialization
1.
2.
Execute subquery and store result in a temporary table with unique
index (For quick look-up and duplicate removal.)
Execute outer query and check for matches in temporary table.
select o_orderdate, o_totalprice
from orders
where o_orderkey in (select l_orderkey
from lineitem
group by l_orderkey
Materialize
having sum(l_quantity) > 313);
16
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
MySQL 5.5 vs MySQL 5.6:
Subquery Materialization
Query Execution Time Relative to MySQL 5.5
DBT-3, Scale 10 (23 GB)
100 %
87 %
80 %
innodb_buffer_pool_size= 24 GB
(CPU-bound)
For Q20:
optimizer_switch =
’semijoin=off;
subquery_materialization_cost_
based=off’
60 %
40 %
20 %
0%
Q16
Q18:
MySQL 5.5: ~37 years?
MySQL 5.6: 69 seconds
17
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
48 %
0.000006 %
Q18
MySQL 5.5
Q20
MySQL 5.6
MySQL 5.6: Semi-join

Convert subquery to inner join, BUT
–


Different strategies for duplicate removal:
–
FirstMatch (equivalent to traditional subquery execution)
–
LooseScan (index scan, skip duplicates)
–
Materialization: MatLookup (like subquery materialization),
MatScan (materialized table is first in join order)
–
Duplicate WeedOut (insert result rows of semi-join query into temporary
table with unique index; duplicate rows will be rejected. Any join order.)
If duplicate removal is not necessary:
–
18
Need some way to remove duplicates
Table pull-out
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Semi-join, cont.

Main advantage:
–
Opens up for more optimal ”join orders”.
–
Example:
select o_orderdate, o_totalprice
from orders
where o_orderkey in (select l_orderkey
from lineitem
where l_shipDate=’1996-09-30’);
Will process less rows if starting with lineitem instead of orders

Restriction:
–
19
Cannot use semi-join if subquery contains union or aggregation
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
MySQL 5.6: Semi-join: Example 1
select o_totalprice
from orders
where o_orderkey in
(select l_orderkey
from lineitem
where l_shipdate =
'1996-09-30');
DBT-3, Scale 10 (23 GB)
innodb_buffer_pool_size= 24 GB (CPU-bound)
20
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Query Time (seconds)
70
60
57.83
50
40
30
20
10.84
10
0.06
0
0.07
Trad.
Subquery Mat.
LooseScan
DupsWeedout
MySQL 5.6: Semi-join: Example 2
DBT-3, Scale 10 (23 GB)
innodb_buffer_pool_size= 24 GB (CPU-bound)
80
Query Time (seconds)
select
sum(l_quantity*
l_extendedprice)
from lineitem
where l_orderkey in
(select o_orderkey
from orders
where o_orderdate =
'1996-09-30');
70
60
50
40
30
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
27.61
20
10
0.03
0
Trad.
21
67.02
Subquery Mat.
Table Pullout
MySQL 5.6: Semi-join: Example 3
DBT-3, Scale 10 (23 GB)
innodb_buffer_pool_size= 24 GB (CPU-bound)
14
Query Time (seconds)
select s_name, s_address
from supplier
where s_suppkey in
(select ps_suppkey
from partsupp, part
where ps_partkey=p_partkey
and p_name like 'grey%‘
and ps_availqty > 9990);
12.74
12.53 12.49
12
10
8
6
4
2
0.89 0.89
0
Trad.
Subquery Mat.
MatLookup
FirstMatch
DupsWeedout
22
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Semi-join
Usage
 Default: All IN sub-queries that do not contain aggreation or union are
converted to semi-join
 Disable semi-join conversion:
set optimizer_switch = ’semijoin=off’;
 Disable individual semi-join strategies:
set optimizer_switch = ’firstmatch=off’;
set optimizer_switch = ’loosescan=off’;
set optimizer_switch = ’materialization=off’;
 Force traditional IN-to-EXIST evaluation:
set optimizer_switch = ’semijoin=off,materialization=off’;
23
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
MySQL 5.6: Index Condition Pushdown (ICP)
DBT3 Query 6: Forecasting Revenue Change Query
Need force index to get ICP for this query
select sum(l_extendedprice * l_discount) as revenue
from lineitem force index(j_l_shipdate_discount_quantity)
where l_shipdate >= '1994-01-01'
and l_shipdate < date_add('1994-01-01’,interval '1' year)
and l_discount between 0.09 - 0.01 and 0.09 + 0.01
and l_quantity < 24;
Index range scan criteria
24
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Conditions evaluated during index scan
MySQL 5.6: Index Condition Pushdown
DBT-3, Query 6
Scale 10 (23 GB)
innodb_buffer_pool_size= 24 GB
(CPU-bound)
optimizer_switch settings:
index_condition_pushdown = on/off
Query Time (seconds)
30
25
20
15
10
5
0
25
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Table Scan
Index Scan
(ICP off)
Index Scan
(ICP on)
Index Condition Pushdown
EXPLAIN
mysql> explain select sum(l_extendedprice * l_discount) as revenue from
lineitem force index (i_l_shipdate_discount_quantity) where l_shipdate >=
'1994-01-01' and l_shipdate < date_add( '1994-01-01' , interval '1' year)
and l_discount between 0.09 - 0.01 and 0.09 + 0.01 and l_quantity < 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lineitem
type: range
possible_keys: i_l_shipdate_discount_quantity
key: i_l_shipdate_discount_quantity
key_len: 16
ref: NULL
rows: 18940908
Extra: Using index condition
1 row in set (0.00 sec)
26
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Index Condition Pushdown
EXPLAIN FORMAT=JSON
mysql> explain FORMAT=JSON select sum(l_extendedprice * l_discount) as
revenue from lineitem force index (i_l_shipdate_discount_quantity) where
l_shipdate >= '1994-01-01' and l_shipdate < date_add( '1994-01-01' ,
interval '1' year) and l_discount between 0.09 - 0.01 and 0.09 + 0.01 and
l_quantity < 24;
| {
"query_block": {
"select_id": 1,
"table": {
"table_name": "lineitem",
"access_type": "range",
...
"filtered": 100,
"index_condition": "((`dbt3`.`lineitem`.`l_shipDATE` >= '1994-01-01')
and (`dbt3`.`lineitem`.`l_shipDATE` < ('1994-01-01' + interval '1' year))
and (`dbt3`.`lineitem`.`l_discount` between (0.09 - 0.01) and (0.09 +
0.01)) and (`dbt3`.`lineitem`.`l_quantity` < 24))"
}
27
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
MySQL 5.6:
More Optimizer Improvements
 ORDER BY with LIMIT optimization
 Delayed Materialization of Derived Tables
 Extended secondary keys (InnoDB)
 Reduced optimization time for large IN-lists
 Reduced optimization time for many-table joins
 Reduced space usage for large temporary tables with VARCHAR
 Speed-up of information schema queries
 EXPLAIN for INSERT, UPDATE, DELETE
 Structured EXPLAIN (JSON format)
 Optimizer trace
28
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
More information
 My blog:
– http://oysteing.blogspot.com/
 Optimizer team blog:
– http://mysqloptimizerteam.blogspot.com/
 What’s new in MySQL 5.6:
– http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html
29
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
The preceding is intended to outline our general product direction. It is
intended for information purposes only, and may not be incorporated
into any contract. It is not a commitment to deliver any material, code,
or functionality, and should not be relied upon in making purchasing
decisions. The development, release, and timing of any features or
functionality described for Oracle’s products remains at the sole
discretion of Oracle.
30
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Q&A
31
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
32
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.