MySQL Created_tmp_disk_tables

Question

I'm trying to improve the performance of our primary MySQL server. It's a Dual Quadcore Intel X5450, with 8GB of RAM. It's a dedicated MySQL box. I'm only in the beginnings of configuring it, so I'm starting relatively fresh.

I noticed that Created_tmp_disk_tables was awfully high, at 330k. Created_tmp_tables is only at 380k. This might not be too bad, except that MySQL was restarted less than 4 hours ago.

I've set the following:

15:31:15 (9) > show global variables like '%table_size%'; +---------------------+------------+ | Variable_name | Value | +---------------------+------------+ | max_heap_table_size | 1073741824 | | tmp_table_size | 1073741824 | +---------------------+------------+ 

This page suggests that the smaller of the two values determines when a temporary table becomes a disk-based table. I would think (personally) that 1GB would be more than large enough to handle any sized temporary table that we'd be creating, but apparently, I'm incorrect.

Can anyone suggest other methods to keep the number of disk-based temporary tables down?

Answer 1

If temporary tables are over utilized, my first thought is that the application using the database is in need of query optimization.

Answer 2

There are other reasons than just the size that results in an implicit tmp table being created on disk rather than memory, such as:

• If the resulting table contains columns of type BLOB or TEXT.
• Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used.
• The SHOW COLUMNS and DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

Source: 8.4.4 Internal Temporary Table Use in MySQL (dev.mysql.com documentation)

So, you would need to limit the above to keep temp tables on disk low.

By the way, if you're selecting TEXT columns, it's still possible to avoid temp tables on disk by selecting only a smaller part of them via e.g. the SUBSTR function. This is described here:

Avoid temporary disk tables with MySQL (fromdual.com)