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.