Sunday, November 29, 2009

Inmemory mysql database with InnoDB engine

In a typical MVC based application, for unit testing of models, should we allow the tests to hit database? Anyway its a different discussion and I am not going to talk about that here. If you allow any test of that kind to access database (you can call that as unit test or what ever which you are comfortable with), your test suite is probably going to take much time since we are now limited by the disk I/O speed of database.

If you are using mysql as your database server, you can refer this comparison chart to choose the engine which best fits your needs. So mysql provides inmemory engines, but the downside of those is that they does not support transactions and foreign keys. InnoDB supports the both mentioned, but it operated data only on disk and its relative disk usage is higher.

So you if you want a mysql engine which provides the features of InnoDB and also operates from RAM so that you can make your tests faster, a trick can be done. A part of RAM can be mounted as disk and that can be used as data location of mysql server. I got this idea from one of my co-worker Gavri Fernandez long back. By this way, we are simply zero downing the disk I/O time and all the read/write operations are going to be super fast.

Obviously this can be done only in nix based operating systems.

These are the steps to use ram disk to speed up your innodb mysql database.

1) Create a ram disk. This differs for each OS and these links provides information about creating the ram disk for Mac os x and Ubuntu/Redhat.

2) Mount the ram disk to your prefered location. The data location of mysql in ubuntu is /var/lib/mysql. This is configured in the mysql server config file. Again the location of config file and the data dir can differs from os to os.Change the data dir location to the mount point you just created. Copy the contents of the original data dir to the mount point.

3) Usually mysqlserver runs with its own user called mysql and hence the file permissions of the data directory. So change the owner and group of the mount point to mysql:mysql.

4) Now start/restart the mysql server to make the new configuration effective.

Note for ubuntu users: I had tough time in ubuntu to make mysql to use the new location because of apparmor, So finally i had to disable apparmor for mysql to make it to run. As the machine was my local dev box its not an issue. If anyone find success with apparmor, i request them to ping me about it.

If you are not able to start the server, syslog should give an insight about what is happening behind.

Thats it. Now all the database opearations are going to be super fast. This is suitable to for running tests which hits database, since we dont care about the data even if we lose.

No comments: