Improving MySQL Insert Performance

Warning: Do NOT do this on a production server!

So, my laptop has been crawling when I try to import a database (about 6MB .sql file). It takes about 20 minutes to run the script. After doing some reading, I found a way to improve the speed.

The whole thing has to do with the fact that my / partition is running ext4 file system. And ext4 has some default settings that are good for reliability, but not so good for speed.

Before I started experimenting with ext4 settings, I wanted to move /var to a new partition so my experiments do not affect the entire OS.

Steps to move /var to a new partition in Ubuntu 12.04

Luckily for me, I had some unpartitioned free space lying around in my hard disk. I created a new partition using that free space, and formatted it as ext4. In your case, you might have to resize an existing partition. Do this carefully, or you might lose your data.

In my case the new partition was in sda7. So, first I boot into single user mode.

# mkdir /mnt/newvar
# mount /dev/sda7 /mnt/newvar
# mv /var/* /mnt/newvar/
# vi /etc/fstab

Add a line at the end

/dev/sda7    /var    ext4    defaults    0   2

I rebooted the machine, and everything came up fine. Still MySQL was taking 20 minutes to import my database. So, next I got around to experimenting with ext4 settings.

Steps to improve ext4 speed (and reduce reliability)

There are three main parameters at play here:

The first parameter is access time. There are three possible modes: atime, relatime and noatime. When you choose atime, everytime a file is accessed, the last accessed timestamp is written to the inode. This means, for every read, there will be a write, and therefore might be reason for slow performance. The noatime option will cause the filesystem not to update the last access timestamp. Be warned that this might break some email applications that rely to last access timestamp to see if an email was read or unread. The default option is called “relatime” and is similar to noatime, but updates the access time only when the previous access time was earlier than the current modify or change time. This will improve performance as well as not break any email applications. But I wanted to extract max performance, and since it is my development laptop, and not an email server, I went ahead and chose “noatime”.

The second parameter is data. The valid options are journal, ordered and writeback. The default option is ordered, and all data is written to the main filesystem before the metadata is written to the journal. This is the safest thing to do, and in case of crash will recover fine. However, to push the performance, I chose “writeback”. In this mode, the data ordering is not preserved. This is somewhat risky if there is a crash or abrupt poweroff. But this is my laptop and I am feeling lucky. So!

The third parameter is barrier. By default ext4 enables write barrier. This forces proper on-disk ordering of journal commits. Again, it’s a question of reliability vs performance. So, I chose to disable barrier.

I edited my /etc/fstab and it now looks like this:

/dev/sda7 /var ext4 noatime,data=writeback,barrier=0    0   2

Also I replaced “/dev/sda7” in my /etc/fstab with the UUID of the device, that I got from running the command blkid.

Doing these steps gave me a huge performance improvment. I am able to import my MySQL database in seconds! It’s been a few weeks now and so far I have not had any problems.

Be sure to read the man page of mount, the documentation of ext4 and Nick’s blog about improving ext4 filesystem performance.