Why does MySQL produce so many temporary MYD files?

Question

On a Debian Linux server, hosting many PHP/MySQL websites (photo galleries), sometimes I have "many" files like /tmp/#sql_6405_58.MYD.

For example today :

[2012-12-15 15:18:11] /tmp/#sql_6405_6.MYD : 88MB
[2012-12-15 15:18:11] /tmp/#sql_6405_3.MYD : 22MB
[2012-12-15 15:18:11] /tmp/#sql_6405_4.MYD : 138MB
[2012-12-15 15:18:11] /tmp/#sql_6405_10.MYD : 88MB
...
[2012-12-15 15:18:11] /tmp/#sql_6405_9.MYD : 15MB
[2012-12-15 15:18:11] /tmp/#sql_6405_65.MYD : 49MB
[2012-12-15 15:18:11] /tmp/#sql_6405_44.MYD : 69MB

(59 files at the same time, for more than 6GB... yes I monitor big files in /tmp)

Unfortunately, /tmp is on the same partition than / and it temporary breaks the web server, because / is full I suppose. Then files disappear and the server is back to normal.

All the file names follow the #sql_6405_*.MYD pattern. I would like to understand which MySQL operation implies so many temporary files. I have approximately 2000 databases on this server. Is it possible to know which database is concerned?

I‘m guessing they‘re temp data for large operations that use filesort, and 6405 is the PID of mysql to keep temp files from different server instances separate. – Marc B Dec 15 ‘12 at 20:27 — Marc B Dec 15 ‘12 at 20:27, Dec 15 ‘12 at 20:27
Should I ask an admin to move my question? – plegall Dec 15 ‘12 at 22:06 — plegall Dec 15 ‘12 at 22:06, Dec 15 ‘12 at 22:06

RolandoMySQLDBA · Answer 1 · 2012-12-17 23:19:51Z

There are some options that can cause temp tables to materialize as MyISAM tables or can be configured to delay it. Keep in mind that for disk-based temp tables, there are no .frm files, but only .MYD and .MYI files (of course. the .MYI file is never used since it is impossible index an internal temp table).

Here are the options:

Temp Table Variables That Should Be the Same Size
- tmp_table_size
- max_heap_table_size
sort_buffer_size
join_buffer_size

You should also consider the MySQL Documentation on Internal Temp Table Usage

The situations where in-memory temp tables are made are

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
DISTINCT combined with ORDER BY may require a temporary table.
If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

When an in-memory temp table exceeded the minimum of (tmp_table_size or max_heap_table_size), mysqld does the following:

Suspends the query
Copies the in-memory table‘s contents into a MyISAM temp table
Discards the in-memory table
Continues the query, sending the temp data into the MyISAM temp table

The situations where in-memory temp tables are bypassed in favor of disk are

Presence of a BLOB or TEXT column in the table
Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes
Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used

Some due diligence is required to reduce temp table creation on disk

Setting join_buffer_size bigger
Setting sort_buffer_size bigger
Setting tmp_table_size and max_heap_table_size bigger
Tuning queries to minimize or even prevent temp tables
Creating indexes to create presorted view of data from individual tables
Installing additional RAM to accommodate large in-memory temp tables

If after such due diligence, there are still temp tables being formed on Disk, here is one desperate move: Mapping disk-based temp table creation to memory.

Here is a quick-and-dirty way to set up a 16GB RAM Disk using tmpdir

STEP01) Create RAM Disk Folder

mkdir /var/mysql_tmpfs

STEP02) Add this to my.cnf

[mysqld]
tmpdir=/var/mysql_tmpfs

STEP03) Add this to /etc/fstab

echo "none /var/mysql_tmpfs tmpfs defaults,size=16g 1 2" >> /etc/fstab

STEP04) Reload /etc/fstab

mount -a

STEP05) service mysql restart

After this, all temp table that become MyISAM are written to the RAM Disk. This should speed disk-based temp table creation.

Give it a Try !!!

Valerie Parham-Thompson · Answer 2 · 2012-12-16 05:50:56Z

These are queries that are rolling over onto disk because the results are too large for memory.

When the query is done, the space clears.

There‘s no way to match these temp files definitively to queries, but you can get clues to make a good guess from SHOW FULL PROCESSLIST; or SHOW INNODB STATUS; or by looking in your error log if the queries fail.

Vinay · Answer 3 · 2012-12-16 13:48:40Z

Whenever we use alter statements on table It creates the #sql_6405_3.MYD temporay files and once it‘s done throws the output and disappears.

Alter on tables make MySQL to copy whole data into temporary files #sql.xxx.MYD and make changes to created temporary files then drop original data files tablename.MYD and renames the temporay files to table name.

Also for some kinda sorting queries it creates temporary files.

As I traced out. This thing happens.