Bug #37577 Query Q9 of DBT3 is running on Falcon slower than Innodb
Submitted: 23 Jun 2008 6:40 Modified: 26 May 2010 17:50
Reporter: Xuekun Hu Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S5 (Performance)
Version:MySQL6.0 OS:Linux (SLES10SP1 (2.6.16.46-0.12-smp, x64))
Assigned to: Ann Harrison CPU Architecture:Any
Tags: DBT3, F_PERFORMANCE, falcon, Optimizer
Triage: Triaged: D4 (Minor)

[23 Jun 2008 6:40] Xuekun Hu
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!!!
[25 Jun 2008 13:42] Susanne Ebrecht
Many thanks for writing a bug report.

This is an already known issue and we are already working on it.
[25 Jun 2008 14:24] Xuekun Hu
Hi, Susanne

Do you know where could be the problem? optimizer, or Falcon doesn't give the right statistics to oprimizer? 

I'm very interesting in this issue and is also investigating it. If you have more info, that would be helpful. 

Thx, Xuekun
[25 Oct 2008 3:44] Kevin Lewis
It has been suggested in previous comments that the reason Falcon is slower here than InnoDB is because falcon does not provide realistic row estimates to allow the optimizer to make the right decisions.  That makes this bug similar to these; 

Bug#36562 - Falcon reports unrealistic row estimate with IN()
Bug#36474 - Falcon reports unrealistic row estimate on simple key lookup.

This bug and Bug#36562 are currently triaged for SR60BETA, but getting row estimates correct is a performance change and will be done later.
[15 Dec 2008 23:44] Kevin Lewis
Triage team, Please re-evaluate this beta tag.  This is a performance issue.