How to Change or Move MySQL /tmp Directory to tmpfs

Janeiro 31st, 2018

For websites that have lots of slow queries, disk access is often the bottleneck. For these slow queries, MySQL writes temporary tables to disk, populates them with intermediate results, then queries them again for the final result.

We all know that the disk is the slowest part in a computer, because it is limited by being mechanical, rather than electronic. One way of mitigating this is to tell MySQL to use memory rather than disk for temporary tables.

Since memory access is much faster than a disk, this improves performance, and decreases load on the server by not causing pile up bottlenecks on disks.

We will use this tutorial to describe a method to achieve this goal.

Method 1: Using an existing tmpfs directory

Rather than creating a new ram disk or tmpfs mount, we first search for one that is already on your server.

• df -h

Filesystem      Size  Used Avail Use% Mounted on
...
tmpfs           3.9G     0  3.9G   0% /dev/shm
...

This tells us that the the /dev/shm filesystem is of type temporary file system, and has 3.9 GB allocated for it. So, all we need is to tell MySQL to use this directory.

• chmod 1777 /dev/shm

Now open /etc/my.cnf file for editing, and add the bellow line under the [mysqld] section and save the file.

• vi /etc/my.cnf

[mysqld]
tmpdir = /dev/shm

Then restart MySQL.

• service mysqld restart

or

• service mysql restart

To finalize make sure that the new value is now in effect:

• mysql

SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| tmpdir        | /dev/shm    |
+---------------+-------------+

Method 2: Creating a new tmpfs directory

If you are not running CentOS 6/7, then you may not have a ready made RAM disk that you can use, and you have to create one.

Here are the steps to create a new tmpfs directory starting by creating the tmp directory:

• mkdir -p /var/mysqltmp

Set permissions:

• chown mysql:mysql /var/mysqltmp

Now lets determine mysql user id, take note of the user id (uid) and group id (gid) because you’ll need them in the next step.

• id mysql

uid=27(mysql) gid=27(mysql) groups=27(mysql)

Edit /etc/fstab and add the following line, replacing your specific mysql user id and group id instead of the 27 below:

• vi /etc/fstab

tmpfs /var/mysqltmp tmpfs rw,gid=27,uid=27,size=1024M,nr_inodes=50k,mode=0700 0 0

To finish mount the new tmpfs partition.

• mount -a

Now open /etc/my.cnf file for editing, and add the bellow line under the [mysqld] section and save the file.

• vi /etc/my.cnf

[mysqld]
tmpdir = /var/mysqltmp

Then restart MySQL.

• service mysqld restart

or

• service mysql restart

To finalize make sure that the new value is now in effect:

• mysql

SHOW VARIABLES LIKE 'tmpdir';
+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| tmpdir        | /var/mysqltmp |
+---------------+---------------+

How much of a difference does it make?

How much of a difference can you expect from moving MySQL’s temporary files from disk to a RAM? Significant, if your server has lots of slow queries.

Here is a graph from a site that was suffering considerably because of a large number of logged in users (averaging 250 at peak hours, and exceeding 400 at times), and some other factors.

Using a RAM disk made a noticeable difference on how many slowqueries per second before and after the change were registered.