Move MySQL/MariaDB tmpdir to tmpfs/ramdisk

Posted on August 19, 2014 Comments

With any MySQL/MariaDB database, you always want to optimize your queries and tune the settings to avoid needing a temporary table to be created on disk. However, there are some query types that make avoiding a temporary table impossible. How many temporary tables has your server created? To find out, run the SHOW GLOBAL STATUS command. In my case running a large scale forum database, I had thousands of temporary table creations in a day, often creating some temp tables every few seconds. Obviously, this causes significant disk i/o.

If your count is high and you’ve done everything possible to optimize queries, you may want to try moving MySQL’s tmpdir to a ramdisk. This is a temporary filesystem in memory, so it does not have the i/o constraints or speed issues that a hard disk does.

By default, MySQL writes to the /tmp/ folder on CentOS and RedHat Linux based systems. If you are using these, you may already have a tmpfs mounted. To verify if you do, run df -h:

In this return, we see that a tmpfs is already running in /dev/shm. If this is not the case, we can go ahead and create it:

Choosing the appropriate size for your tmpfs depends on your database and the size of temporary files being written. I recommend glancing over /tmp/ to see the general size of files being written, but in general, 500MB is more than enough. The example makes it 1GB just to be safe. On my example system, a 16GB tmpfs is automounted but this is overkill for our purposes however there is no harm in using it anyway.

Next, create a directory for the temporary files:

And set ownership to /dev/shm/mysql

Finally, open my.cnf and add:

Now restart MySQL:

To verify if this worked correctly run this SQL query to create a temporary table:

If it worked, then the tmpdir setting was a success. If it did not and gave you an error about being unable to create it or unable to select database, recheck that everything was completed and that tmpfs is mounted. If it didn’t work or is giving you an error about not having permission, you need to either disable SELinux or adjust it. You can disable SELinux temporarily for the current boot with this command:

Warning: tmpfs is exactly as it’s named: a temporary filesystem. As said before, it’s stored in RAM/memory. If the server is powered off unexpectedly for any reason, the data in the RAMdisk will be lost if it was not written back before being removed. While MySQL is robust and can survive crashes quite well in my experience, you might still lose data being processed “in the moment” if it happens. REPAIR TABLE queries may be necessary to deal with the damage.

Comments

  1. /dev/shm is chmod o+t so temp files are protected anyway. A directory under there is likely to be removed on reboot so I’d just go for /dev/shm.

    Rather than forcing a disable all on selinux, you can disable it just for mysql:

    semanage permissive -a mysqld_t

    If you want to generate a policy so you don’t need to disable it continue on:

    when tmpdir is set try:

    mysql -e 'create temporary table xxx ( xx int ); insert into xxx values (3),(4),(5);'

    This create some audit messages:

    grep mysqld /var/log/audit/audit.log | audit2allow -m MySQL_tmpfs -o MySQL_tmpfs.te

    I got this as MySQL_tmpfs.te if you want to skip all the previous steps:

    module MySQL_tmpfs 1.0;

    require {
    type tmpfs_t;
    type mysqld_t;
    class dir { write search read remove_name open getattr add_name };
    class file { write getattr read lock create unlink open };
    }

    allow mysqld_t tmpfs_t:dir { write search read remove_name open getattr add_name };

    allow mysqld_t tmpfs_t:file { write getattr read lock create unlink open }

    Create a module, compile it, load it, set selinux back to enforcing for mysqld_t

    checkmodule -M -m MySQL_tmpfs.te -o MySQL_tmpfs.mod
    semodule_package -m MySQL_tmpfs.mod -o MySQL_tmpfs.pp
    semodule -i MySQL_tmpfs.pp
    semanage permissive -d mysqld_t

    too easy. Some distros have moved to /run/shm but the blog and comment here still apply.

    • Thank you for your input Dan. I wasn’t sure at the time whether to create a folder or not, and you are right – it will be removed on reboot/shutdown. I’ve actually included an mkdir statement in my start up scripts before MySQL fires up but I failed to mention that in my blog post.

      I didn’t realize you could disable selinux just for MySQL either. I usually disable selinux entirely though – only because I’m not good at managing it, though.

Leave a Reply