MySQL Bugs: #100325: Dropping big table causes IO spike

Bug #100325	Dropping big table causes IO spike
Submitted:	26 Jul 2020 17:27	Modified:	30 Jul 2020 16:06
Reporter:	Zheng Lai (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	drop table, io

Description:
Currently, when user try to drop a table with big .ibd file, it'll cause IO spike when removing this .ibd file in filesystem, which can have a much more severe impact on the MySQL performance.

How to repeat:
create a table and insert enough rows into it, make the .ibd file > 10G
drop table
An IO spike can be observed when dropping table

Suggested fix:
The .ibd file can be gradually truncated, instead of deleting it immediately.

Hi Mr. Lai,

Thank you for your bug report.

However, this is not a bug.

Modern permanent storage devices are getting faster and faster, so spikes that you describe are getting more and more rare. Recently, I dropped 10 Gb table on my 2 Tb SSD and had no spike what so ever. Also, your idea of removing table bit by bit would produce a total I/O lag that is far greater then the current solution.

Not a bug.

Hi Mr. Lai,

Thank you for your bug report.

However, this is not a bug.

Modern permanent storage devices are getting faster and faster, so spikes that you describe are getting more and more rare. Recently, I dropped 10 Gb table on my 2 Tb SSD and had no spike what so ever. Also, your idea of removing table bit by bit would produce a total I/O lag that is far greater then the current solution.

Not a bug.

Hi,

As we know, removing a big file will cost lots of IO resource, even in a short time, this will have impact of concurrent IO bound operations, no matter it's on SSD or HDD.
Currently, most of MySQL users use hard link + truncate to avoid this impact.

Our team, Tencent Cloud-Native Database Team(CDB&CynosDB) developed a patch which will do dropping table asynchrony. The new approach of dropping table will be renaming .ibd file and then truncating the big .ibd file in background thread. And this patch works fine on our server.

I guess maybe this patch can be evaluated by InnoDB developer?

Hi Mr. Lai,

By all means ......

We would seriously consider your patch ......

Just use "Files" tab to upload it.

Hi,
I've uploaded the patch, it can be applied to 8.0.21, please evaluate it. Thanks.

Updated the patch, please use the latest one, and ignore the previous one.

Thank you Mr. Lai.

We shall evaluate the patch carefully.