Popular stories:

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!

cd /var/lib/mysql

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:

One thought on “Moving single MySQL database to tmpfs”

when you doing these steps manually (via `sudo` I think), you have to fix owner of created folder and links. Just check parent folder and deeper to be owned by «mysql:mysql»:
`ls -la /var/lib/mysql`
To change owner of a symlink use «-h» key:
`chown -h mysql:mysql path/to/link`

I helped to build and maintain the infrastructure for Game of Thrones, the biggest and most popular show in the world.

Do you want to know the single most important thing that I learned over the years?

NONE OF IT REALLY MATTERED…

Yes, it was fun for a while.

Yes, like most of us engineers I was making good money.

But at the end of the day, I would still have to show up at work and sell my time.

Sometimes I would come in, sit in my cubicle and dream about things I could do instead of staring at the screen all day long…

I could go to the beach with my wife and my son.

I could fly to El Classico game in Barcelona with my brother and watch Messi scoring amazing goals.

I could organize a surfing trip to South Africa and other awesome places around the world. Places I’ve never seen.

I could work on my own projects that would make the impact in the world or at the very least, make me some money.

Hell, I could just sit home and do absolutely nothing!

And yet there I was still in my cubicle 12 years later with big hopes and dreams and pretty much nothing to show for…

Sounds familiar?

The tipping point for me was when I started buying games on Steam and GoG and playing them in my mind.

Nothing to install, no need to upgrade video cards, no need to feel bad in front of my wife, no time to waste…

You are right, I was spiraling down and needed a break, but more so I felt like I needed some radical changes in my life.

I’m sure you heard this saying before: “Insanity: doing the same thing over and over again and expecting different results”

It became clear that the road I was walking on would lead me to mediocre life.

The problem was that I didn’t want to be mediocre. I wanted my life to be awesome, full of fun, happiness and excitement!

I wanted to make a difference in the world, leave a legacy, make my kids proud, live without regrets, discover my true purpose.

So about a year ago, I set out on my new journey…

I left my old comfortable job, attended multiple high profile non-technical events (including Tony Robbins UPW), joined an expensive business program, hired a personal coach and mentor, met a bunch of people who were able to disconnect from the Matrix and never looked back.

And let me tell you – there is another world out there, something we technical guys don’t get to experience!

There is hope.

Now, here is my question for you:

Do you want to continue to be just a tool in someone else’s hands or you want to upgrade yourself and become a Rain Maker?

If you want to find out who you really are, take full control of your life, step outside your comfort zone in order to grow physically, mentally and financially and help others along the way, then the Red pill is for you. Just drop your email in the field below and we’ll be in touch.

Take a Blue pill and you will forget that we ever met. You will close this popup and continue reading articles about Nginx, Kubernetes, Docker, secretly dreaming of life that you could have… (or pathetically thinking that you will have it one day just by perfecting technical skills)