Mysql too slow in tests? ramdisk it!

Suppose you're doing it wrong: you're using MySQL and your test suite is slow very slow. MySQL is very slow at DDL statements so creating/clearing/loading that test database all the time is going to be very slow.

People have worked out some solutions like:

Using TRUNCATE instead of DROP/CREATE and/or other tricks [1].
Fine tunning mysql ... just for development?!? [2], [3].
Use TransactionTestCase
Use sqlite in :memory: - very bad idea, lots of subtle bugs won't be catched in the test suite.

And finally there's one solution that doesn't have the code trade-offs but it's a pain to get it set up: MySQL running on ramdisk (known as tmpfs). I've seen some solutions [4], [5], [6], none worked on Ubuntu (12.04) very well for me thus I've cooked up my own version.

It's largely based on [6] but with cleanup code in case you already ran it and added the missing database setup part (you might want to edit the passwords and database names).

#!/bin/bash -xEe
i=$1

if [ -z "$i" ]; then
  echo "Missing argument: instance number."
  exit 1
fi

port=$[3306+$i]
pid=`cat "/var/run/mysqld/mysqld$i.pid" || true`
if [ -n "$pid" ]; then
    kill -9 $pid
    while kill -0 $pid; do
        sleep 0.5
    done
fi
umount -l "/var/lib/mysql$i" || true
rm -rf "/var/lib/mysql$i"
mkdir "/var/lib/mysql$i"
mount -t tmpfs -o size=400M tmpfs "/var/lib/mysql$i"
chown -R mysql.mysql "/var/lib/mysql$i"
mkdir -p "/var/log/mysql$i"
chown -R mysql.mysql "/var/log/mysql$i"
rm -rf "/etc/mysql$i"
cp -R /etc/mysql/ "/etc/mysql$i"

cd "/etc/mysql$i/"
sed -i "s/3306/$port/g" my.cnf
sed -i "s/mysqld.sock/mysqld$i.sock/g" my.cnf
sed -i "s/mysqld.pid/mysqld$i.pid/g" my.cnf
sed -i "s/var\/lib\/mysql/var\/lib\/mysql$i/g" my.cnf
sed -i "s/var\/log\/mysql/var\/log\/mysql$i/g" my.cnf

if [ ! -f /etc/apparmor.d/disable/usr.sbin.mysqld ]; then
    touch /etc/apparmor.d/disable/usr.sbin.mysqld
    service apparmor reload
fi

mysql_install_db --user=mysql --datadir="/var/lib/mysql$i"
pass=`perl -e 'print map{("a".."z","A".."Z",0..9)[int(rand(62))]}(1..16)'`;
mysqld_safe --defaults-file=/etc/mysql$i/my.cnf&
sleep 3
mysqladmin -S /var/run/mysqld/mysqld$i.sock password $pass
cat <<EOF | mysql -uroot -h127.0.0.1 -P$port -p$pass
    USE mysql;
    CREATE DATABASE IF NOT EXISTS app CHARACTER SET utf8 COLLATE utf8_bin;
    CREATE DATABASE IF NOT EXISTS app_tests CHARACTER SET utf8 COLLATE utf8_bin;
    GRANT ALL ON app.* TO 'app'@'localhost' IDENTIFIED BY 'app';
    GRANT ALL ON app_tests.* TO 'app'@'localhost' IDENTIFIED BY 'app';
    GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '$pass';
    FLUSH PRIVILEGES;
EOF

It will run on port 3306 + instance-number. Also, you need to run it with sudo.

[1]	https://github.com/jbalogh/test-utils

[2]	http://www.stereoplex.com/blog/speeding-up-django-unit-test-runs-with-mysql

[3]	http://www.mysqlperformanceblog.com/2010/02/28/maximal-write-througput-in-mysql/

[4]	https://github.com/mathieuk/mysql-ramdisk-helper

[5]	http://stackoverflow.com/questions/13814247/bash-script-to-start-new-mysql-server-instance-into-memory

[6]	(1, 2) http://www.ducea.com/2009/01/19/running-multiple-instances-of-mysql-on-the-same-machine/