scary-pg-mongoid-sequel-ar-benchmark.rb

# following is a little benchmark looking a real world example of inserting a
# bunch of records into pg and mongo using a few methods available in ruby.
# this is not meant to be an apples to apples benchmark, rather it's trying to
# see how the various dbs might perform in a real world situation.  i know
# mongo does not fsync to disk.  i know it's not fair using a transaction with
# the rdbms, but these conditions will exist in real world usage and it's that
# which i'm interested in.
#
# here is a few runs
#
# n = 10_000  (with transactions)
#
#   user     system      total        real
#   pg  0.840000   0.330000   1.170000 (  3.265902)
#   sequel  6.350000   0.540000   6.890000 ( 10.261432)
#   ar model 11.860000   0.380000  12.240000 ( 15.038289)
#   mongid model  9.400000   0.520000   9.920000 ( 35.926766)
#   mongo  5.920000   0.410000   6.330000 (  7.749044)
#
#
# n = 10_000  (*without* transactions - code was modified)
#
#         user     system      total        real
#   pg  0.900000   0.440000   1.350000 (  7.976228)
#   sequel  6.150000   0.560000   6.710000 ( 13.553662)
#   ar model 11.500000   0.790000  12.290000 ( 18.490117)
#   mongid model  9.330000   0.530000   9.860000 ( 35.956647)
#   mongo  5.710000   0.410000   6.120000 (  7.577185)
#
#
# n = 100_000  (with transactions)
#
#         user     system      total        real
#   pg  8.450000   2.780000  11.230000 ( 37.551105)
#   sequel 69.880000   5.170000  75.050000 (103.346337)
#   ar model233.250000   3.860000 237.110000 (265.600197)
#   mongid model145.180000   5.910000 151.090000 (2443.426801)
#   mongo 51.490000   3.920000  55.410000 ( 70.370120)
#
#### on bbrincks machine (3 riak nodes in the cluster)
# n = 10_000
                                    user     system      total        real
# pg                              0.740000   0.310000   1.050000 (  3.318124)
# sequel                          5.950000   0.380000   6.330000 (  8.724072)
# ar model                       10.520000   0.370000  10.890000 ( 13.480038)
# mongid model                    8.870000   0.500000   9.370000 ( 38.057865)
# mongo                           4.350000   0.380000   4.730000 (  5.723543)
# riak-client (default values)   10.470000   0.590000  11.060000 ( 21.008600)
# riak-client r-1, w-1, dw-0     18.180000   0.890000  19.070000 ( 34.781044)

# (I'm surprised the last one is slower. I expected it to be faster)

###
#
# summary of results:
#
# 1) raw pg is almost as fast(er) as raw mongo and a *lot* more durable.
# 2) ar has gotten much, much better.
# 3) riak is quite slow, at least with my development setup
#

## setup
#
  require 'rubygems'
  require 'ostruct'
  require 'benchmark'
  require 'uuidtools'
  require 'active_record'
  require 'mongoid'
  require 'riak'

## cli options
#
  STDOUT.sync = true
  $n = (ARGV.shift || 1000).to_i

## config
#
  C =
    OpenStruct.new(
      'db' => (ENV['USER'] || 'bench'),
      'table' => 'bench'
    )

## setup a sequel connection
#
  require 'sequel'
  S = Sequel.connect("postgres://localhost/#{ C.db }")
  require 'sequel_pg'

## setup an ar class
#
  ActiveRecord::Base.establish_connection(:adapter => 'postgresql', :database => C.db)

  class A < ActiveRecord::Base
    set_table_name C.table
  end

## setup mongoid class
#
  Mongoid.configure do |config|
    config.master = Mongo::Connection.new.db(C.db)
  end

  class M
    include Mongoid::Document
    self.collection_name = C.table
    field(:value)
    index(:value, :unique => true)
  end
  A.delete_all if S.table_exists?(C.table)

## make sure the rdbms table is setup and indexed
#
  S.drop_table(C.table) if S.table_exists?(C.table)
  S.create_table(C.table) do
    primary_key(:id)
    String(:value, :unique => true, :null => false)
  end

## make sure the mongo collection is setup and indexed
#
  M.delete_all
  M.create_indexes

## Make sure Riak bucket is cleared
# 
  #riak_client = Riak::Client.new(:protocol => "http", :http_port => 8091, :pb_port => 8081)
  riak_client = Riak::Client.new(:protocol => "pbc", :http_port => 8091, :pb_port => 8081)
  bucket = riak_client.bucket('bench4')
  bucket.keys do |keys|
    keys.each do |key|
      bucket.delete key
    end
  end

## bench
#
  def values() Array.new($n).map{ UUIDTools::UUID.timestamp_create.to_s } end
    
  Benchmark.bm(30) do |bm|
    sequel = S[:bench]
    mongo = M.collection.db.collection(C.table)
    pg = A.connection.raw_connection


    bm.report 'pg' do
      pg.exec('begin transaction')
      values.each do |value|
        res = pg.exec('insert into bench values(default, $1) returning id', [value])
        id = res.getvalue(0,0)
        res = pg.exec('select * from bench where id=$1', [id])
      end
      pg.exec('commit')
    end

    bm.report 'sequel' do
      S.transaction do
        values.each do |value|
          id = sequel.insert(:value => value)
          sequel.where(:id => id).first
        end
      end
    end

    bm.report 'ar model' do
      A.transaction do
        values.each do |value|
          r = A.create!(:value => value)
          A.where(:id => r.id).first
        end
      end
    end

    bm.report 'mongid model' do
      values.each do |value|
        r = M.safely.create!(:value => value)
        M.where(:id => r.id).first
      end
    end

    bm.report 'mongo' do
      values.each do |value|
        id = mongo.insert({:value => value}, {:safe => true})
        mongo.find(:_id => id).first
      end
    end

    bm.report 'riak-client (default values)' do
      values.each do |value|
        entry = bucket.new
        entry.data = {:value => value}
        entry.store(:returnbody => false)
  
        key = entry.key
        stored = bucket.get(key)
      end
    end

    bm.report 'riak-client r-1, w-1, dw-0' do
      values.each do |value|
        entry = bucket.new
        entry.data = {:value => value}
        entry.store(:r => 1, :w => 1, :dw => 0)
        entry.store(:returnbody => false)
  
        key = entry.key
        stored = bucket.get(key, :r => 1)
      end
    end

  end