Moving single MySQL database to tmpfs
Recently I had a problem running Drupal Simpletest on my new i7 dev box. It took couple minutes to execute a set of relatively simple tests and as a developer you probably know how annoying that could be. The problem was largely due to InnoDB storage engine and it slowness caused by creating / removing tables (compared to let say MyISAM).
The solution I came up with was to move that single Drupal databse to in-memory (tmpfs) partition. Luckily I had couple of free gigs of ram to spare.
I was able to reduce Drupal Simpletest exectuion time from aprox 2 minutes to 9 seconds! That’s impressive! Hope those instructions will work for you as well!
Here is how I did it:
First we need to create a tmpfs partition in your fstab ( /etc/fstab on my box) :
tmpfs /var/lib/mysql/tmpfs tmpfs rw,nosuid,nodev,uid=115,gid=123 0 0
Note: 115 and 123 are uid and gid of mysql user. To get those on your Ubuntu box you can run
id -u/-g mysql
After partition was created:
sudo mkdir /var/lib/mysql/tmpfs - create mount point
sudo chown mysql:mysql /var/lib/mysql/tmpfs - change the owner of the folder to mysql since we create it inside mysql dir
sudo mount -a - mount partition
Then we move our Drupal database from it’s old location ( /var/lib/mysql/drupal_db on my box ) to
the newly created partition (/var/lib/mysql/tmpfs in our sample):
Important: Don’t forget to stop mysql server first!
mv drupal_db tmpfs
Note: You can skip following section and go directly to Make changes permanent below
Since we are moving just one database instead of whole mysql data folder, we create a symlink to our drupal_database so mysql could still find it at its old location:
ln -s tmpfs/drupal_db drupal_db
After that you should be able to start mysql server and have drupal_db running in memory.
If you see your database in the list of databases when you start mysql then you are almost there, the final and very important step is to make changes “permanent”, so your database could survive mysql server start / stop events.
Important: If you will reboot your server / workstation without copying files from tmpfs back to hardive, you will lose the database!
Make changes permanent or survive server reboot
There are multiple ways to do this, you can either write a job which will start before and after mysql, or you can just modify mysql init script. For the sake of simplicity we will use the last mentioned method.
Usually you can find your mysql init script in /etc/init.d/mysql. Add following lines in bold to the start) section after the first else statement:
log_daemon_msg "Starting MySQL database server" "mysqld" if mysqld_status check_alive nowarn; then log_progress_msg "already running" log_end_msg 0 else # Copy drupal db into tmpfs mv /var/lib/mysql/drupal_db /var/lib/mysql/tmpfs ln -s /var/lib/mysql/tmpfs/drupal_db /var/lib/mysql/drupal_db /usr/bin/mysqld_safe > /dev/null 2>&1 &
log_failure_msg "Please stop MySQL manually and read /usr/share/doc/percona-server-server-5.1/README.Debian.gz!" exit -1 else # Copy our Drupal db back to hardrive rm /var/lib/mysql/drupal_db mv /var/lib/mysql/tmpfs/drupal_db /var/lib/mysql log_end_msg 0 fi
That’s about it! You can test if those commands by starting and stopping your mysql instance and checking your databases list.
Some popular ones
- Story behind X-Forwarded-For and X-Real-IP headers (23 Apr 2014)
- Internal redirect to another domain with proxy_pass and Nginx (14 Oct 2013)
- Secure data bag items with chef solo (04 Aug 2013)
My books recommendations
Great book for operations people. Helped me to design and build solid deployment pipelines. Awesome advices on automated testing as well. The author advocates against feature branches, every commit goes to master! Scary? I know, but it actually makes sense once you get the idea. Read the book to find out more.
One of those rare books where every word counts!
Classics from John Allspaw who is SVP of Infrastructure and Operations at Etsy (and used to work for Flickr). The book covers very important topics like metrics collection, continuous deployment, monitoring, dealing with unexpected traffic spikes, dev and ops collaboration and much more. Def recommend if you are starting out in the operations field or been doing it for a while ( in latter case you probably read this book already :).
This book is must read for every software engineer, no matter which language you use! It will change your perspective on writing code. I was amazed by the quality of material - very detailed and up to the point.
"The only way to make the deadline -- the only way to go fast -- is to keep the code as clean as possible at all times."
blog comments powered by Disqus