Using tmpfs (RAM + swap) with MySQL

If you’re dealing with a lot of temporary tables being created by your MySQL server, you can really benefit from using tmpfs.  With tmpfs you can mount a part of your physical memory as a volatile storage, with of course all the speed benefits of using RAM vs disk I/O operations.
A tmpfs partition can be easily created

sudo mkdir /mnt/tmpfs
sudo mount -t tmpfs tmpfs /mnt/tmpfs/


By default tmpfs uses half of your installed physical memory

df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda1 15G 2.5G 12G 18% /
udev 488M 4.0K 488M 1% /dev
tmpfs 199M 256K 199M 1% /run
none 5.0M 0 5.0M 0% /run/lock
none 498M 4.0K 498M 1% /run/shm
tmpfs 498M 0 498M 0% /mnt/tmpfs

Trying to write more data than the available size will obviously result in an error. You can specify the desired size in the mount options, just be sure not to use more than the combined size of your RAM and swap. If you do, you could effectively allocated all available memory of your system, resulting in very serious problems for your system…

To configure MySQL to use the new tmpfs system for the creation of temporary tables, we add the folowing entry in our my.cnf configuration file:

tmpdir = /mnt/tmpfs

Initially I created a 16GB tmpfs device, completely covered by physical memory. Should be enough for anyone, right?  Wrong… as your temporary tables grow in size, your tmpfs fills up. When it reaches its maximum capacity, MySQL will throw following error:

Incorrect key file for table '/tmp/#sql_1d_0.MYI'; try to repair it

To enlarge the tmpfs directory, I added an extra 50GB swap partition to my system and remounted the directory with a total of 56GB:

mkswap /dev/sdc1
swapon /dev/sdc1

mount -t tmpfs tmpfs -osize=56GB /mnt/tmpfs

Just keep in mind that when the temporary tables grow and become larger than your available RAM, you’ll see a dramatic drop in I/O speed, as tmpfs is now writing to your swap/disk device. Since the tmpfs implementation tries to fill your RAM first, before touching the swap space, you will keep the benefit of your in-RAM filesystem as long as your temporary tables keep a reasonable size.

To mount the tmpfs filesystem at boot time, add the follwing rule to your /etc/fstab file:

tmpfs    /mnt/tmpfs01   tmpfs   rw,mode=0755,size=275G 0 0