Memory backed filesystem for the MySQL tmpdir

If your queries are constantly writing to disk, and you cannot fix your query (which is strongly recommended) or properly adjust your buffer sizes for one reason or another, you can create a memory backed file system for the tmpdir, which should allow for incredibly faster writes and reads.

This should only be viewed as a temporary fix until you can get your query to stop writing its temp tables to disk.

Create the mount point:

mkdir -p /tmp/mysqltmp

Now give MySQL access to it:

chown mysql:mysql /tmp/mysqltmp

Set up the entry in /etc/fstab so it will mount on reboot:
* NOTE: Change the uid and gid to whatever MySQL runs as in /etc/passwd and /etc/group

vi /etc/fstab
# Add
tmpfs /tmp/mysqltmp tmpfs
rw,gid=27,uid=27,size=256M,nr_inodes=10k,mode=0700 0 0

Mount the memory backed file system:

mount -a

Update /etc/my.cnf and add the variable:

tmpdir = /tmp/mysqltmp

Now, restart MySQL:

service mysql restart

Finally, confirm MySQL is now utilizing the new memory backed filesystem:

mysql> show variables like '%tmpdir%';
| Variable_name     | Value          |
| slave_load_tmpdir | /tmp           |
| tmpdir            | /tmp/mysqltmp  |
2 rows in set (0.00 sec)