MySQL and RAMdisk, or how to make tmpdir-usage queries faster

Have you ever looked at your processlist and seen queries in the state “copying to tmp table” and then run an explain on it and noticed that the tmp table is being created on disk? Happens a lot with some servers and some workloads. Of course disk is much slower than RAM so this becomes a slow process and makes queries execute slower than they could if they were allowed to use RAM. So, one way to get this process to speed up (aside from tuning your queries which should be done first) is to create a tmpfs or ram-disk and let MySQL use that for it’s temp-table-on-disk creations. MySQL on Linux defaults to /tmp for the tmpdir location so this will need to be changed.

Here is how you get MySQL to use a 1G size tmpfs. How you size your tmpfs depends how much ram your system has and how much tmpdir space mysql needs for your workload. If you need more tmpdir space than you can make in a tmpfs mount then mysql allows multiple tmpdir locations to be set (see the manual: http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_tmpdir).

0. mkdir -p /db/tmpfs01 && chown mysql:mysql /db/tmpfs01
1. mount tmpfs /db/tmpfs01 -t tmpfs -o size=1G
3. edit my.cnf file and add: tmpdir = /db/tmpfs01
4. restart MySQL

To have persistent tmpfs with reboot put the following into /etc/fstab. On this system the mysql user is uid/gid of 27, yours might vary so adjust accordingly.
tmpfs /db/tmpfs01 tmpfs rw,uid=27,gid=27,size=1G,nr_inodes=10k,mode=0755 0 0

Tagged

12 thoughts on “MySQL and RAMdisk, or how to make tmpdir-usage queries faster

  1. Robert Lankey says:

    Loved the idea of using ramdisk, but I just wanted to point out something that I found extremely important. Our implementation of a ramdisk temporary directory was intended to speed up read operations on our slave systems. In initial testing all was right with the world. That is, until I read that little blurb at the end of that option description:

    ” If the MySQL server is acting as a replication slave, you should not set –tmpdir to point to a directory on a memory-based file system or to a directory that is cleared when the server host restarts.”

    Ouch. Turned out to be a case of RT(Entire)FM.

    I’m hoping that at some point in the future we can make some distinction between a temporary directory where MySQL will store its files and a temporary directory that we can use, but for the moment, a tmpdir ramdisk solution isn’t advised for replication slaves.

  2. admin says:

    Robert, there is an option for slaves to use their own tmpdir location. If you read the rest of the manual… http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_slave-load-tmpdir

  3. John says:

    Did you try using this on a master/slave replication. does this mean i can put on both tmpfs for tmpdir as long as i keep current temp folder for slave-load-tmpdir in the settings of the slave ?
    Not sure I fully get it so any help is welcome on this.

  4. John says:

    any chance to get my previous comment published as i really need an answer on it before messing with my production server. Thanks

  5. admin says:

    It works fine with replication, it’s just putting data into ram instead of on disk.

  6. John says:

    Just to be sure. I need to make this mount operations on both master and slave with just slave-load-tmpdir difference, then restart and relaunch replication? or do i do it only on master ?

  7. John says:

    can we leave replicated server on disk to avoid ram for it as i guess speed is not the issue on replicated db.

  8. Spechal says:

    Just as an FYI, tmpfs is dynamic and can use swap space and still hit the disk if it runs out of space. You’d need to adjust the swapiness setting or use a partition (such as making an ext2 partition on /dev/ram1 and then mounting it someplace like /ramdisk).

  9. Nobe says:

    This method not working for me, because SELinux policy do not give support to mysqld_t to use tmpfs_t.
    The solution for me, is ‘chcon -R -t tmp_t /db/tmpfs01′ after ‘mount tmpfs /db/tmpfs01 -t tmpfs -o size=1G’.

  10. Camille says:

    Isn’t it a better idea to set bigger buffer for sort and tmp objects? So that MySQL can use available memory in a optimized way?

  11. gggeek says:

    @Camille mysql will still use disk for some temp tables regardless of their size (eg. when sorting by blob cols etc)

  12. Ven says:

    I am considering to make tmpfs for tempdir, but what will happen if space is not enough? tmpdir option can have multiple path, but as I understand it, those paths will be looped, so when query is executed it gets one path (tmpfs for example) and work with that. If space is not enough… I don’t see any information that next dir will be used instead. And if next is not used, then what will happen when ramdisk space is too small?

    Is there some way to measure current usage of tmp space, so to create my tmpfs accordingly?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>