High Number of MySQL Temporary Disk Tables

Question

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 1

First of all read the answer by Marc B It's about the reason of why you have lot of temporary tables. Anyway, the temporary tables itself are not bad, the bad thing is "on-disk temporary table" that is slow and cause much of disk IO.

To prevent a temporary table to be stored on disk try to follow these steps:

• Check the value of max_heap_table_size variable
• Check the value of tmp_table_size variable
• Check the percentage of the disk-tables among all temporary tables. Just compare show global status like 'Created_tmp_%tables' these two numbers. If the percentage is not too big - there is nothing to worry about.

Answer 2

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.

Answer 3

You have defined many indexes on tables. Have you ever though about how indexing works ?

Simply an index is a temporary table in dbase which keeps a copy of indexed column sorted. When a new row is inserted, dbase puts a new record in temp table at the correct place so an index has the following results:

A) Advantages:

1) Increase in search speed because the table is sorted in a temp table based on indexed field(s)

B) Disadvantages:

1) Slow down (Create, Update, Delete) because same actions should be done on temp tables if needed.

2) DataBase size increases because of using temp tables.

Conclusion:

Indexing is a trade-off of larger db size and slower insertion and fast searching in large amount of data. Use indexing on fields which you frequently refer to them as the search criteria (WHERE) and remove extra indexes to optimize your db design.