Bug #93753 Drop table slowly because of dropping hash index
Submitted: 28 Dec 2018 9:42 Modified: 9 Jan 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

[28 Dec 2018 9:42] zkong kong
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
[29 Dec 2018 2:40] zhai weixiang
duplicate of bug#91977 ?
[31 Dec 2018 1:14] zkong kong
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)
[31 Dec 2018 11:36] zhai weixiang
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....
[9 Jan 11:56] Umesh Shastry
Hello zkong kong,

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

regards,
Umesh