Using TMPFS for MySQL's tmpdir

Avatar Category: Database | 320 days ago |
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.

Comments
Any coments? Contact us.