mongo-should-be-bloody-faster.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.
# 
# the code inserst a value with a simple uniq contraint and then looks it up.
# 
# 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 are a few runs
#
#     n=1_000
#       user     system      total        real
#       pg - with transactions  0.020000   0.030000   0.050000                     ( 0.255017    ) 
#       pg - without transactions  0.030000   0.030000   0.060000                  ( 0.594669    ) 
#       sequel - with transactions  0.580000   0.060000   0.640000                 ( 0.890069    ) 
#       sequel - without transactions  0.580000   0.060000   0.640000              ( 1.232356    ) 
#       active_record model - with transactions  1.010000   0.040000   1.050000    ( 1.258300    ) 
#       active_record model - without transactions  1.150000   0.080000   1.230000 ( 1.841997    ) 
#       mongo_mapper model  1.200000   0.070000   1.270000                         ( 1.520423    ) 
#       mongoid model  0.860000   0.060000   0.920000                              ( 1.941832    ) 
#       mongo  0.410000   0.040000   0.450000                                      ( 0.586048    ) 
#
#     n=10_000
#       user     system      total        real
#       pg - with transactions  0.210000   0.280000   0.490000                     ( 2.587327    ) 
#       pg - without transactions  0.280000   0.310000   0.590000                  ( 6.444020    ) 
#       sequel - with transactions  6.060000   0.510000   6.570000                 ( 9.315459    ) 
#       sequel - without transactions  6.030000   0.540000   6.570000              ( 13.320014   ) 
#       active_record model - with transactions 11.560000   0.360000  11.920000    ( 14.245436   ) 
#       active_record model - without transactions 11.240000   0.730000  11.970000 ( 18.405799   ) 
#       mongo_mapper model 12.240000   0.480000  12.720000                         ( 15.185164   ) 
#       mongoid model 10.760000   0.590000  11.350000                              ( 85.778884   ) 
#       mongo  4.060000   0.400000   4.460000                                      ( 5.847425    ) 
#
#     n=100_000
#       user     system      total        real
#       pg - with transactions  2.550000   2.700000   5.250000                     ( 27.542305   ) 
#       pg - without transactions  3.470000   3.770000   7.240000                  ( 87.406853   ) 
#       sequel - with transactions 69.130000   4.860000  73.990000                 ( 102.175770  ) 
#       sequel - without transactions 69.320000   5.030000  74.350000              ( 140.852722  ) 
#       active_record model - with transactions 234.690000   3.750000 238.440000    ( 265.734381  ) 
#       active_record model - without transactions 130.730000   7.130000 137.860000 ( 213.607269  ) 
#       mongo_mapper model 141.110000   4.230000 145.340000                         ( 170.478233  ) 
#       mongoid model 155.750000   5.790000 161.540000                              ( 6950.227399 ) 
#       mongo 47.980000   3.750000  51.730000                                      ( 68.493900   ) 
#
#
#
# conclusions:
#
#  - raw pg is almost as fast(er) as raw mongo and a *lot* more durable.
#
#  - ar has gotten much, much better than it used to be.
#
#  - the speed of mongo isn't that compelling for simple queries (massive joins may be different).
#
#  - optimizing orm actions via sql is still sexy and effective.
#
#


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

## 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

## setup mongo mapper class
#
  MongoMapper.connection = Mongo::Connection.new('localhost')
  MongoMapper.database = C.db
  class MM
    include MongoMapper::Document
    set_collection_name C.table
    key(:value)
  end
  MM.ensure_index(:value, :unique => true)

## 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
 
## helper methods
#
  def new_values() Array.new($n).map{ UUIDTools::UUID.timestamp_create.to_s } end

  def transaction(which, obj, &block)
    case which.to_s
      when /without/
        block.call()
      when /with/
        if obj.respond_to?(:exec)
          obj.exec('begin transaction')
          block.call()
          obj.exec('commit')
        else
          obj.transaction(&block)
        end
    end
  end

