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

Thu, 2013/03/21 - 10:19

Ubuntu 12.04, anyone getting "start: Job failed to start" other than me. MySQL doesn't seem to like the new directory.

Thanks!

Thu, 2013/03/21 - 11:28

Check /var/log/upstart/mysql.log. It will have some more specific info in it.

Tue, 2013/03/26 - 12:09

I got the same error - it turned out to be AppArmor, and this fixed it for me:

http://askubuntu.com/questions/175820/mysql-doesnt-update-due-to-error-in-apparmor-profile

Tue, 2013/03/26 - 12:18

Yes, I remember something like that, then editing the file /etc/apparmor.d/usr.sbin.mysqld, and adding the following to it:

/run/mysqltmp/** rwk,

But then reverted to using /run/mysqld.

We always disable apparmor, since it is more of a pain than it is worth.

Fri, 2014/05/02 - 15:19

I also got the message "start: Job failed to start" when following the instructions. I am running Ubuntu 13.04.

I ran sudo apparmor_status to see if Apparmor was loading any policies for MySQL and it was!

I added the below to /etc/apparmor.d/local/usr.sbin.mysqld:

# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
/run/mysqld/ r,
/run/mysqld/** rwk,

Then I ran sudo service apparmor reload and the above instructions by Khalid worked! Woohoo, now I have a working MySQL tmp directory, thanks Khalid!

Source: https://blogs.oracle.com/jsmyth/entry/apparmor_and_mysql

Fri, 2013/04/26 - 14:06

A colleague linked me to your resource. Thanks for the information.

Thu, 2014/02/20 - 02:39

Hi,

I followed all steps mentioned still mysql query is copying tmp table to disk

Here are all that i did

#id mysql
uid=101(mysql) gid=104(mysql) groups=104(mysql)

#cat /etc/fstab | grep tmpfs

tmpfs /data/mysql_tmp/ tmpfs rw,gid=104,uid=101,size=10g,nr_inodes=10k,mode=0700 1 2

# mount -a

#df -kh | grep tmpfs

tmpfs 10G 0 10G 0% /data/mysql_tmp

mysql> show variables like '%tmpdir%';
+-------------------+------------------+
| Variable_name | Value |
+-------------------+------------------+
| slave_load_tmpdir | /data/mysql_tmp/ |
| tmpdir | /data/mysql_tmp/ |
+-------------------+------------------+

#ls -l | grep tmp
drwx------ 2 mysql mysql 40 Feb 20 12:54 mysql_tmp

#/etc/init.d/mysql restart
Shutting down MySQL...... [ OK ]
Starting MySQL. [ OK ]

RAM Size 16GB

#top | head | grep Mem
Mem: 16436552k total, 16217048k used, 219504k free, 46248k buffers

#free
total used free shared buffers cached
Mem: 16436552 16219072 217480 0 46796 15138292
-/+ buffers/cache: 1033984 15402568
Swap: 16779852 910012 15869840

Table size on disk

#ls -lh db/TRN_ACT.MY*

-rw-rw---- 1 mysql mysql 5.6G Dec 14 12:25 db/TRN_ACT.MYD
-rw-rw---- 1 mysql mysql 1.0K Dec 14 12:25 db/TRN_ACT.MYI

But still query uses disk

#mysqladmin proc -i2 | grep Q
| 2 | root | localhost | mpulse | Query | 165 | Copying to tmp table on disk | select TASA_TRANC_DATE, TASA_OPERATOR_CIRCLE_ID, TASA_PRODUCT_ID, TASA_PLATFORM_ID, TASA_STATUS_ID, |

Kindly help, am i doing it properly..?

Fri, 2014/02/21 - 09:53

Post the output from the following commands:

# mysqladmin proc -v

# mysql db
mysql> SHOW CREATE TABLE TRN_ACT;

Fri, 2014/08/29 - 03:35

I agree with Georg Wächter, seems really foolish to use a RAM disk to store the temporary tables for operations that does not fit in memory.

It's obvious that if MySQL have enought RAM available, it will not use disk-based temporary table.
Also obvious is that if you create a RAM disk in the server, you have much less memory for MySQL.
You all will do better if you remove the RAM disk and tune MySQL to use that RAM.

Perhaps not so obvious is the fact that if the data does not fit in a tunned MySQL memory, it will still not fit in a RAM disk: it's still the same RAM available to the server, unless the server starts swappin to allocate more memory than available, which will be a huge performance lost.

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

I can achieve same and better results by fine-tunning MySQL to use all this RAM. That only proves you don't know how to configure MySQL and you messed up your server and all the servers that followed that guide.

Fri, 2014/08/29 - 12:08

I hate to break it to you, but even when MySQL has loads of available memory it will still create a temp table on disk for many types of queries that are pretty common in Drupal, and that is what this article is all about.

Some details in this article.

I don't think you can say that those guys "Don't know how to configure MySQL".

However these days we mostly just use SSDs for all DB servers and rarely have a case where we have to leverage this trick.

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