MySQL partition drop takes time

Question

In some cases we use MySQL partitioning by date to store data, keep X days back of info and on a daily basis an automatic process creates partitions ahead and drops old partitions (mind there is no archive being done here just drop). The data is also subpartitioned into 40 partitions by some hash to further optimize access.

While the daily "alter table drop partition" query runs the DB suffers a noticeable performance drop and the application relaying on this DB exhibits dropped connections, serves less requests per second etc.

We are running MySQL 5.5.17 for this specific app with InnoDB and each of these partitions being dropped has a few millions of records (possibly above 10 million). Size per partition is on average 4.5GB.

I am not seeing any intensive IO on that box at the time of the partition drop so I can only assume it's not related to that. CPU load average however, goes up from the 0.5 normal to that time of day to around 8-10. This goes on for a couple of minutes.

Isn't the partition drop supposed to be an easy logical drop? Is it possible we're doing something wrong or that we can tweak it somehow or is this to be expected.

Cheers

Answer 1

I realize this is an old question, but it's unanswered so I'll give it a whirl.

If your filesystem is ext3, deleting the file can take some time. XFS and ext4 will be much faster.

A trick you can do to speed things up from MySQL's perspective is create a hard link (not a symlink) to the partition's file. The DROP PARTITION will then simply decrement the reference count on the file, which is near instant. You can them remove the link you made to the file. This will still take some time, but MySQL won't see it.