Using TMPFS for MySQL's tmpdir

[Chris]

There have been a lot of systems I have looked at where the Created_tmp_disk_tables was very high. MySQL has good documentation on how it uses memory, specifically temp tables, here.

I would like to talk about not "why" MySQL does this but how to speed up the performance when MySQL internally or users create temporary tables to disk. A great solution is TMPFS, a quick how to is as follows:

-- Before you start
1. Make sure you allocate enough space to TMPFS
-- 2GB is usually safe but if you are using larger data sets with inefficient queries then there are far worse performance issues to deal with.

-- The safe way to implement TMPFS for MySQL
shell> mkdir /tmp/mysqltmp
shell> chown mysql:mysql /tmp/mysqltmp
shell> id mysql
##NOTE: make sure you get the uid and gid for mysql
shell> vi /etc/fstab
## make sure this in in your fstab
tmpfs /tmp/mysqltmp tmpfs rw,uid=25,gid=26,size=2G,nr_inodes=10k,mode=0700 0 0
shell> mount /tmp/mysqltmp
shell> vi /etc/my.cnf #or the mysql config file for your server
## NOTE: inside the file add the following under [mysqld]
tmpdir=/tmp/mysqltmp/
shell> service mysql restart

-- The not so safe way to implement TMPFS for MySQL
shell> chown mysql:mysql /tmp
shell> id mysql
## NOTE: make sure you get the uid and gid for mysql
shell> vi /etc/fstab
## make sure this in in your fstab
tmpfs /tmp tmpfs rw,uid=25,gid=26,size=2G,nr_inodes=10k,mode=0700 0 0
shell> ##move everyting out of /tmp
shell> mount /tmp

It is possible to run the "not so safe" implementation but if you can take scheduled downtime the "safe way" is the best way to go!

The performance benefits for the TMPFS addition are great so try it out and let us know what you think after your implementation.

[Chris]

Rob,
If you have large tables you might want to try "What's faster than Alter".

Mark,
Great post and it would be very nice to be able to specify multiple temp directories for different MySQL activities, both internal and user based.

Cheers

[Mark R]

Unfortunately there is no way to specify a different temp dir for temporary tables and filesort files. Filesort files can get much larger than temporary tables, and you get very large ones created during MyISAM REPAIR and ALTER operations.

A MyISAM repair / alter can easily create a filesort file (or more than one; normally several) which is much bigger than the table it is repairing.

If it runs out of space trying to do a filesort, it may revert to the performance-runious "Reparing with keycache"

It seems to be approximately 2x the uncompressed, unpacked total size of the data in the columns in the largest single index in the table which is required. This includes expanding utf8 varchar(255)s to about 768 bytes.

I don't know if InnoDB repair / alter uses a filesort.

Filesorts are also usually used for queries which use a ORDER BY or GROUP BY on something which isn't the index being scanned, but as (useful) queries normally only return a relatively small amount of rows, they don't matter so much.

Also the impact of a filesort filling up your tmpfs during a SELECT query is less than if it happens during a repair or alter. The caller simply gets an error.

[Rob Wultsch]

Please note that any ALTER's that you do that require a REPAIR BY SORTING will make use of tmpdir. If you have large tables this can be less than ideal.