MySQL tmpdir on RAM-disk

MySQL temporary tables are created either in memory (as MEMORY tables) or on disk (as MyISAM tables). How many tables went to disk and how many tables went to memory you can find with:

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 49094    |
| Created_tmp_tables      | 37842181 |
+-------------------------+----------+

Tables created in memory are typically faster than tables created on disk. Thus we want as many as possible tables to be created in memory.

To achieve this we can configure the variables accordingly:

mysql> SHOW GLOBAL VARIABLES LIKE '%table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 25165824 |
| tmp_table_size      | 25165824 |
+---------------------+----------+

All result sets which are smaller than these values can be handled as MEMORY tables. All result sets bigger than these values are handled as MyISAM tables an go to disk.

But there is still an other reason for tables going to disk: MEMORY tables cannot handle TEXT or BLOB attributes as it often occurs in CMS like Typo3. In these cases MySQL has to do directly MyISAM tables on disk and they are counted as Created_tmp_disk_tables.

If these temporary disk tables are causing serious I/O performance problems one could consider to use a RAM-disk instead of normal physical disks instead.

On Linux we have 2 possibilities to create a RAM-disk: ramfs and tmpfs [ 1 ].

We recommend to use tmpfs.

A RAM-disk can be created as follows:

shell> mkdir -p /mnt/ramdisk
shell> mount -t tmpfs -o size=512M tmpfs /mnt/ramdisk
shell> chown mysql:mysql /mnt/ramdisk

To make this persistent we have to add it to the fstab:

#
# /etc/fstab
#

tmpfs           /mnt/ramdisk     tmpfs   rw,mode=1777,size=512M    0       0

MySQL still writes to the default location which is found as follows:

mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+

To changes this value you have to configure your my.cnf accordingly and restart the database...