Increase speed for MySQL table creation in Django?

Question

Some of my unit tests take 10-15 seconds just for mysql to create the tables. This seems unnecessarily long. It has to create around 50 tables, but that's still only 3 tables per second. This is a big annoyance when running unit tests over-and-over.

As a workaround, I have been running my unit tests in sqlite3. It is blazing fast, but I would prefer to run my tests on MySQL since that's what my live servers run.

To illustrate the speed difference, create a fresh project. Then run syncdb on it using mysql. Then try it using sqlite3.

[~/testproject] ./manage.py syncdb
Creating table auth_permission
Creating table auth_group
Creating table auth_user
Creating table auth_message
Creating table django_content_type
Creating table django_session
Creating table django_site

For me, it takes about 2 seconds to create the above tables in MySQL. Sqlite3 is almost instant.

I am running mysql on my development machine. Here is my my.cnf.

Please suggest any tips or tweaks you can think of that might help speed up MySQL's table creation time.

Answer 1

You can create RAM-disk and move db there, just for unit testing. If you write script for this then it's automatic and very convenient.

Also, for other purposes I've written custom test runner that loads DB from sql dump instead of creating it and then creating tables.

You choose.

Answer 2

I've been experiencing slow INNODB table creation speeds (about 25 seconds to create 13 small tables).

I experimented with options in the [mysqld] section of the my.cnf file.

Adding:

innodb_flush_method=fdatasync

produced the best results (about 1.5 seconds to create the same 13 small tables).

Answer 3

I have found that using sqlite as a replacement makes my unit tests much faster. I'm also removing southdb, as this slows down table creation too.

if len(sys.argv) > 1 and sys.argv[1] == 'test':
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': ':memory',
            'USER': '',
            'PASSWORD': '',
            'HOST': '',
            'PORT': '',
        }
    }
    INSTALLED_APPS = tuple([x for x in INSTALLED_APPS if x != 'south'])