Technology and Software

Ruby performances with PostgreSQL and MySQL

(original post in the Italian Ruby Forum)

I had to convert a database seeding script from PostgreSQL 9.4 to MariaDB 10 (customer’s choice and with little enthusiasm I had to comply). This lead to a number of interesting discoveries about the pg and mysql2 Ruby drivers. Apart a few minor issues [1] [2] [3] [4] I immediately noticed that the script with MariaDB run 20 times (twenty) more slowly than the  PostgreSQL one: 21 minutes vs 1 minute and 3 seconds. Unusable and inexplicable.

Such a big difference can not be due to the database, so I started to investigate the configuration. Even the MySQL coming with Ubuntu 12.04 was too slow and I can expect that the distributors set it up reasonably well. At this point the suspect becomes the driver. I opened this issue and they gave me two valuable tips: use a profiler ( ) and the gem-import activerecord ( ). I knew both of them but sometimes you have to be reminded about tools you don’t use often. Ops.

The profiler show that the driver uses pg prepared statements that give obvious benefits with the number of records created by my script (a little over 32,000). The version of mysql2 I had to use (0.3.x) does not have prepared statement (but the newer version does) and that seems to make the difference. I rewrote the script to use activerecord-import, which  inserts a whole array of objects at once. The script looks a bit unnatural, because I repeatedly needed the ids of the record I created to pass them along the associations, but the execution times for mysql2 dropped from 21 minutes to 1 minute and 33″. It was worth it. There are only 1,045 calls to the db and yet is always slower than 32k calls made by the original script with pg. The script with pg and activerecord-import dropped to 47 seconds.

Despite all the enhancements introduced in the import-activerecord calls my script’s calls to PostgreSQL add up to 9.4 seconds. The calls to MariaDB are  49.8 seconds. Ruby accounts for 40 seconds, regardless of the database used.


1) Work on PostgreSQL has performance advantages with Ruby due to drivers.

2) mysql2 0.4.0+ has prepared statements but if you’re working with Rails you must be careful. There are issues [A] [B] and it seems you need Rails 4.2.5+ to use it. I didn’t test the combination yet.

3) For details of my profiling research (tables, times, calls) read 


Finally, the issues I run into:

[1] https: // …

[2] For MariaDB install the gem mysql2 with

bundle config \
build.mysql2 --with-mysql-config=/path/to/mariadb/bin/mysql_config

Careful: this is globals so use –with-mysql-config=/usr/bin/mysql_config when you need to connect to MySQL.

[3] My script would clear the db before seeding using TRUNCATE CASCADE, but MySQL and MariaDB don’t have it. This  is the workaround

 connection = ActiveRecord :: Base.connection
 Connection.Execute ("SET foreign_key_checks = 0;")
 [all models] .each do | model |
   Connection.Execute ("TRUNCATE model.table_name # {}")
 Connection.Execute ("SET foreign_key_checks = 1;")

[4] But neither ActiveRecord has TRUNCATE, so either you use some gems that add it to AR or even for PostgreSQL you need a loop like that, but you don’t need the SET foreign_ley_checks statements.

Technology and Software

Running Ruby on Rails tests with a ramdisk backed PostgreSQL

I’m not a fan of mocking objects in my Ruby On Rails tests so my tests always hit the database, which is PostgreSQL anytime I can make the choice and a customer doesn’t dictate MySQL.

Hitting the DB means that the test suite eventually slows down as tests pile up as the application grows.
It’s been a long time since I wanted to check what happens if I run my tests on a database backed on RAM instead of by a spinning disk. Are they going to be much faster?

Tl;dr No, they run at the same speed.

Creating the DB on the ramdisk

The setup is based on information provided at and

The test system is my laptop, an Ubuntu 12.04, i7-4700MQ CPU @ 2.40GHz, 16 GB RAM, SDD for the OS, HDD for my home and databases. The DB is PostgreSQL 9.3.

Linux has 16 ramdisks already created as /dev/ram* at boot time. Let’s take one and mount it.

