mysql "drop database" takes time -- why?

Question

mysql5.0 with a pair of databases "A" and "B", both with large innodb tables. "drop database A;" freezes database "B" for a couple minutes. Nothing is using "A" at that point, so why is this such an intensive operation?

Bonus points: Given that we use "A", upload data into "B", and then switch to using "B", how can we do this faster? Dropping databases isn't the sort of thing one typically has to do all the time, so this is a bit off the charts.

Answer 1

So I'm not sure Matt Rogish's answer is going to help 100%.

The problem is that MySQL* has a mutex (mutually exclusive lock) around opening and closing tables, so that basically means that if a table is in the process of being closed/deleted, no other tables can be opened.

This is described by a colleague of mine here: http://www.mysqlperformanceblog.com/2009/06/16/slow-drop-table/

One excellent impact reduction strategy is to use a filesystem like XFS.

The workaround is ugly. You essentially have to nibble away at all the data in the tables before dropping them (see comment #11 on the link above).

Answer 2

Following off of skaffman:

Change your my.cnf (and restart MySQL) to include:

innodb_file_per_table = 1

(http://mysqldba.blogspot.com/2006/12/innodbfilepertable.html)

This will give your databases dedicated file storage and take it out of the shared pool. It will then let you do fun things like place the tables/indexes on different physical disks to even further split up I/O and improve performance.

Note this doesn't change existing tables; you'll have to do work to get 'em in their own file (http://capttofu.livejournal.com/11791.html).