The problem I am going to describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries, it might easily go unnoticed. I used it as one of the examples in our talk given at phpDay.it conference last week to demonstrate some pitfalls one may hit when designing schemas and queries, but then I thought it could be a good idea to publish this on the blog as well.

To demonstrate the issue let’s use a typical example – a sales query. Our data is a tiny store directory consisting of three very simple tables:

PLAIN TEXT SQL:

1. CREATE TABLE `products` (
2.   `prd_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.   `prd_name` varchar(32) NOT NULL,
4.   PRIMARY KEY (`prd_id`),
5.   KEY …

It is a known fact that ext3 is not the most efficient file system out there and for example file removals can be painfully slow and cause a lot of random I/O. However, as it turns out, it can sometimes have a much more severe impact on the MySQL performance that it would seem. When or why?

When you run DROP TABLE there are several things that need to happen – write lock on a table so it cannot be used by any other thread, the data file(s) removal by the storage engine and of course in the end MySQL has to destroy the definition file (.frm). That's not all that happens, there is one other thing:

PLAIN TEXT CODE:

1. VOID(pthread_mutex_lock(&LOCK_open));
2. error= mysql_rm_table_part2(thd, tables, if_exists, drop_temporary, 0, 0);
3. pthread_mutex_unlock(&LOCK_open);
…