Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8.5
-
None
-
None
-
N/A
Description
For the current development tree of 11.8 I observed the following on the dataset of TPC-H scale 1 for the query:
select avg(l_discount) from |
LINEITEM as l, |
(
|
select * from |
(
|
select l_orderkey, l_linenumber from LINEITEM, SUPPLIER, NATION n1 |
where s_suppkey = l_suppkey and |
s_nationkey = n1.n_nationkey and |
n1.n_name = 'MOZAMBIQUE' |
) dt1
|
natural join |
(
|
select l_orderkey, l_linenumber from LINEITEM, ORDERS, CUSTOMER, NATION n2 |
where o_orderkey = l_orderkey and |
c_custkey = o_custkey and |
c_nationkey = n2.n_nationkey and |
n2.n_name = 'UNITED STATES' |
) dt2
|
) t
|
where l.l_orderkey=t.l_orderkey and |
l.l_linenumber=t.l_linenumber;
|
after having added the index IDX_LINEITEM_SUPPKEY :
create index IDX_LINEITEM_SUPPKEY on LINEITEM(l_suppkey); |
to the standard TPC-H database schema.
(E.g. after adding the IDX_LINEITEM_SUPPKEY the indexes I had for LINEITEM were
PRIMARY KEY (`L_ORDERKEY`,`L_LINENUMBER`),
|
KEY `LINEITEM_PART_SUPP_FKIDX` (`L_PARTKEY`,`L_SUPPKEY`),
|
KEY `IDX_LINEITEM_ORDERKEY_FKIDX` (`L_ORDERKEY`),
|
KEY `IDX_LINEITEM_SUPPKEY` (`L_SUPPKEY`),
|
I created the database for InnoDB engine with the parameter --innodb-file-per-table using a release build of the current 11.8 server . Then I ran
analyze table CUSTOMER, LINEITEM, NATION, ORDERS, PART, PARTSUPP, REGION, SUPPLIER persistent for all; |
for this server
and after after re-launching the server on the 'cold' cache (i.e. after running the sequence of commands
sync
|
echo 1 > /proc/sys/vm/drop_caches
|
echo 2 > /proc/sys/vm/drop_caches
|
echo 3 > /proc/sys/vm/drop_caches
|
in su mode)
with the parameters
--innodb-buffer-pool-size=500M --optimizer_prune_level=0 --innodb_flush_method=fsync
|
I executed the query. The execution time was
+-----------------+
|
| avg(l_discount) |
|
+-----------------+
|
| 0.049930 |
|
+-----------------+
|
1 row in set (7.337 sec)
|
I checked the execution plan and it was:
+------+-------------+----------+--------+----------------------------------------------------------+-----------------------------+---------+-----------------------------------------------------------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+----------+--------+----------------------------------------------------------+-----------------------------+---------+-----------------------------------------------------------+------+----------+-------------------------------------------------+
|
| 1 | SIMPLE | n1 | ALL | PRIMARY | NULL | NULL | NULL | 25 | 4.00 | Using where |
|
| 1 | SIMPLE | n2 | ALL | PRIMARY | NULL | NULL | NULL | 25 | 4.00 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | SUPPLIER | ref | PRIMARY,SUPPLIER_NATION_FKIDX | SUPPLIER_NATION_FKIDX | 5 | tpch_s1.n1.N_NATIONKEY | 400 | 100.00 | Using index |
|
| 1 | SIMPLE | LINEITEM | ref | PRIMARY,IDX_LINEITEM_ORDERKEY_FKIDX,IDX_LINEITEM_SUPPKEY | IDX_LINEITEM_SUPPKEY | 4 | tpch_s1.SUPPLIER.S_SUPPKEY | 600 | 100.00 | Using index |
|
| 1 | SIMPLE | l | eq_ref | PRIMARY,IDX_LINEITEM_ORDERKEY_FKIDX | PRIMARY | 12 | tpch_s1.LINEITEM.L_ORDERKEY,tpch_s1.LINEITEM.L_LINENUMBER | 1 | 100.00 | |
|
| 1 | SIMPLE | LINEITEM | eq_ref | PRIMARY,IDX_LINEITEM_ORDERKEY_FKIDX | IDX_LINEITEM_ORDERKEY_FKIDX | 12 | tpch_s1.LINEITEM.L_ORDERKEY,tpch_s1.LINEITEM.L_LINENUMBER | 1 | 100.00 | Using index |
|
| 1 | SIMPLE | ORDERS | eq_ref | PRIMARY,ORDERS_FK1 | PRIMARY | 8 | tpch_s1.LINEITEM.L_ORDERKEY | 1 | 100.00 | |
|
| 1 | SIMPLE | CUSTOMER | eq_ref | PRIMARY,CUSTOMER_NATION_FKIDX | CUSTOMER_NATION_FKIDX | 9 | tpch_s1.n2.N_NATIONKEY,tpch_s1.ORDERS.O_CUSTKEY | 1 | 100.00 | Using index |
|
+------+-------------+----------+--------+----------------------------------------------------------+-----------------------------+---------+-----------------------------------------------------------+------+----------+-------------------------------------------------+
|
I repeated the same actions as for 11.8 for the release build of 10.6 taken from the current development tree with an instance of the same database created specifically for 10.6 and the execution of the query took
+-----------------+
|
| avg(l_discount) |
|
+-----------------+
|
| 0.049930 |
|
+-----------------+
|
1 row in set (1.661 sec)
|
while the excution plan was:
+------+-------------+----------+--------+----------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+----------+--------+----------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------+------+----------+-------------------------------------------------+
|
| 1 | SIMPLE | n1 | ALL | PRIMARY | NULL | NULL | NULL | 25 | 4.00 | Using where |
|
| 1 | SIMPLE | n2 | ALL | PRIMARY | NULL | NULL | NULL | 25 | 4.00 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | SUPPLIER | ref | PRIMARY,SUPPLIER_NATION_FKIDX | SUPPLIER_NATION_FKIDX | 5 | tpch_s1.n1.N_NATIONKEY | 400 | 100.00 | Using index |
|
| 1 | SIMPLE | LINEITEM | ref | PRIMARY,IDX_LINEITEM_ORDERKEY_FKIDX,IDX_LINEITEM_SUPPKEY | IDX_LINEITEM_SUPPKEY | 4 | tpch_s1.SUPPLIER.S_SUPPKEY | 600 | 100.00 | Using index |
|
| 1 | SIMPLE | ORDERS | eq_ref | PRIMARY,ORDERS_FK1 | PRIMARY | 8 | tpch_s1.LINEITEM.L_ORDERKEY | 1 | 100.00 | |
|
| 1 | SIMPLE | CUSTOMER | eq_ref | PRIMARY,CUSTOMER_NATION_FKIDX | PRIMARY | 4 | tpch_s1.ORDERS.O_CUSTKEY | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | l | eq_ref | PRIMARY,IDX_LINEITEM_ORDERKEY_FKIDX | PRIMARY | 12 | tpch_s1.LINEITEM.L_ORDERKEY,tpch_s1.LINEITEM.L_LINENUMBER | 1 | 100.00 | |
|
| 1 | SIMPLE | LINEITEM | eq_ref | PRIMARY,IDX_LINEITEM_ORDERKEY_FKIDX | PRIMARY | 12 | tpch_s1.LINEITEM.L_ORDERKEY,tpch_s1.LINEITEM.L_LINENUMBER | 1 | 100.00 | Using index |
|
+------+-------------+----------+--------+----------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------+------+----------+-------------------------------------------------+
|
I checked the current MySQL 8.0 and the execution plan for the query was the same as for 10.6 while the execution time was comparable to that of 10.6 (though worse).
I used fsync for the parameter innodb_flush_method. With the default value for this parameter I got the following execution times:
+-----------------+
|
| avg(l_discount) |
|
+-----------------+
|
| 0.049930 |
|
+-----------------+
|
1 row in set (2.021 sec)
|
for 10.6
and
+-----------------+
|
| avg(l_discount) |
|
+-----------------+
|
| 0.049930 |
|
+-----------------+
|
1 row in set (34.051 sec)
|
for 11.8
|
So the difference was even bigger.
(By the way I observed worse execution times for all queries from TPC-H benchmark with default setting for innodb_flush_method in comparison with fsync setting for any settings of the parameter --innodb_buffer_pool_size. The bigger was the pool size the smaller was the the difference, but it still could be observed. These observations were done for both 10.6 and 11.8.)
My measurements were done on Ubuntu 24.04.3 LTS. My laptop has 64GB of memory and an SSD of 2GB.