Drop database locked the server

Question

I tried to drop one of the databases on the server. That locked the entire server up so I stopped the mysql process via the console.

I restarted the server, started mysql and now it seems hanged on:

debian-sys-maint | localhost | | Query | 192 | Opening tables | select count(*) into @discard from information_schema.COLUMNS |

Not necessarily hanged, but this takes a long time to move to a different query.

The other databases seem locked but I see no locks in "show open tables".

Any idea what's going on and what I could try?

Thanks.

Edit: it seems like the locks are moving from database to database and table to table. I guess it's doing some kind of checks?

Edit2: I think I understand what's happening, the answer to why it's doing this is: https://stackoverflow.com/questions/12567122/linux-mint-trigger-slowly-query-on-mysql-on-system-booting

Answer 1

Your conclusion is correct about the debian-sys-maint process. That's not technically part of MySQL and can be disabled.

Your DROP DATABASE gave the appearance of a lockup because opening/closing/dropping tables uses a global mutex in MySQL -- only one such operation can be happening at any point in time and every other similar operation has to wait. That wouldn't really be a problem, except that MySQL has to wait for the filesystem to report that the underlying table file(s) has been deleted, which can take a significant amount of time.

Dropping the tables individually won't eliminate the problem, but will spread out multiple, shorter blocking events over more time and be potentially less disruptive... or you might be able to use a different filesystem if dropping big tables is a frequent requirement for your environment:

http://www.mysqlperformanceblog.com/2009/06/16/slow-drop-table/ https://stackoverflow.com/questions/150058/mysql-drop-database-takes-time-why