MySQL Bugs: #93753: Drop table slowly because of dropping hash index

Bug #93753	Drop table slowly because of dropping hash index
Submitted:	28 Dec 2018 9:42	Modified:	9 Jan 2019 11:56
Reporter:	zkong kong	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.13	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Version 8.0.13 drop table is much slower than 5.7.24 when innodb_adaptive_hash_index is on.

How to repeat:
The main configs of 8.0.13 and the 5.7.24 are the same
using sysbench :
sysbench --test=insert.lua --mysql-host=xxxx --mysql-port=xxxx --mysql-db=sbtest --mysql-user=test --mysql-password=test   --max-requests=0  --oltp_table_size=10000000 --oltp-tables-count=1 --oltp-auto-inc=on  --report-interval=20  --db-ps-mode=disable  --random_points=10   --mysql-table-engine=InnoDB  --num-threads=1 --max-time=3600 prepare

Then drop table separately and count the time;

Suggested fix:
using the perf get a hot function, btr_drop_ahi_for_table which is new of version 8.0.13

duplicate of bug#91977 ?

hi zhai weixiang:
This is a different problem.  There is some performance regression of version 8.0.13 when hash index is on. 

Use previous sysbench insert 10000000 records then drop it. Repeat serval times:
Version 5.7.24:
root@sbtest 08:24:25>drop table sbtest1;
Query OK, 0 rows affected (0.85 sec)

root@sbtest 08:24:33>drop table sbtest1;
Query OK, 0 rows affected (0.85 sec)

root@sbtest 08:29:08>drop table sbtest1;
Query OK, 0 rows affected (0.85 sec)

root@sbtest 08:34:09>drop table sbtest1;
Query OK, 0 rows affected (0.84 sec)

root@sbtest 08:38:27>drop table sbtest1;
Query OK, 0 rows affected (0.86 sec)

They are almost the same;

Version 8.0.13:

root@sbtest 08:24:56>drop table sbtest1;
Query OK, 0 rows affected (1.77 sec)

root@sbtest 08:25:06>drop table sbtest1;
Query OK, 0 rows affected (1.91 sec)

root@sbtest 08:32:56>drop table sbtest1;
Query OK, 0 rows affected (2.19 sec)

root@sbtest 08:38:33>drop table sbtest1;
Query OK, 0 rows affected (2.36 sec)

They are increasing.

But when turn off the hash index:

root@sbtest 08:44:30>show variables like "%hash%";
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | OFF   |
| innodb_adaptive_hash_index_parts | 8     |

mysql> drop table sbtest1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table sbtest1;
Query OK, 0 rows affected (0.05 sec)

bug#91977 gave a workaround by disabling AHI. So i think this is duplicate of that one. Actually I also saw similar issues on production though our mysql is based on an old version 5.6.16...the table is more than 200G, and the DROP TABLE statement was trying to invalidate all  AHI entries(saw backtrace and lead to assert because of time out waiting for latch....Actually in most case, we can't see improvement of AHI....

Hello zkong kong,

Thank you for the report.
Agree with zhai, this is duplicate of bug#91977.

regards,
Umesh