Bug #116271 | Performance of TPC-H Query 4 | ||
---|---|---|---|
Submitted: | 30 Sep 2024 20:34 | Modified: | 14 Nov 2024 22:18 |
Reporter: | JINSHENG BA | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S5 (Performance) |
Version: | 596f0d23 (9.0.0) | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[30 Sep 2024 20:34]
JINSHENG BA
[2 Oct 2024 12:55]
Øystein Grøvlen
I am not able to reproduce this. Which scale factor is used, and what indexes have been created on the tables? What are the settings for innodb_buffer_pool_size and innodb_adaptive_hash_index?
[2 Oct 2024 19:12]
JINSHENG BA
Thanks for looking into it! I am sorry that you cannot reproduce it in your environment. I had a try just now. I believe you can observe the number of cost in query plans is always reduced. However, for execution time, I can observe the execution time difference on my PC but cannot observe it on another PC. I have not figured out the reason. Not sure it is due to cache. I will report again if I can reproduce it. For the information you asked: TPC-H: 1GB. mysql> SELECT @@innodb_adaptive_hash_index; +------------------------------+ | @@innodb_adaptive_hash_index | +------------------------------+ | 0 | +------------------------------+ 1 row in set (0.00 sec) mysql> show index from ORDERS; +--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | ORDERS | 0 | PRIMARY | 1 | O_ORDERKEY | A | 2887534 | NULL | NULL | | BTREE | | | YES | NULL | | ORDERS | 1 | ORDERS_FK1 | 1 | O_CUSTKEY | A | 197859 | NULL | NULL | | BTREE | | | YES | NULL | +--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.01 sec) mysql> show index from LINEITEM; +----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | LINEITEM | 0 | PRIMARY | 1 | L_ORDERKEY | A | 2848228 | NULL | NULL | | BTREE | | | YES | NULL | | LINEITEM | 0 | PRIMARY | 2 | L_LINENUMBER | A | 11525904 | NULL | NULL | | BTREE | | | YES | NULL | +----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+