Speedup unit tests by moving MySql data to memory [Ubuntu] ¶
- Move db to memory script
- Move db to disk script
- Restore db backup script
- Check db state script
There are several ways to speedup slow unit tests which interact with database:
- Refactor code and tests and do not touch db in tests
- Use sqlite db in memory instead of MySql
- Use MySql MEMORY engine
- Move MySql data to memory
Each method has drawbacks:
- Not using DB in unit tests means that parts of the code won't be tested.
- Using SQLite only for tests may induce different behavior. This is against the very meaning of unit tests.
- Like SQLite, the MySQL Memory engine is different from InnoDB and MyISAM. For example, FK are ignored.
The following process moves every MySQL database into a memory filesystem. If you have databases other than the test DB, the copy/restore process may be slow, and you can loose data if something goes wrong. You've been warned, do not use this when you have important data in any database of your MySQL server.
It is better to try other listed approaches and I think of last method as of quick temporary hack, but here it is:
- stop mysql
- move /var/lib/mysql to /dev/shm/mysql
- link /var/lib/mysql to /dev/shm/mysql
- start mysql
In Ubuntu there is also a problem with apparmor which will not allow mysql to read from /dev/shm.
To fix this it is recommended to add following to the
[sh] /dev/shm/mysql/ r, /dev/shm/mysql/** rwk,
But it doesn't work for me and I disabled apparmor for mysql (not recommended):
sudo mv /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable
Below are shell scripts to move MySql data to /dev/shm and back, restore backed up data and check db state.
Move db to memory script ¶
[sh] #Check if run as root if [ `whoami` != root ] then echo "You must be root to do that!" exit 1 fi service mysql stop if [ ! -s /var/lib/mysql.backup ] then cp -pRL /var/lib/mysql /var/lib/mysql.backup fi mv /var/lib/mysql /dev/shm/mysql chown -R mysql:mysql /dev/shm/mysql ln -s /dev/shm/mysql /var/lib/mysql chown -h mysql:mysql /var/lib/mysql service mysql start
Move db to disk script ¶
[sh] #Check if run as root if [ `whoami` != root ] then echo "You must be root to do that!" exit 1 fi service mysql stop rm /var/lib/mysql if [ ! -s /dev/shm/mysql ] then cp -pRL /var/lib/mysql.backup /var/lib/mysql else mv /dev/shm/mysql /var/lib/mysql fi service mysql start
Restore db backup script ¶
[sh] #Check if run as root if [ `whoami` != root ] then echo "You must be root to do that!" exit 1 fi service mysql stop if [ ! -s /var/lib/mysql.backup ] then exit -1 fi rm /var/lib/mysql cp -pRL /var/lib/mysql.backup /var/lib/mysql rm -rf /dev/shm/mysql service mysql start
Check db state script ¶
[sh] #Check if run as root if [ `whoami` != root ] then echo "You must be root to do that!" exit 1 fi if [ -L /var/lib/mysql ] then echo "Mem db" exit 0 else echo "File db" exit 1 fi