MySQL Bugs: #115857: Dropping large unused tables on prod with file_per_table

Bug #115857	Dropping large unused tables on prod with file_per_table_on=1 can cause downtime
Submitted:	18 Aug 2024 20:46	Modified:	6 Dec 2024 17:37
Reporter:	Malika Gogia	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	All	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The MySQL manual for all versions gives just a small Disadvantage of keeping innosb_ file_per_table as ON. Its as below:

“The buffer pool is scanned when dropping a table that resides in a file-per-table tablespace, which can take several seconds for large buffer pools. The scan is performed with a broad internal lock, which may delay other operations.”

This information is ambiguous and incomplete. As this issue caused a 10 minute downtime on production putting the jobs of DBAs at stake. Now the problem with this issue/statement is that it’s left to the judgement of the audience that what does a “broad internal lock” mean. If the drop locks the table and metadata of the table to be dropped, ideally it should bot cause any issues as there are no active reads and writes on the table. Also table has no active dirty pages in buffer pool. But still it causes all activity to stop on server as it takes kind of a global lock. It causes client threads to get stuck and timeout. And if you are using any proxies, they shun this node thinking the node is unresponsive.

How to repeat:
Drop a large table preferably 10G and more on a busy server.

Suggested fix:
We need to fix this as ideally all these cleanup process for a dropped table should happen in background without affecting other threads which are not using this table. But even if we aren't fixing it we need to clearly document in the manual that what kind of locks a drop table takes on a table apart from global table lock and table metadata lock so that people can decide if its safe to drop an unused table
On production.

Hi Mr. Gogia,

Thank you for your bug report.

However, this is not a bug. This is expected behaviour.

You have just quoted a single sentence from our Reference Manual. However, dropping a table involves much more than that.

You should also read chapters on Data Dictionary, Information Schema and Performance Schema to understand how many operations are involved in dropping a table. Each of these operations also involves a large number of locks. Hence, you should also read a chapter on the Metadata Locks, as well. You should also read a chapter on InnoDB storage engine, as dropping a table involves undo logs as well.

Hence, this is much more complicated that what you have cited in your bug report.

Last, but not least, there is the operation on physically removing all the files from the filesystem which has it's own locks, independent of MySQL.

Not a bug.

HI Mr. Gogia,

We decided to verify this bug as a documentation enhancement.

It would be prudent to add more to the above description that you quoted.

Verified as a documentation bug.

Hi Team,

Do we have any update around this?

hi Mr. Gogia,

No, there aer no updates yet....

When the documentation team documents this behaviour, they will update this page with that information.