Using TMPFS for MySQL's tmpdir

Using TMPFS for MySQL's tmpdir: "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.
PlanetMySQL Voting:
Vote UP /
Vote DOWN"