Bug #101732 | ibtmp1 is very large in semijoin duplicate weedout strategy | ||
---|---|---|---|
Submitted: | 24 Nov 2020 9:55 | Modified: | 14 Dec 2020 11:00 |
Reporter: | Dean Zhou | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 5.7.30, 5.7.32, 8.0.21 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | ibtmp1 |
[24 Nov 2020 9:55]
Dean Zhou
[24 Nov 2020 11:11]
Dean Zhou
8.0.21 can also reproduce
[25 Nov 2020 12:22]
MySQL Verification Team
Hello Dean Zhou, Thank you for the report and feedback. May I request you to please provide MySQL Server configuration details(my.cnf/my.ini - pls mark it as private after posting here), OS details and sysbench test used for preparing etc to reproduce this issue at our end. Thank you. regards, Umesh
[26 Nov 2020 7:29]
Dean Zhou
Hi Umesh, Thank you for the feed. 1. test preparation a) sysbench initializes 2 tables with 10 million rows sysbench 1.0.20 sysbench --mysql-user='user' --mysql-password='password' --mysql-db='test' --mysql-host=host --mysql-port=5730 --tables=2 --time=100 --report-interval=1 --table_size=10000000 - -threads=10 oltp_insert.lua cleanup sysbench --mysql-user='user' --mysql-password='password' --mysql-db='test' --mysql-host=host --mysql-port=5730 --tables=2 --time=100 --report-interval=1 --table_size=10000000 - -threads=10 oltp_insert.lua prepare b) alter table sbtest2 add a int default null; c) update sbtest1, sbtest2 set sbtest2.a= sbtest1.k where sbtest1.id = sbtest2.id; 2. execute sql MySQL [(none)]> use test Database changed MySQL [test]> set optimizer_switch = 'firstmatch=off'; Query OK, 0 rows affected (0.00 sec) MySQL [test]> set optimizer_switch = 'materialization=off'; Query OK, 0 rows affected (0.00 sec) MySQL [test]> set max_heap_table_size = 16384; Query OK, 0 rows affected (0.00 sec) MySQL [test]> explain select count(*) from sbtest1 where k in (select a from sbtest2 ); +----+-------------+---------+------------+------+---------------+------+---------+----------------+---------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+----------------+---------+----------+------------------------------+ | 1 | SIMPLE | sbtest2 | NULL | ALL | NULL | NULL | NULL | NULL | 9857202 | 100.00 | Using where; Start temporary | | 1 | SIMPLE | sbtest1 | NULL | ref | k_1 | k_1 | 4 | test.sbtest2.a | 6 | 100.00 | Using index; End temporary | +----+-------------+---------+------------+------+---------------+------+---------+----------------+---------+----------+------------------------------+ 2 rows in set, 1 warning (0.00 sec) // so execution plan is semi join duplicateweedout MySQL [test]> select count(*) from sbtest1 where k in (select a from sbtest2 ); // execution time is about 1hour44min, ibtmp1 keeps expanding 3. os detail # cat /proc/version Linux version 5.8.2-1.el7.elrepo.x86_64 (mockbuild@Build64R7) (gcc (GCC) 9.3.1 20200408 (Red Hat 9.3.1-2), GNU ld version 2.32-16.el7) #1 SMP Tue Aug 18 18:38:17 EDT 2020 mount | grep vdb1 /dev/vdb1 on /data type xfs (rw,relatime,attr2,inode64,logbufs=8,logbsize=32k,noquota)
[26 Nov 2020 7:30]
MySQL Verification Team
Thank you Dean for the requested details. I'll try with the provided conf and other details and will get back to you if further clarification needed. regards, Umesh
[11 Dec 2020 15:44]
Dean Zhou
Hi Umesh, thank you for the test. We need to confirm three things: 1. both set optimizer_switch = ... and the select count(*) ... must be executed on the same connection. 2. your sql plan seems right, but 5 min 12.63 sec execution time is impossible if the sql plan is semijoin duplicateweedout. can you set breakpoint on do_sj_dups_weedout function ? 3. the result of select count(*) ... must be 10000000 in my test case. I also reproduce in MySQL 5.7.32, i uploaded show session variables result before executing select sql, so you can check the parameters. ----------------- MySQL 5.7.32 -rw-r----- 1 mysql mysql 19G Dec 11 21:51 ibtmp1
[11 Dec 2020 15:46]
Dean Zhou
show session variables
Attachment: session variables.txt (text/plain), 17.85 KiB.
[14 Dec 2020 11:00]
MySQL Verification Team
Thank you for your patience, finally I was able to reproduce (host, server version and conf remain same. I didn't check structure etc but looks like downgrading sysbench from 1.1.0 to 1.0.20 did the trick - haven't checked if table structure varied etc). I'll be joining the activity logs shortly. regards, Umesh
[15 Dec 2020 7:58]
MySQL Verification Team
MySQL Server 5.7.32 test results
Attachment: 101732_5.7.32.EL7_results.txt (text/plain), 144.04 KiB.