mkdir ~/tmp/ram
sudo mkfs.ext4 -m 0 /dev/ram0
sudo mount /dev/ram0 ~/tmp/ram/
df -h ~/tmp/ram/
Filesystem Size Used Avail Use% Mounted on
/dev/ram0 58M 1.3M 56M 3% /home/me/tmp/ram

It’s a tiny disk and it turned out to be barely enough to accommodate my tests but it’s OK for experimenting.
You can make it larger if you need to. explains how.

We create a DB there now.

cd ~/tmp/ram
sudo bash
mkdir postgresql
chown postgres.postgres postgresql/
su - postgres
/usr/lib/postgresql/9.3/bin/initdb --locale=en_US.UTF-8 -D ~/tmp/ram/postgresql/
mkdir postgresql/log
chown postgres.postgres postgresql/log/

We make it use a different port from the one used by the default PostgreSQL DB on the laptop.

vi postgresql/postgresql.conf

port = 5433

We start the DB and connect to it

sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl \
  -D ~/tmp/ram/postgresql/
  -l ~/tmp/ram/postgresql/log/postgresql-9.3-main.log start
psql -p 5433 -U postgres


Running tests

We edit config/database.yml to use the ramdisk DB

port: 5433

We create the test user and the test db

psql -p 5433 -U postgres
create role testuser login password 'password';
alter user testuser with createdb;
create database myapp_test owner testuser encoding='UTF8' lc_collate='en_US.UTF-8' lc_ctype='en_US.UTF-8';

We create the DB schema

cd the/rails/directory
RAILS_ENV=test rake db:migrate

And finally we benchmark the tests over the two databases.

rake spec:controllers


Finished in 1 minute 5.34 seconds (files took 1.76 seconds to load)
Finished in 1 minute 4.26 seconds (files took 1.75 seconds to load)
Finished in 1 minute 2.07 seconds (files took 1.75 seconds to load)


Finished in 1 minute 7.09 seconds (files took 1.76 seconds to load)
Finished in 1 minute 6.01 seconds (files took 1.72 seconds to load)
Finished in 1 minute 4.68 seconds (files took 1.74 seconds to load)

2 seconds are not worth the trouble. Let’s benchmark the models.

rake spec:models


Finished in 1 minute 36.8 seconds (files took 1.69 seconds to load)
Finished in 1 minute 38.08 seconds (files took 1.72 seconds to load)
Finished in 1 minute 37.9 seconds (files took 1.73 seconds to load)


Finished in 1 minute 38.64 seconds (files took 1.79 seconds to load)
Finished in 1 minute 32.73 seconds (files took 1.69 seconds to load)
Finished in 1 minute 41.89 seconds (files took 1.71 seconds to load)

No difference at all, only a bit more variance in the durations of the HDD tests.

This is my conjecture. The data go first into the OS file buffer, then are synced to the disks. Syncing to ramdisk is faster but if there is enough RAM data is staying in RAM anyway and it doesn’t matter if we’re using a ramdisk or a HDD. Remember: this is a test DB with a handful of data, not a large production DB with high I/O loads.

Let’s stop the DB and change the configuration to do without syncing. If there is no speedup my conjecture should be confirmed.

sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl \
  -D ~/tmp/ram/postgresql/ \
  -l ~/tmp/ram/postgresql/log/postgresql-9.3-main.log stop
sudo vi ~/tmp/ram/postgresql/postgresql.conf
sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl \
  -D ~/tmp/ram/postgresql/ \
  -l ~/tmp/ram/postgresql/log/postgresql-9.3-main.log start

Run the test on the ramdisk again.

rake spec:models


Finished in 1 minute 36.52 seconds (files took 1.71 seconds to load)
Finished in 1 minute 35.45 seconds (files took 1.7 seconds to load)
Finished in 1 minute 36.59 seconds (files took 1.68 seconds to load)

No difference, so my tests didn’t move enough data to make the syncing operations relevant.


You can keep your test DB on a spinning disk and the OS buffering will make it fast.
If you want quick tests you probably have to mock everything and do without the DB.

Alternative: run tests in parallel with the parallel_tests gem.


Could I have done something better to make the ramdisk based DB run faster?