Over the last 20-odd years I've been developing software, very rarely have I had an excess of computing resources. More often than not, I would be asking a simple machine to do far more than the average developer might, and the machine would do the best it could with the resources available, processor fans spinning at full speed the whole time. Since fully loading the Lenovo notebook from work, though, I've yet to run into many instances where I wished the unit were more powerful. A 4th Generation i7 wtih 32GB of RAM and a terabyte of SSD can make for quite the processing beast, after all. But two decades of being very careful with resources has taught me to consider every setting and test every change … until now.
Last week I was having an issue with some long-running queries in MySQL. Long story short, as part of a project I'm working on at the day job, I need to take roughly 9-million records from two different CMSes and merge them into consistent records in the new system that can then be manipulated by colleagues. These 9 million records are essentially summaries of what was in the bigger CMSes and, when split, would result in close to 23-million records spread across a dozen or so data tables. Nothing crazy at all, given that import processes tend to be very processor and IO intensive. That said, some of the queries that were running were being read from storage, then written back to storage in /tmp, then read back into memory, then written back to /tmp, before finally being output as required. These reads and writes to /tmp struck me as odd, given that the machine does have 32GB of RAM, and rarely did the system use more than 3.8GB for MySQL itself.
I wanted to ditch /tmp.
Luckily, Linux is quite versatile when it comes to situations like this, and I decided the best way to reduce the need to write to storage would be to make a tmpfs directory, and have MySQL use that instead. A tmpfs directory will use the system's RAM first, then write to storage when necessary. And, while I am fortunate enough to have a pair of SSDs in place offering write speeds in excess of 800MB/sec, RAM is faster still. More than this, there is typically 10GB that sits completely unused throughout the day, so why not rectify the problem?
First, I created a new tmpfs directory and applied the requisite permissions:
# sudo mkdir -p /var/mysqltmp
# sudo chmod -R 1755 /var/mysqltmp
# sudo chown -R mysql:mysql /var/mysqltmpNext,I determined the ids for MySQL on the machine:
# id mysqlWhichresulted in:
uid=107(mysql) gid=111(mysql) groups=111(mysql)From here, it was time to edit the fstab file so that the directory would be properly mounted as tmpfs after a reboot. Add these lines to your /etc/fstab file, changing the uid and gid values according to your own MySQL ids:
tmpfs /var/mysqltmp tmpfs rw,uid=107,gid=111,size=8G,nr_inodes=10k,mode=1700 0 0Also keep in mind that this line will make the directory 8GB in size. Not everyone will need or want a directory of this size, so do set it to the most appropriate value for your MySQL installation. My server1 at home, for example, are set to 512M, because the machine has just 4GB of RAM installed.
Now let's mount this directory:
# mount -aIfyou're running Ubuntu 16.04 LTS or thereabouts, you'll need to update the apparmor service next. If you do not do this, MySQL will never be able to access the tmpfs directory you just set up.
Edit the /etc/apparmor.d/usr.sbin.mysqld file and add the following lines:
# Allow temp dir access
/var/mysqltmp r,
/var/mysqltmp/** rwk,Thenrestart apparmor with a sudo service apparmor restart. Now it's time to edit the MySQL configuration file.
In your /etc/mysql/mysql.conf.d/mysql.cnf file2, look for the following line:
tmpdir = /tmpand change it to:
tmpdir = /var/mysqltmpAnd that's all there is to it. Restart mysql with a sudo service mysql restart, and you'll be off to the races. Temporary tables and whatnot will be written into memory and instantly destoryed when not needed, and your IO will drop significantly during those more oppressive queries. Of course, if you specify a tmpfs directory that is too large to fit in memory, Linux is smart enough to start using the storage system in order to complete the tasks. Should this happen, you'll see that the queries are no faster than before.
Again, I understand that most people will not have the luxury of having a beefy Quad-Core i7 with 32GB of RAM available to them. Heck, I don't have anything approaching this myself, and I've worked with databases measuring in the hundreds of gigabytes for years. That said, there's a lot that our systems are capable of if we just know how to set it up right.
I call it a server, but it's really just a re-purposed computer with a bunch of external drives connected to it because I can't quite afford a "real" server at home.
Some people say you should put it in a different file. If you're one of these people, that's cool. Put it wherever you'd like. So long as that config file is read after the file specifying the
tmpdirvariable.