Description:
Query Q9 of DBT3 is running on Falcon slower than Innodb, that’s 6s vs. 101s.
Configuration:
MySQL-6.0-Falcon, ChangeSet@1.2681
SLES10SP1 (2.6.16.46-0.12-smp)
DP Xeon Quad Core Platform
Q9: select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%:1%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;
The Query Plan are different for Falcon and InnoDB.
For Falcon:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 299053 Using temporary; Using filesort
2 DERIVED nation ALL PRIMARY NULL NULL NULL 2
2 DERIVED supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3.nation.n_nationkey 2
2 DERIVED lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3.supplier.s_suppkey 2
2 DERIVED partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3.lineitem.l_partkey,dbt3.lineitem.l_suppkey 1 Using where
2 DERIVED orders eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_orderkey 1
2 DERIVED part eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_partkey 1 Using where
For Innodb:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 299053 Using temporary; Using filesort
2 DERIVED part ALL PRIMARY NULL NULL NULL 200718 Using where
2 DERIVED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3.part.p_partkey 1
2 DERIVED supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3.partsupp.ps_suppkey 1
2 DERIVED nation eq_ref PRIMARY PRIMARY 4 dbt3.supplier.s_nationkey 1
2 DERIVED lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 10 dbt3.partsupp.ps_partkey,dbt3.partsupp.ps_suppkey 3 Using index condition
2 DERIVED orders eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_orderkey 1
The index cardinality is also different for Falcon and Innodb. One example on lineitem table.
Falcon:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
lineitem 0 PRIMARY 1 l_orderkey NULL 1325000 NULL NULL BTREE
lineitem 0 PRIMARY 2 l_linenumber NULL 2650000 NULL NULL BTREE
lineitem 1 i_l_shipdate 1 l_shipDATE NULL 2650000 NULL NULL YES BTREE
lineitem 1 i_l_suppkey_partkey 1 l_partkey NULL 1325000 NULL NULL YES BTREE
lineitem 1 i_l_suppkey_partkey 2 l_suppkey NULL 2650000 NULL NULL YES BTREE
lineitem 1 i_l_partkey 1 l_partkey NULL 2650000 NULL NULL YES BTREE
lineitem 1 i_l_suppkey 1 l_suppkey NULL 2650000 NULL NULL YES BTREE
lineitem 1 i_l_receiptdate 1 l_receiptDATE NULL 2650000 NULL NULL YES BTREE
lineitem 1 i_l_orderkey 1 l_orderkey NULL 2650000 NULL NULL BTREE
lineitem 1 i_l_orderkey_quantity 1 l_orderkey NULL 1325000 NULL NULL BTREE
lineitem 1 i_l_orderkey_quantity 2 l_quantity NULL 2650000 NULL NULL YES BTREE
lineitem 1 i_l_commitdate 1 l_commitDATE NULL 2650000 NULL NULL YES BTREE
Innodb:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
lineitem 0 PRIMARY 1 l_orderkey A 6019542 NULL NULL BTREE
lineitem 0 PRIMARY 2 l_linenumber A 6019542 NULL NULL BTREE
lineitem 1 i_l_shipdate 1 l_shipDATE A 6967 NULL NULL YES BTREE
lineitem 1 i_l_suppkey_partkey 1 l_partkey A 429967 NULL NULL YES BTREE
lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 2006514 NULL NULL YES BTREE
lineitem 1 i_l_partkey 1 l_partkey A 429967 NULL NULL YES BTREE
lineitem 1 i_l_suppkey 1 l_suppkey A 23152 NULL NULL YES BTREE
lineitem 1 i_l_receiptdate 1 l_receiptDATE A 18 NULL NULL YES BTREE
lineitem 1 i_l_orderkey 1 l_orderkey A 3009771 NULL NULL BTREE
lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 3009771 NULL NULL BTREE
lineitem 1 i_l_orderkey_quantity 2 l_quantity A 6019542 NULL NULL YES BTREE
lineitem 1 i_l_commitdate 1 l_commitDATE A 6959 NULL NULL YES BTREE
How to repeat:
Use DBT3 workload. Set Scale Factor with 1, and put all the dataset in memory via tmpfs.
The execution time of Q9 on Falcon is 101s, while 6s on Innodb.
Suggested fix:
After changing the query plan on Falcon to the same order of Innodb via “STRAIGHT_JOIN”, the execution time is shorten to 5s!!!
Description: Query Q9 of DBT3 is running on Falcon slower than Innodb, that’s 6s vs. 101s. Configuration: MySQL-6.0-Falcon, ChangeSet@1.2681 SLES10SP1 (2.6.16.46-0.12-smp) DP Xeon Quad Core Platform Q9: select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%:1%' ) as profit group by nation, o_year order by nation, o_year desc; The Query Plan are different for Falcon and InnoDB. For Falcon: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 299053 Using temporary; Using filesort 2 DERIVED nation ALL PRIMARY NULL NULL NULL 2 2 DERIVED supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3.nation.n_nationkey 2 2 DERIVED lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3.supplier.s_suppkey 2 2 DERIVED partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3.lineitem.l_partkey,dbt3.lineitem.l_suppkey 1 Using where 2 DERIVED orders eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_orderkey 1 2 DERIVED part eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_partkey 1 Using where For Innodb: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 299053 Using temporary; Using filesort 2 DERIVED part ALL PRIMARY NULL NULL NULL 200718 Using where 2 DERIVED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3.part.p_partkey 1 2 DERIVED supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3.partsupp.ps_suppkey 1 2 DERIVED nation eq_ref PRIMARY PRIMARY 4 dbt3.supplier.s_nationkey 1 2 DERIVED lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 10 dbt3.partsupp.ps_partkey,dbt3.partsupp.ps_suppkey 3 Using index condition 2 DERIVED orders eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_orderkey 1 The index cardinality is also different for Falcon and Innodb. One example on lineitem table. Falcon: Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment lineitem 0 PRIMARY 1 l_orderkey NULL 1325000 NULL NULL BTREE lineitem 0 PRIMARY 2 l_linenumber NULL 2650000 NULL NULL BTREE lineitem 1 i_l_shipdate 1 l_shipDATE NULL 2650000 NULL NULL YES BTREE lineitem 1 i_l_suppkey_partkey 1 l_partkey NULL 1325000 NULL NULL YES BTREE lineitem 1 i_l_suppkey_partkey 2 l_suppkey NULL 2650000 NULL NULL YES BTREE lineitem 1 i_l_partkey 1 l_partkey NULL 2650000 NULL NULL YES BTREE lineitem 1 i_l_suppkey 1 l_suppkey NULL 2650000 NULL NULL YES BTREE lineitem 1 i_l_receiptdate 1 l_receiptDATE NULL 2650000 NULL NULL YES BTREE lineitem 1 i_l_orderkey 1 l_orderkey NULL 2650000 NULL NULL BTREE lineitem 1 i_l_orderkey_quantity 1 l_orderkey NULL 1325000 NULL NULL BTREE lineitem 1 i_l_orderkey_quantity 2 l_quantity NULL 2650000 NULL NULL YES BTREE lineitem 1 i_l_commitdate 1 l_commitDATE NULL 2650000 NULL NULL YES BTREE Innodb: Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment lineitem 0 PRIMARY 1 l_orderkey A 6019542 NULL NULL BTREE lineitem 0 PRIMARY 2 l_linenumber A 6019542 NULL NULL BTREE lineitem 1 i_l_shipdate 1 l_shipDATE A 6967 NULL NULL YES BTREE lineitem 1 i_l_suppkey_partkey 1 l_partkey A 429967 NULL NULL YES BTREE lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 2006514 NULL NULL YES BTREE lineitem 1 i_l_partkey 1 l_partkey A 429967 NULL NULL YES BTREE lineitem 1 i_l_suppkey 1 l_suppkey A 23152 NULL NULL YES BTREE lineitem 1 i_l_receiptdate 1 l_receiptDATE A 18 NULL NULL YES BTREE lineitem 1 i_l_orderkey 1 l_orderkey A 3009771 NULL NULL BTREE lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 3009771 NULL NULL BTREE lineitem 1 i_l_orderkey_quantity 2 l_quantity A 6019542 NULL NULL YES BTREE lineitem 1 i_l_commitdate 1 l_commitDATE A 6959 NULL NULL YES BTREE How to repeat: Use DBT3 workload. Set Scale Factor with 1, and put all the dataset in memory via tmpfs. The execution time of Q9 on Falcon is 101s, while 6s on Innodb. Suggested fix: After changing the query plan on Falcon to the same order of Innodb via “STRAIGHT_JOIN”, the execution time is shorten to 5s!!!