MySQL Bugs: #103054: DISCARD TABLESPACE of an empty table takes a long time with a large buffer pool

Bug #103054	DISCARD TABLESPACE of an empty table takes a long time with a large buffer pool
Submitted:	22 Mar 2021 6:01	Modified:	5 Apr 2021 6:52
Reporter:	Hao He	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Importing an innodb table, as described in the doc https://dev.mysql.com/doc/refman/5.7/en/innodb-table-import.html, requires creating an empty table on the destination instance and then discarding its tablespace. We noticed that the "ALTER TABLE t1 DISCARD TABLESPACE" command can take a long time if the number of pages in the buffer pool is large:

bufferPoolPages    DISCARD TABLESPACE time
0                  0.00 sec
6 GB               0.05 sec
40 GB              0.44 sec

During the execution of DISCARD TABLESPACE, reads/writes to other databases in the same mysql instance can become noticeably slower. We suspect innodb is scanning the entire buffer pool and can lock up buffer pool mutex.

How to repeat:
Start mysql with a large buffer pool. Issue enough writes to fill up the buffer pool. Then execute the following two commands. DISCARD TABLESPACE can take a long time if the buffer pool is large.

mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.44 sec)

Suggested fix:
In 2012 a fix is made to solve a similar problem for DROP TABLE; see https://github.com/mysql/mysql-server/commit/160c5dddc48b52561977c720051956d7f15e8856

Perhaps we can do a similar fix for DISCARD TABLESPACE/

Hi Mr. He,

Thank you for your bug report.

We have tested your report and this operation is much faster on 8.0 than on 5.7. If that is the same situation in your case, then let us inform you that performance will not be improved in 5.7. 

Hence, let us know your experience with 8.0.

Thanks for your timely reply! I'll run my test against mysql 8.0 and reply back with the result.

We are waiting on your feedback.

I have repeated my tests on the latest mysql 8.0. Indeed, the Discard Tablespace time remains small when the data size in the buffer pool grows.

bufferPoolPages    DISCARD TABLESPACE time
2.7 GB             0.01 sec
5 GB               0.01 sec
20 GB              0.01 sec

It's great that this performance issue is resolved in mysql 8.0. Could you point me to the relevant change in mysql 8.0 that contributed to this improvement? I am asking this because it might be easier for us to backport/implement a fix to the old mysql version we are using, rather than upgrade our production server to 8.0 as a whole.

Hi Mr. He,

Thanks for your feedback.

A feature in 8.0 that enables fast discarding of the tablespace is the introduction of the data dictionary and due to the grouping of all system data into a single system tablespace. No other version, except for 8.0, has these feature. This is all described in our 8.0 Reference Manual.

Not a bug.

Thanks for explaining why Discard Tablespace is faster in mysql 8.0. I have no more questions. Feel free to resolve this issue as mysql performs as expected.

Thank you, Mr. He.