MarkUs Blog

MarkUs Developers Blog About Their Project

Archive for the ‘in-memory db’ tag

In-memory database speeds up tests, but not in a such great scale.

without comments

It has been observed that test suites with Machinist are slower than those with transactional fixtures . This is because the bottleneck of test performance lies in database operations. Machinist normally requires much more database queries than transactional fixtures do. To solve the performance issue related to Machinist, we should either speed up database operations or reduce the number of database queries.

There is a type of database known as in-memory database. As its name suggests, it stores data in memory instead of file systems. Memory access is a lot faster than file access, and therefore, an in-memory database should have performance advantage over an normal file-based database. By using in-memory database for testing, database operations can be faster and thus test speed will go up.

We tried in-memory SQLite3 following the instructions on I expected in-memory SQLite3 would have an enormous speed boost, but  an experiment comparing PostgreSQL v.s. in-memory SQLite3 shows that it is no more than 20% faster.

The experiment runs several ruby files with test database set to PostgreSQL and in-memory SQLite respectively. Here is the code in config/database.yml that sets the test database to in-memory SQLite3 and PostgreSQL:

# setup test db to in-memory SQLite3

adapter: sqlite3
database: ":memory:"
verbosity: quiet

#setup test db to PostgreSQL

adapter: postgresql
encoding: unicode
database: markus_test
username: markus
password: markus

I pick test/assignment_test.rb as the first benchmark, because this test suite uses Machinist and has heavy database operations.  On my laptop, it takes around 15.6s to run test/assignment_test.rb when in-memory SQLite3 is used, and it takes around 18.5 s when PostgreSQL is used. In-memory SQLite3 is only 18% faster for a typical Machinist test.

The other benchmark I use is a temporary source file, performance_test.rb in the root directory of the project:

# markus_root/performance_test.rb

ENV["RAILS_ENV"] = "test"
require File.expand_path(File.dirname(__FILE__) + "/config/environment")
require File.join(File.dirname(__FILE__), "test/blueprints/helper")

(ActiveRecord::Base.send :subclasses).each {|c| c.delete_all}

t1 =

5000.times { Section.make }

t2 =

puts "time span: #{t2-t1}s"

It gives 53.6s for PostgrSQL and 45.2 for in-memory SQLite3. In this case, in-memory SQLite3 is 19% faster.

19%, not as good as expected, is it? Yet, this is understandable. Using in-memory database is not equal to keeping objects within the same memory space used by the ruby application. The former requires interprocess (or sometimes internet) communications between a ruby application and a database, which itself is slow. And I suspect a big fraction of db operation is the communication between an application and a db, and thus we do not see a significant speed-up when using in-memory SQLite3. For this reason, I think that reducing the number of db queries is a better way to improve test speed. This can be testified if we observe the running time of an earlier version of assignment_test where transactional fixtures were used. This test suite runs in 11.6s even if PostgreSQL is the db, compared to 15.6s using in-memory SQLite3 for Machinist tests. So my conclusion is that in-memory SQLite is faster than file-based databases, but this should not be the reason to abandon transactional tests, because transactional tests are much faster than non-transactional ones.

Written by bryanshen

April 19th, 2010 at 2:32 am

Posted in Uncategorized