High Number of MySQL Temporary Disk Tables
We have noticed that MySQL is reporting a very high number of temporary disk tables (over 10,000) this is reported by Server Density. Were trying to understand a bit more about this.
- Why are temporary disk tables created by MySQL?
- What impact do they have on performance?
- Are they ever removed by MySQL or will this number just increase?
Answer
Temp tables can be created for lots of reasons. Any select operation which has a large data set and requires sorting will be written into one. Actual temp tables created by queries directly (TEMPORARY table type) are done on a per-connection basis, so if you've got a script with 50 connections each doing the same temporary table, thats 50 sets of on-disk temp files for them. temp table creation reason group by order by and distinct by Disk-based i/o is the most expensive part of a DBMS, generally, so if these tables are for large data sets, you're probably limiting DB performance to that of your i/o system. But generally, by just existing, they're only chewing up disk space and not much else.
Temp tables for sorting purposes should clean themselves up when the query completes. Temp table of the 'TEMPORARY' type will clean themselves up when the connection they're attached to is closed. If you're using persistent connections, then the TEMPORARY tables will stick around until you (or a program) DROPs them manually.