Reduce your server's resource usage by moving MySQL temporary directory to tmpfs

Published Mon, 2013/03/18 - 22:05, Updated Thu, 2013/03/21 - 13:01

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.

I agree with Georg Wächter,

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.

I hate to break it to you,

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.

Not working

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..?

The query and table definition

Post the output from the following commands:

# mysqladmin proc -v

# mysql db
mysql> SHOW CREATE TABLE TRN_ACT;

Thanks

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

start: Job failed to start

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

Thanks!

Got it working with Apparmor

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

I got the same error - it

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

Apparmor

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.

Check the logs

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

Also, bear in mind that tmpfs

Also, bear in mind that tmpfs is not quite the same as a RAM disk - it will use RAM if available, but if the size of the disk exceeds available RAM (which may be never, or almost always depending on your typical temporary table size and what RAM you leave free after setting the InnoDB buffer pool) it will also use disk based swap for storage.

See Tmpfs for more details.

I see what you mean

I see what you mean now Owen.

I started out trying to create the older style RAM Disk, formatted as ext2. But the default sizes were too small (16MB).

Then I upgraded from 10.04 (or was it 8.04) to 12.04, and noticed that there is already a tmpfs large enough to use without the hassle to create it from scratch.

Yes

Yes, which is fine, rather than running out of space on the temporary directory.

But the improvement in resource usage is remarkable, as evidenced in the graphs.

Caching?

Would this technique be beneficial for sites that are already running behind a caching proxy such as Varnish?

Depends

Depends on the site's specific situation.

If you are not seeing lots of slow queries being logged, or high disk contention, then no need to do it.

Why dont you increase the

Why dont you increase the Mysql Buffer sizes that control when mysql converts an in-memory temporary table to an on-disk table? This should give you the same boost using a much more simple approach.

See MySQL internal temporary tables for details, especially the mentioned variables at the bottom of the page.

That can only get you so far.

That can only get you so far. Any query that joins table A to table B, filters on table A, and sorts on table B _must_ use a temp table, regardless of buffers or other settings. This scenario is very common in Drupal (due to fields separate from the parent entities). So most slow queries cannot be improved by tweaking buffers.

Thats perfectly fine. The

Thats perfectly fine. The point is not to prevent temporary tables but to let MySql create them in-memory and not on hard disk.

The problem with the built-in

The problem with the built-in memory tables is that they - as far as I know - don't work with (e.g.) BLOB fields. These are always handled in MyISAM tables - and thus written to disk.

Unknown table engine 'InnoDB' ??

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!

The directories above are

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.

Thanks

Thanks, fixed.

Thank you for tip. I did

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.

Change the tmpdir

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.

Sorry, nothing still, even

Sorry, nothing still, even if:

tmpdir = /run/mysqltmp
tmpfs 256M 0 256M 0% /run/mysqltmp
272275 drwxrwxrwx 2 mysql mysql 40 2013-03-26 17:08 mysqltmp

What about other memory settings?

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.

Tuning mysql to use more

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.

It worked ...

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.

For you, maybe

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.

Let us disagree then

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

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