For sites 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 query 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.

This is often done by creating either a RAM Disk, or the easier to use tmpfs. Both are a portion of the server's RAM memory made to emulate a disk with slightly different details: RAM disk has a file system on it that can be ext3 or anything, while tmpfs is its own file system type.

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 describe here methods 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           1.6G  260K  1.6G   1% /run
...

This tells us that the the /run filesystem is of type temporary file system, and has 1.6 GB allocated for it.

# mount
...
tmpfs on /run type tmpfs (rw,noexec,nosuid,size=10%,mode=0755)
...

On Ubuntu 12.04 LTS, the directory /run/mysqld already exists and is allocated to a tmpfs with sufficient space for temporary files.

Save yourself some grief and do not try to create your custom directory under /run (e.g. mysqlslow), because it will not survive reboots, and MySQL will not start after a reboot.

So, all we need is telling MySQL to use this directory.

To do this, create a file called /etc/mysql/conf.d/local.cnf. By using this file, and not editing /etc/mysql/my.cnf, we don't have Ubuntu updated overwrite your changes.

Add this to the file:

[mysqld]
tmpdir = /run/mysqld

Then restart MySQL

service mysql restart

Then make sure that the new value is now in effect:

# mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| tmpdir        | /run/mysqld |
+---------------+-------------+

Method 2: Creating a new tmpfs directory

If you are not running Ubuntu 12.04 LTS, 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 the tmpfs directory:

Create the tmp directory

# mkdir -p /var/mysqltmp

Set permissions

# chown mysql:mysql /var/mysqltmp

Determine mysql user id

# id mysql

Edit /etc/fstab

And add the following line, replacing your specific mysql user id and group id instead of the 105 and 114 below:

tmpfs /var/mysqltmp tmpfs rw,gid=105,uid=114,size=256M,nr_inodes=10k,mode=0700 0 0

Mount the new tmpfs partition

# mount -a

Change your MySQL configuration

# vi /etc/mysql/conf.d/local.cnf 

Change, or add the following line:

tmpdir = /var/mysqltmp

Restart MySQL

/etc/init.d/mysql restart

Or, for Ubuntu 12.04:

service mysql restart

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 are the graphs 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.

CPU usage. Note how much iowait (magenta) before and after the change:

And how many slowqueries per second before and after the change:

The server's load is less too:

The Input Outputs per second on sda (where the /tmp is, which was the destination for slow queries before the change.

Comments

Tue, 2013/03/19 - 06:05

How does this compare to using the memory in other ways? I've not needed to tune MySQL, but can't you just increase it's memory allocation? If you give it the same amount of extra memory as you were going to allocate to your RAM disk then I'd hope it would be clever enough to use it and not write temporary tables to disk in the first place.

Thu, 2013/03/21 - 09:53

Tuning mysql to use more memory would be definitely the preffered approach as i've written above. See the link to the mysql documentation: http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

Furthermore, this has the advantage that Mysql uses algorithms optimized for in-memory storage. In contract to the ram disk solution where the storage format is optimized for hard disks - trying to reduce random accesses where possible.

Thu, 2013/03/21 - 12:45

Whether there are other avenues for doing something similar or not, really depends on the site. There are often several ways to achieve the same result in many cases.

This site has lots of queries that scan large tables and join them to other large tables

The idea here is that this works, and is proven. See the graphs. They speak for themselves.

Thu, 2013/03/21 - 15:20

Maybe it was enough to solve your problem, but it's not an approach I'd recommend for other people when a little bit of further research should provide a native MySQL solution that is likely to perform even better.

Thu, 2013/03/21 - 15:40

You obviously have a different opinion than mine here, so let us agree to disagree ...

Tue, 2013/03/19 - 08:10

Hello,
Thank you for useful tip! It looks like it might make performance difference. Could you please advice how to resolve an issue I got after making changes you've described? This is it:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB': SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available() (line 167 of /includes/lock.inc).

Thank you!

Thu, 2013/03/21 - 09:06

The directories above are messed up. First you create ramdisk on /var/lib/mysqltmp, then you tell Mysql to use /var/run/mysqltmp
Change tmpdir in mysql config to /var/lib/mysqltmp to resolve this.

Thu, 2013/03/21 - 09:20

Thanks, fixed.

Tue, 2013/03/26 - 08:47

Thank you for tip. I did mention different paths and make appropriate changes so both paths in fstab and local.cnf are the same:

fstab
tmpfs /var/lib/mysqltmp tmpfs rw,gid=123,uid=113,size=256M,nr_inodes=10k,mode=0700 0 0

local.cnf
tmpdir = /var/lib/mysqltmp

but getting same Unknown table engine 'InnoDB' error again.

my df -h (on Ubuntu 11.10) looks like this:

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda5              54G   43G  8.6G  84% /
udev                  873M  4.0K  873M   1% /dev
tmpfs                 352M  888K  351M   1% /run
none                  5.0M     0  5.0M   0% /run/lock
none                  880M  660K  879M   1% /run/shm
tmpfs                 256M     0  256M   0% /var/mysqltmp
/dev/sda1             120G   88G   33G  73% /media/sda1
/dev/sda2             123G   92G   31G  75% /media/sda2

It might be something else but I'm not sure where to move from here. Please advice.

Tue, 2013/03/26 - 11:25

It could be the permissions and/or ownership on the new directory that you created.

Change the tmpdir to /run/mysqld, and see if it works.

Pages

Is your Drupal or Backdrop CMS site slow?
Is it suffering from server resources shortages?
Is it experiencing outages?
Contact us for Drupal or Backdrop CMS Performance Optimization and Tuning Consulting