Personal tools

Log in

Sections

You are here: Home › Blog › Speeding up Django unit test runs with MySQL

Speeding up Django unit test runs with MySQL

by Dan Fairs — last modified Dec 07, 2010 02:22 PM

Here are a couple of tips to speed up unit test runs on Mac OS X and Linux when running MySQL.

When I'm developing Django sites, my database of choice is usually PostgreSQL. However, lots of clients use MySQL. And there lies a problem: table creation on MySQL seems to be an order of magnitude slower on Mac OS X than on Linux. This makes repeated unit test runs extremely painful.

I researched this a little bit a while ago, and noticed that it had been reported as a bug in the MySQL tracker. At the time, there were no fixes or workarounds.

A recent update, however, has revealed the use of the skip-sync-frm option. Put it in your MySQL config file in the [mysqld] section for a quick speedup:

[mysqld]
default-table-type=innodb
transaction-isolation=READ-COMMITTED
default-character-set=utf8
skip-sync-frm=OFF

Of course, nothing in this life is free, as Daniel Fischer explains in a comment:

The reason why it's slower on Mac OS X than on Linux is that on Mac OS X, fcntl(F_FULLFSYNC) is available, and mysqld prefers this call to fsync(). The difference is that fsync() only flushes data to the disk - both on Linux and Mac OS X -, while fcntl(F_FULLFSYNC) also asks the disk to flush its own buffers and blocks until the data is physically written to the disk.

In a nutshell, it's slower because it's safer.

So, we're trading data integrity for performance - but this is a development machine, so trashing and recreating databases (or the MySQL installation for that matter) is fine, if necessary.

Et tu, Linux?

My colleague was having similar problems on the latest Ubuntu, 10.10. The tweak above helped him too, but his test runs were also painfully slow. He'd already added the 'noatime' option to fstab.

It turns out that the newest Ubuntu ships with ext4 as the default file system. By default, ext4 makes absolutely sure that all data has been written out to the filesystem journal before writing the journal commit record. This is done through the use of filesystem barriers. Again - this is done to prefer data integrity over performance. Since this is a dev machine, it's disposable, and performance is more important. So, we can turn this off in /etc/fstab:

/dev/sda3 on / type ext4 (noatime,rw,errors=remount-ro,barrier=0)

Read more about the barrier setting on Kernelnewbies.org.

Just to reiterate - it's probably best not to do this on a machine that's important without thinking about it carefully. Those settings have conservative defaults for a reason!

Filed under: python, linux, django, mysql, tips, macintosh

Michael Halls-Moore says:

Dec 07, 2010 04:06 PM

Interesting article - this has probably saved me from some potential headache in the future!

How do you think this would compare if the MySQL tables were stored on an ext3 file system? Is ext4 "extra careful" when compared to ext3?

Dan Fairs says:

Dec 08, 2010 08:01 AM

Another colleague using ext3 didn't experience the slowdowns that the ext4 guy did - that's one of the things that helped us track it down to the filesystem.

Peter Bengtsson says:

Dec 07, 2010 04:39 PM

Why can't you use a sqlite in-memory database when running unit tests?

Dan Fairs says:

Dec 08, 2010 08:03 AM

Often you can, but not if you're testing features that use transactions or MySQL-specific features. Some bulk operations we've got use LOAD DATA INFILE, for example.

Dan Ostrowski says:

Mar 15, 2011 08:31 PM

First of all, sqlite has transactions. (http://www.sqlite.org/lang_transaction.html) Secondly, there's pretty much no circumstance under which you should be ACTUALLY performing a LOAD DATA INFILE in a unit test. Perhaps there are other MySQL specific hacks you might be using in your code, but none of your response is a valid reason not to use SQLite.

Dan Fairs says:

Mar 24, 2011 05:33 PM

I didn't realise SQLite supported transactions, actually - thanks. Perhaps I was thinking of enforced FK constraints.

With regard to the LOAD DATA INFILE - I agree it's a pretty unusual thing to do. However, it's not actually happening in the unit test itself, but in the code under test. Not doing the LOAD DATA INFILE would rather be missing the point of the test.

Michaux Kelley says:

Dec 07, 2010 05:12 PM

Great post! I've also seen considerable unit test performance speed up on both Linux and Mac OS X speed using a ramdisk.

Wayne Moore over at kotega.com wrote another excellent post "Running MySQL in a ramdisk on OS X" at:

http://kotega.com/blog/2010/apr/12/mysql-ramdisk-osx/

I took that post and transformed what I learned from it into a python script:

https://github.com/mkelley33/python-dev-utils

./mysql-ramdisk -cm -s 256

to create a 256 MB ramdisk with MySQL up and running on it.

Nice work. It's good to read such a concise yet informative explanation of why MySQL is slower on a Mac--basically, giving thumbs up to both: safer by default. Off to change my.cnf now :)

simon says:

Dec 07, 2010 05:14 PM

have you tried using sqlite as the db engine when you run tests?

Daniel says:

Dec 08, 2010 12:23 AM

You could use MySQL's MEMORY engine for tests to take disk i/o out of the picture entirely.

Daniel says:

Dec 08, 2010 12:28 AM

I should have added to my comment.. " (for model schemas that are compatible) ". Since MEMORY does not support all the columns from MYISAM/INNODB. But for many it would work well.

Marc Remolt says:

Dec 10, 2010 04:56 PM

If I may add two more things to your post. The following article about ext4 tuning is based on a rails testsuite, but should affect django as well - IO is IO:

http://blog.smartlogicsolutions.com/[…]/

And I usually add

innodb_flush_log_at_trx_commit = 0

to my my.cnf, which causes transactions to be flushed to disk later (1 sec), not at once.

We run a rails testsuite with 8 parallel cpu threads (meaning 8 tests parallel at a time on 8 different databases) with this settings and we have 0-2% IO wait.

Before the tuning IO was our main bottleneck.

Dan Fairs says:

Dec 15, 2010 01:34 PM

Top tips Marc - I'll pass those on to the ext4 guy! Thanks.

Piotr Czachur says:

Dec 21, 2010 09:32 AM

You can have both transactional engine enabled and in-memory database: use MySQL/InnoDB with datadir set to ramdisk - speedup is quite noticeable.

Here is my post on this subject: http://stackoverflow.com/[…]/4437821#4437821
And remember it's only for testing purposes as data vanishes when power goes down...

Wes Winham says:

Aug 12, 2011 06:37 PM

@Piotr thanks for the link. I had to fuss with apparmor, but your post got me most of the way there. Here's my writeup of the process: http://devblog.policystat.c[…]l-from-a-ram-disk-in-ubuntu

ssspiochld says:

Jan 03, 2011 11:46 PM

In my setup with no extra MySQL tuning on OS X this (sync-frm=0) gave ~4x speedup!
That might be also useful tip or other frameworks/testing under MySQL outside Django or Python ecosystem.

Thanks a lot for sharing

Add comment

You can add a comment by filling out the form below. Plain text formatting.

Name

Comment ■