Serious performance regression with default settings for Q20 from DBT-3
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Confirmed
|
Critical
|
Sergey Petrunia |
Bug Description
When Q20 is executed from DBT-3 with default settings a serious performance regression can be seen in mariadb-5.3 in comparison with mariadb-5.1/5.2 or mysql-5.1.
for a myisam scale factor 10 DBT-3 database I had the following execution time:
for mariadb-5.1/5.2 ~ 3 hrs
for mariadb-5.3 ~ 11 hrs.
With mariadb-5.1/5.2 the query execution plan was:
MariaDB [dbt3x10_myisam]> explain
-> select sql_calc_found_rows
-> s_name, s_address
-> from supplier, nation
-> where s_suppkey in (select ps_suppkey from partsupp
-> where ps_partkey in (select p_partkey from part
-> where p_name like 'forest%')
-> and ps_availqty >
-> (select 0.5 * sum(l_quantity)
-> from lineitem
-> where l_partkey = ps_partkey
-> and l_suppkey = ps_suppkey
-> and l_shipdate >= date('1994-01-01')
-> and l_shipdate < date('1994-01-01') +
-> interval '1' year ))
-> and s_nationkey = n_nationkey
-> and n_name = 'CANADA'
-> order by s_name
-> limit 10;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | supplier | ALL | i_s_nationkey | NULL | NULL | NULL | 100000 | Using where; Using filesort |
| 1 | PRIMARY | nation | eq_ref | PRIMARY | PRIMARY | 4 | dbt3x10_
| 2 | DEPENDENT SUBQUERY | partsupp | index_subquery | i_ps_suppkey | i_ps_suppkey | 4 | func | 80 | Using where |
| 4 | DEPENDENT SUBQUERY | lineitem | ref | i_l_shipdate,
| 3 | DEPENDENT SUBQUERY | part | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+-
The same plan was chosen in 5.3 with settings:
MariaDB [dbt3x10_myisam]> set optimizer_
Query OK, 0 rows affected (0.00 sec)
MariaDB [dbt3x10_myisam]> set optimizer_
Query OK, 0 rows affected (0.00 sec)
With default settings for 5.3 (optimizer_
MariaDB [dbt3x10_myisam]> explain
-> select sql_calc_found_rows
-> s_name, s_address
-> from supplier, nation
-> where s_suppkey in (select ps_suppkey from partsupp
-> where ps_partkey in (select p_partkey from part
-> where p_name like 'forest%')
-> and ps_availqty >
-> (select 0.5 * sum(l_quantity)
-> from lineitem
-> where l_partkey = ps_partkey
-> and l_suppkey = ps_suppkey
-> and l_shipdate >= date('1994-01-01')
-> and l_shipdate < date('1994-01-01') +
-> interval '1' year ))
-> and s_nationkey = n_nationkey
-> and n_name = 'CANADA'
-> order by s_name
-> limit 10;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | supplier | ref | PRIMARY,
| 1 | PRIMARY | partsupp | ref | PRIMARY,
| 1 | PRIMARY | part | eq_ref | PRIMARY | PRIMARY | 4 | dbt3x10_
| 4 | DEPENDENT SUBQUERY | lineitem | ref | i_l_shipdate,
+----+-
With the setting optimizer_
with mariadb-5.1/5.3
MariaDB [dbt3x10_myisam]> set optimizer_
Query OK, 0 rows affected (0.01 sec)
MariaDB [dbt3x10_myisam]> set optimizer_
Query OK, 0 rows affected (0.00 sec)
MariaDB [dbt3x10_myisam]> explain
-> select sql_calc_found_rows
-> s_name, s_address
-> from supplier, nation
-> where s_suppkey in (select ps_suppkey from partsupp
-> where ps_partkey in (select p_partkey from part
-> where p_name like 'forest%')
-> and ps_availqty >
-> (select 0.5 * sum(l_quantity)
-> from lineitem
-> where l_partkey = ps_partkey
-> and l_suppkey = ps_suppkey
-> and l_shipdate >= date('1994-01-01')
-> and l_shipdate < date('1994-01-01') +
-> interval '1' year ))
-> and s_nationkey = n_nationkey
-> and n_name = 'CANADA'
-> order by s_name
-> limit 10;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | supplier | ALL | i_s_nationkey | NULL | NULL | NULL | 100000 | Using where; Using filesort |
| 1 | PRIMARY | nation | eq_ref | PRIMARY | PRIMARY | 4 | dbt3x10_
| 2 | DEPENDENT SUBQUERY | partsupp | index_subquery | i_ps_suppkey | i_ps_suppkey | 4 | func | 80 | Using where |
| 4 | DEPENDENT SUBQUERY | lineitem | ref | i_l_shipdate,
| 3 | DEPENDENT SUBQUERY | part | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+-
Changed in maria: | |
milestone: | none → 5.5 |
importance: | Undecided → Critical |
status: | New → Confirmed |
Changed in maria: | |
assignee: | nobody → Sergey Petrunia (sergefp) |