Details
Minor
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_myisam.supplier.s_nationkey | 1 | Using where |
| 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,i_l_partkey,i_l_suppkey | i_l_partkey | 5 | dbt3x10_myisam.partsupp.ps_partkey | 30 | Using where |
| 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_switch='semijoin=off'; Query OK, 0 rows affected (0.00 sec) MariaDB [dbt3x10_myisam]> set optimizer_switch='materialization=off'; Query OK, 0 rows affected (0.00 sec)
With default settings for 5.3 (optimizer_switch='semijoin=on,materailization=on') I had the following execution plan:
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,i_s_nationkey | i_s_nationkey | 5 | dbt3x10_myisam.nation.n_nationkey | 4000 | |
| 1 | PRIMARY | partsupp | ref | PRIMARY,i_ps_partkey,i_ps_suppkey | i_ps_suppkey | 4 | dbt3x10_myisam.supplier.s_suppkey | 80 | Using where |
| 1 | PRIMARY | part | eq_ref | PRIMARY | PRIMARY | 4 | dbt3x10_myisam.partsupp.ps_partkey | 1 | Using where; FirstMatch(supplier) |
| 4 | DEPENDENT SUBQUERY | lineitem | ref | i_l_shipdate,i_l_partkey,i_l_suppkey | i_l_partkey | 5 | dbt3x10_myisam.partsupp.ps_partkey | 30 | Using where |
+----+--------------------+----------+--------+--------------------------------------+---------------+---------+------------------------------------+------+--------------------------------------------
With the setting optimizer_switch='semijoin=off,materailization=on' the execution plan is the same
with mariadb-5.1/5.3
MariaDB [dbt3x10_myisam]> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.01 sec)
MariaDB [dbt3x10_myisam]> set optimizer_switch='materialization=on';
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_myisam.supplier.s_nationkey | 1 | Using where |
| 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,i_l_partkey,i_l_suppkey | i_l_partkey | 5 | dbt3x10_myisam.partsupp.ps_partkey | 30 | Using where |
| 3 | DEPENDENT SUBQUERY | part | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+----------+-----------------+--------------------------------------+--------------+---------+-------------------------------------+--------+-----------------------------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
This issue will be fixed by the new feature MDEV-83.
Will be fixed by MDEV-83.
Launchpad bug id: 914569