Slow DROP TEMPORARY TABLE

Ran into an interesting problem开发者_如何转开发 with a MySQL table I was building as a temporary table for reporting purposes.

I found that if I didn't specify a storage engine, the DROP TEMPORARY TABLE command would hang for up to half a second.

If I defined my table as ENGINE = MEMORY this short hang would disappear.

As I have a solution to this problem (using MEMORY tables), my question is why would a temporary table take a long time to drop? Do they not use the MEMORY engine by default? It's not even a very big table, a couple of hundred rows with my current test data.

Temporary tables, by default, will be created where ever the mysql configuration tells it to, typically /tmp or somewhere else on a disk. You can set this location (and even multiple locations) to a RAM disk location such as /dev/shm.

Hope this helps!

If the temporary file is created with InnoDb engine, which may be the case if your default engine was InnoDb, and the InnoDb buffer pool is large, DROP TEMPORARY TABLE may take some time since it needs to scan all pages to discard. It was mentionned in a comment to this stack overflow question.

Note also that DROP (TEMPORARY) TABLE uses a LOCK that may have huge impact on all your server. See for example this.

At my work, we recently had a server slow down because we had an InnoDb buffer pool of 80 Gb and some SQL requests had been optimized using InnoDb temporary tables. About 100 such DROP TEMPORARY TABLE requests every 5 minutes were sufficient to have a huge impact. And the problem was hard to debug since slow query log would tell us that UPDATEs of a single row accessed by primary key in some other table was taking two seconds, and there was an enormous amount of such updates. But even if most query time was spent on these updates, the problem was really because of the DROP TEMPORARY TABLE requests.