MySQL Bugs: #56655: Background DROP TABLE in 5.1 with innodb_file_per

Bug #56655	Background DROP TABLE in 5.1 with innodb_file_per_table=1
Submitted:	8 Sep 2010 14:15	Modified:	27 Oct 2010 20:54
Reporter:	James Day	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Inaam Rana	CPU Architecture:	Any

Description:
This is a fork from bug #41158 to cover the remaining case in 5.1, which isn't addressed by the bug #41158 work.

DROP TABLE usually calls unlink() on one or more files while holding LOCK_open. With innodb_file_per_table = 1 the space for tables is allocated in 4 megabyte chunks, so extreme fragmentation results from the interleaved expanding of many tables. Freeing all of those fragments is very time-consuming due to the large number of disk seeks required - a 100GB table could involve 25,000 disk seeks involving writes

This is a request for InnoDB to do the unlink part of the DROP in the background when innodb_file_per_table = 1, on 5.1 only and only in the plugin version.

From bug #41158 comments:

[7 Sep 19:56] Mark Callaghan

Please reopen this. I think this can be fixed in the innodb plugin for 5.1 by always doing background drop table. This can be a big issue for anyone using innodb_file_per_table in 5.1.

[7 Sep 20:02] Mark Callaghan

From row_drop_table_for_mysql(). I want a my.cnf option to always do the drop in the
background possibly also controlled by whether file-per-table is used and by the size of
the table.

if (table->n_mysql_handles_opened > 0) {
ibool added;

added = row_add_table_to_background_drop_list(table->name);

if (added) {

[7 Sep 22:17] Mark Callaghan

How is defragmenting the filesystem a workaround? That requires me to take the server offline for too long which will cause even more stalls.

Using links is a very short-term workaround. Most of us are unwilling to make our scripts even more complex to do that, or to grant permissions to the file system to others.

The fix in InnoDB is easy.

How to repeat:
Same as bug #41158, drop a table with many fragments.

For MySQL 5.1 and earlier the partial workarounds for this are:

1. Defragment in the filesystem. When you extend many tables the chunks for each end up very fragmented, so the defragmenting can be of benefit for table scans as well as dropping speed. If you don't have a defragmenting program, copying the files somewhere and copying them back will do the job.

2. When dropping, create a filesystem link to the IBD file. Drop the table in MySQL and the OS won't drop the file in the filesystem because the link exists, so the drop will be fast within MySQL. Remove the link and the OS will finish the deleting work in the background.

Also see bug #31592, a request to make the size by which individual table tablespaces grow configurable or just bigger.

However, those workarounds assume ready access to the filesystm and that's not practical on large-scale systems with large numbers of applications doing the creating and dropping.

Suggested fix:
Put the dropping part of the work in InnoDB into the background.

So this is not exactly related to LOCK_open, but a more general request to make InnoDB do the actual heavy lifting (unlink and such) of drop table in the background. This should "speed up" drop table even in 5.5 (where LOCK_open is not held).

Davi, right. It looks like a reasonable and useful request and seems to be causing significant production disruption for some people. LOCK_open is in there because that's what blocks the other operations in 5.1, so moving the heavy lifting out of the time when the lock is held should help. Please feel free to change the bug title to something you think more suitable if you like.

The focus here is on 5.1 because that's where it's hurting but if it's of benefit for 5.5 as well that'd be a very good thing.

The workarounds are practical for smaller installations but not at really large places with a very diverse set of applications.

Implementation wise, the idea is to do the absolutely minimum amount of work under a exclusive lock (LOCK_open in 5.1): unlink the frm file and queue a background drop table within InnoDB. Given a file per table, it should be relatively easy to rename the files (to avoid conflicts) and unlink them asynchronously.

See also Bug #57232

I can't assess risk/effort of this in 5.1.

Note that TRUNCATE has a similar problem -- http://bugs.mysql.com/bug.php?id=56696

A patch to fix this in 5.1:
http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3584
http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3585

Issue addressed in 5.5 and will not be fixed in 5.1