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:
[admin@server ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sdb1 71G 4.1G 63G 7% / tmpfs 16G 4.6M 16G 1% /dev/shm
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:
mkdir /dev/shm/ mount -t tmpfs size=1G /dev/shm
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
chown mysql:mysql /dev/shm/mysql
Finally, open my.cnf and add:
Now restart MySQL:
/sbin/service mysqld restart
To verify if this worked correctly run this SQL query to create a temporary table:
CREATE TEMPORARY TABLE test_table ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
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:
echo 0 >/selinux/enforce
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.