## bench
#
  Benchmark.bm do |bm|
  ## grab some handles on drivers
  #
    sequel = S[:bench]
    mongo = M.collection.db.collection(C.table)
    pg = A.connection.raw_connection

  ## multiplex transactions on/off for rdbms
  #
    ## pg
    #
      %w( with without ).each do |which|
        GC.start
        values = new_values
        bm.report "pg - #{ which } transactions" do
          transaction(which, pg) do
            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
          end
        end
      end

    ## sequel 
    #
      %w( with without ).each do |which|
        GC.start
        values = new_values
        bm.report "sequel - #{ which } transactions" do
          transaction(which, S) do
            values.each do |value|
              id = sequel.insert(:value => value)
              sequel.where(:id => id).first
            end
          end
        end
      end

    ## active_record
    #
      %w( with without ).each do |which|
        GC.start
        values = new_values
        bm.report "active_record model - #{ which } transactions" do
          transaction(which, A) do
            values.each do |value|
              r = A.create!(:value => value)
              A.where(:id => r.id).first
            end
          end
        end
      end


  ## mongo tests don't have transactions so... 
  #

    ## mongo_mapper
    #
      GC.start
      values = new_values
      bm.report 'mongo_mapper model' do
        values.each do |value|
          r = MM.new(:value => value)
          r.save(:safe => true)
          MM.find(r.id)
        end
      end

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

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

First pass change for Mongoid:

Instead of:

M.where(:id => r.id).first

Use:

M.find(r.id)

Model#where is going to do some magic trying to convert id fields to BSON::ObjectIds if applicable - see if that change first speeds up Mongoid for you.

I'll do a separate profiling of Mongoid #safely next to see if there's a bottleneck there. Will be in the morning.

@durran - i'll make runs with your mods when complete. if you have stats pls link here - we'd love to get to the bottom of this!

There's apparently an issue with the Mongoid "safely" proxy that is sending MongoDB into a frenzy... Bernerd and I are doing performance related issues right now and will get to the bottom of it. In the meantime here's a workaround and my resulting benchmarks on my laptop. I only included the Mongo related numbers.

n=1000
user system total real
mongo_mapper model 1.340000 0.070000 1.410000 ( 1.737249)
mongoid model 0.630000 0.050000 0.680000 ( 0.842343)
mongo 0.350000 0.050000 0.400000 ( 0.545287)

n=10000
user system total real
mongo_mapper model 10.820000 0.570000 11.390000 ( 14.203879)
mongoid model 6.510000 0.500000 7.010000 ( 8.704443)
mongo 3.560000 0.480000 4.040000 ( 5.577869)

n=100000
user system total real
mongo_mapper model114.700000 5.660000 120.360000 (150.070910)
mongoid model 74.860000 5.490000 80.350000 (100.592700)
mongo 46.340000 5.270000 51.610000 ( 72.534238)

The change I made:

## mongoid
#
  GC.start
  values = new_values
  bm.report 'mongoid model' do
    values.each do |value|
      r = M.new(:value => value)
      r.insert(:safe => true)
      M.find(r.id)
    end
  end

This is:
MongoMapper: 0.9.1
Mongoid: 2.1.0 (master)
Mongo: 1.3.1

One thing that's really messing with me now is that I did a puts of the safety options in the proxy for #safely, and now I dont see the previous issue anymore when I revert the code bask to Mongoid.safely.create! I hate these kinds of issues... Here's the BM on that now (Looks like it's actually something to do with me commenting out the MM and Mongo driver perf tests and running Mongoid by itself - some sort of conflict...)

n=100000
user system total real
mongoid model 68.360000 5.290000 73.650000 ( 91.670074)

Running only this:

## mongoid
#
  GC.start
  values = new_values
  bm.report 'mongoid model' do
    values.each do |value|
      r = M.safely.create!(:value => value)
      M.find(r.id)
    end
  end

oh crap - i'll re-factor so that each test runs in it's own process with only it's libs loaded!

Actually what's much more possible is MongoDB is blocking while syncing to disk... I am seeing that happen now in my larger benchmark runs, as well as journaling causing huge spikes on the database side, which I've now turned off. (Didn't realize homebrew actually had it turned on in it's formula.)

interesting. any server setup advice for mongo based on that fact then?