| Bug #79681 | Queries running much slower in version 5.7 versus 5.6 | ||
|---|---|---|---|
| Submitted: | 16 Dec 2015 21:53 | Modified: | 17 Dec 2015 12:12 |
| Reporter: | Mike Savoie | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 5.7, 5.7.10 | OS: | Red Hat (Redhat 6 update 5) |
| Assigned to: | CPU Architecture: | Any | |
[16 Dec 2015 21:53]
Mike Savoie
[16 Dec 2015 22:56]
Mike Savoie
I have added the file mysql-bug-data-79681.rar (WinRAR tool was used to zip). It contains complete dump of the gosalesdw_1021 mysql schema and a file which contains 5 SQL statements which are much slower in 5.7.9 than it was in 5.6
[17 Dec 2015 12:12]
MySQL Verification Team
Hello Mike, Thank you for the report and test case. Observed the issue(for 2 queries it was 9-10 times slow) with 5.7.10 when compared to 5.6.28. Thanks, Umesh
[12 Jan 2016 15:28]
Øystein Grøvlen
The reason for the regression is that MySQL 5.7 tries to estimate the filtering effect of conditions that are not used by indexes. For columns that are not indexed, these estimates are not very accurate, and in this particular case the filtering effect is over-estimated. Work-around: set optimizer_switch='condition_fanout_filter=off'; Another alternative is to add some indexes. I was able to improve the performance of Q1 in 5.7 to the level of 5.6 by adding the following two indexes: sls_product_dim(product_line_code, product_type_key); go_region_dim(region_code); One comment to the queries: I do not understand why there is two instances of sls_product_dim in the join queries (coduga00 and coduga20/coduga40). Through their join conditions with sls_sales_fact, matching rows from the two instances will have the same product_key. Since product_key are PRIMARY KEY in sls_product_dim, rows are joined with themselves. This self-join does not add anything to the result except execution overhead.
[13 Jan 2016 10:41]
Øystein Grøvlen
Looking at the queries, I think a lot can be gained from eliminating tables that does not affect the query result. AFAICT, the following query should be equivalent to Q1:
select `coguda00`.`product_brand_key` as "c0" ,
min(`coguda01`.`product_brand_en`) as "c1" ,
sum(`sls_sales_fact`.`sale_total`) as "c2"
from
`gosalesdw_1021`.`sls_product_dim` `coguda00`,
`gosalesdw_1021`.`sls_product_brand_lookup` `coguda01`,
`gosalesdw_1021`.`sls_product_lookup` `coguda03`,
`gosalesdw_1021`.`sls_sales_fact` `sls_sales_fact`,
`gosalesdw_1021`.`go_region_dim` `coguda30`,
`gosalesdw_1021`.`sls_rtl_dim` `coguda31`,
`gosalesdw_1021`.`go_time_dim` `go_time_dim`,
`gosalesdw_1021`.`emp_employee_dim` `emp_employee_dim`,
`gosalesdw_1021`.`go_branch_dim` `go_branch_dim`
where
`coguda00`.`product_key` = `sls_sales_fact`.`product_key` and
`coguda31`.`retailer_site_key` = `sls_sales_fact`.`retailer_site_key` and
`coguda31`.`retailer_key` in
(select retailer_key from `gosalesdw_1021`.`sls_rtl_dim` `sls_retailer_dim`
where `sls_retailer_dim`.`retailer_type_code` = 7) and
`sls_sales_fact`.`order_day_key` = `go_time_dim`.`day_key` and
`sls_sales_fact`.`employee_key` = `emp_employee_dim`.`employee_key` and
`emp_employee_dim`.`branch_code` = `go_branch_dim`.`branch_code` and
`go_branch_dim`.`branch_key` = 9929 and
`go_time_dim`.`current_year` = 2011 and
`coguda00`.`product_line_code` = 993 and
`coguda00`.`product_type_key` = 961 and
`coguda30`.`region_code` = 740 and
`coguda30`.`country_code` = `coguda31`.`rtl_country_code` and
`coguda00`.`product_number` = `coguda03`.`product_number` and
`coguda01`.`product_brand_code` = `coguda00`.`product_brand_code` and
`coguda03`.`product_language` = 'en'
group by `coguda00`.`product_brand_key` order by `c1` asc;
On MySQL 5.7.9, the above query execute in 0.5% of the time used by the original query!
