Knowledge Base

With that out of the way, how is this actually useful? Recently we’ve been in a situation where we had a machine with CentOS 5.x 32bit, and MySQL was causing loads up to “load average: 50.55, 49.58, 48.62″. The reason for this? CentOS 32-bit systems can not fully utilize >2 GB for MySQL, which resulted in a high created_tmp_disk_tables from MySQL. Therefore, it was constantly writing to disk for it’s operations – and therefore resulting in high I/O and bottle necking the system.

To get around this, we create a RAM-Disk and set MySQL to create temporary files on the RAM-Disk instead of the actual Hard Drive. The benefit of switching off the Hard Drive for these operations is a significant increase in speed.

We start off by making the directory:

root@test [~]# mkdir /tmp/mysql-tmp/

Get the user id and group id of MySQL:
 

root@test [~]# id mysql
uid=101(mysql) gid=103(mysql) groups=103(mysql)

Change permissions of the directory:
 

chown 101.103 /tmp/mysql-tmp/

Now we edit /etc/fstab and add the following line (replace UID and GID):
 

tmpfs /tmp/mysql-tmp/ tmpfs rw,uid=UID_HERE,gid=GID_HERE,size=2G,nr_inodes=200 k,mode=0700 0 0

This will create a 2 GB RAM-Disk which MySQL has full access to read/write to.

To mount the disk:
 

mount /tmo/mysql-tmp/

Now you can edit /etc/my.cnf to point to the new RAM-Disk directory:
 

[mysqld]
tmpdir = /tmp/mysql-tmp/

Now give MySQL a reboot and confirm that the tmpdir for MySQL is set correctly:
 

root@test [~]# /etc/init.d/mysql restart
Shutting down MySQL... [ OK ]
Starting MySQL. [ OK ]
root@test [~]# mysql -e "show variables" | grep tmpdir
tmpdir /tmp/mysql-tmp/

That’s it! You now have MySQL writing to the RAM-Disk and your load should have been significantly lowered as a result.

Important: Keep in mind once you reboot, the contents of the RAM-Disk will be gone. So do not use it as storage space, but only as temporary space.

Source: http://blog.servaxnet.com/2010/07/11...mpfs-ram-disk/