Benchmarking ActiveRecord + MySQL vs Tokyo Cabinet tables. Sample results at http://gist.github.com/105516

ar_mysql_vs_tc_table.rb

#!/usr/bin/env ruby

# Script to benchmark ActiveRecord + MySQL vs Tokyo Cabinet tables

require 'rubygems'
require 'benchmark'
require 'faker'
require 'date'
require 'fileutils'
require 'activerecord'
require 'rufus/edo'
require 'rufus/tokyo/tyrant'


### data ####################################################################

$people_colnames = %w{ email name gender birthday city about }
$page_view_colnames = %w{ user_id entity_id created_at updated_at }

$year = (1909 .. 2009).to_a
$month = (1..12).to_a
$day = (1..28).to_a

def gen_date
  DateTime.new($year[rand($year.size)], $month[rand($month.size)], $day[rand($day.size)])
end

def gen_gender
  (rand(2) == 1 ? 'm' : 'f')
end

def gen_user_id
  rand(70)+1
end

def gen_entity_id
  rand(300)+1
end

$N = 1_000

puts
puts Time.now.to_s
puts "N is #{$N}"
puts "ruby is #{RUBY_VERSION}"
puts
puts 'Preparing data...'

$people_data = [
  [ 'luna@brakus.mx', 'Jaylon Ortiz', 'm', DateTime.new(1972, 10, 14),
    'New Felipaland', Faker::Lorem.paragraph ],

  [ 'cierra@sauer.com', 'Torey Konopelski II', 'm', DateTime.new(1964, 07, 14),
    'Sarahville', Faker::Lorem.paragraph ],

  [ 'sonya_mrax@gmail.com', 'Sonya Mraz', 'f', DateTime.new(1980, 07, 12),
    'Kendrickview', Faker::Lorem.paragraph ],

  [ 'penelope@effertz.ee', 'Penelope Stracke', 'f', DateTime.new(1954, 07, 13),
    'Lake Velva', Faker::Lorem.paragraph ]
]

$page_view_data = [
  [ 1, 34, Time.now, Time.now],
  [ 10, 55, Time.now, Time.now]
]

$N.times do |i|
  $people_data << [ Faker::Internet.email, Faker::Name.name,
                    gen_gender, gen_date,
                    Faker::Address.city, Faker::Lorem.paragraph ]

  $page_view_data << [ gen_user_id, gen_entity_id, Time.now, Time.now ]
end

$find_email_list = []
100.times { $find_email_list << $people_data[rand($people_data.size)][0] }

$find_name_list = []
100.times { $find_name_list << $people_data[rand($people_data.size)][1] }

$find_user_view_list = []
100.times {
  $find_user_view_list << $page_view_data[rand($page_view_data.size)][0]
}

$people_data.collect! { |p| # build a hash
  (0..$people_colnames.length - 1).inject({}) { |h, i|
    h[$people_colnames[i]] = p[i]
    h
  }
}

$page_view_data.collect! { |p| # build a hash
  (0..$page_view_colnames.length - 1).inject({}) { |h, i|
    h[$page_view_colnames[i]] = p[i]
    h
  }
}

# For Tokyo Cabinet we need to convert DateTimes to strings
$tc_people_data = $people_data.collect { |p|
  h = p.dup
  h['birthday'] = h['birthday'].to_s
  h
}

# Here we must convert both integers and timestamps (all fields) to strings
$tc_page_view_data = $page_view_data.collect { |p|
  h = p.dup
  h.keys.each { |k| h[k] = h[k].to_s }
  h
}

$find_tc_user_view_list = []
100.times {
  $find_tc_user_view_list << $tc_page_view_data[rand($tc_page_view_data.size)]["user_id"]
}

### ActiveRecord ############################################################

class PeopleMigration < ActiveRecord::Migration

  def self.up
    create_table :people do |t|
      t.string   :email
      t.string   :name
      t.string   :gender
      t.datetime :birthday
      t.string   :city
      t.text     :about
    end
    add_index :people, :email
  end

  def self.down
    drop_table :people
  end
end

class PageViewMigration < ActiveRecord::Migration

  def self.up
    create_table :page_views do |t|
      t.integer :user_id
      t.integer :entity_id
      t.timestamps
    end
  end

  def self.down
    drop_table :page_views
  end
end

# In MySQL console:
# create database benchmark character set utf8;
#

ActiveRecord::Base.establish_connection(
  :adapter => 'mysql',
  :database => 'benchmark',
  :encoding => 'utf8')

begin
  PeopleMigration.down
  PageViewMigration.down
rescue Exception # when tables don't exist
ensure
  PeopleMigration.up
  PageViewMigration.up
end

class Person < ActiveRecord::Base; end
class PageView < ActiveRecord::Base; end

puts 'ActiveRecord with MySQL adapter'
puts
puts 'People'

Benchmark.benchmark(' ' * 20 + Benchmark::Tms::CAPTION, 20) do |b|

  b.report('inserting data') do
    $people_data.each { |h| Person.create(h) }
  end
  
  b.report('finding all') do
    Person.find(:all)
  end
  
  b.report('finding last') do
    Person.find($people_data.size)
  end

  b.report('find emails (i)') do
    $find_email_list.each do |name|
      Person.find_by_email(name)
    end
  end

  b.report('find sonya email (i)') do
    Person.find_by_email($people_data[2]['email'])
  end

  b.report('find names') do
    $find_name_list.each do |name|
      Person.find_by_name(name)
    end
  end
end

2.times { puts }
puts 'PageViews'

Benchmark.benchmark(' ' * 20 + Benchmark::Tms::CAPTION, 20) do |b|

  b.report('inserting data') do
    $page_view_data.each { |h| PageView.create(h) }
  end
  
  b.report('finding all') do
    PageView.find(:all)
  end
  
  b.report('finding last') do
    PageView.find($page_view_data.size)
  end

  b.report('find user_ids') do
    $find_user_view_list.each do |uid|
      PageView.find_by_user_id(uid)
    end
  end

  b.report('find user_id 10') do
    PageView.find_by_user_id(10)
  end
end

### Tokyo Cabinet Table #####################################################

def bm_tokyo_people(table)
  Benchmark.benchmark(' ' * 20 + Benchmark::Tms::CAPTION, 20) do |b|

    b.report('inserting data') do
      $tc_people_data.each_with_index { |e, i| table[i.to_s] = e }
    end

    b.report('finding all') do
      table.query { |q| }
    end

    b.report('find last') do
      table[$tc_people_data.size.to_s]
    end

    b.report('find emails (i)') do
      $find_email_list.each do |email|
        table.query { |q| q.add('email', :equals, email) }
      end
    end

    b.report('find sonya email (i)') do
      table.query { |q| q.add('email', :equals, $tc_people_data[2]['email']) }
    end

    b.report('find names') do
      $find_name_list.each do |name|
        table.query { |q| q.add('name', :equals, name) }
      end
    end
  end
end

def bm_tokyo_page_views(table)
  Benchmark.benchmark(' ' * 20 + Benchmark::Tms::CAPTION, 20) do |b|

    b.report('inserting data') do
      $tc_page_view_data.each_with_index { |e, i| table[i.to_s] = e }
    end

    b.report('finding all') do
      table.query { |q| }
    end

    b.report('find last') do
      table[$tc_page_view_data.size.to_s]
    end

    b.report('find user_ids') do
      $find_tc_user_view_list.each do |uid|
        table.query { |q| q.add('user_id', :numequals, uid) }
      end
    end

    b.report('find user_id 10') do
      table.query { |q| q.add('user_id', :numequals, '10') }
    end
  end
end

FileUtils.rm_f('people.tct')
FileUtils.rm_f('people.tct.idx.email.lex')

table = Rufus::Edo::Table.new('people.tct',
                              :mode => 'wc', :mutex => true)

table.set_index('email', :keep)

2.times { puts }
puts 'Tokyo Cabinet table, direct access to file'
puts
puts 'People'

bm_tokyo_people(table)

2.times { puts }
puts 'PageViews'

FileUtils.rm_f('page_views.tct')

table = Rufus::Edo::Table.new('page_views.tct',
                              :mode => 'wc', :mutex => true)
bm_tokyo_page_views(table)

### Tokyo Tyrant
# run:
# ttserver -port 20090 people_tt.tct
# ttserver -port 20091 page_views_tt.tct

puts
puts "Benchmark Tokyo Tyrant? [y/n]"
exit if gets.strip != 'y'

2.times { puts }
puts 'Tokyo Tyrant'; puts;
puts 'People'

table = Rufus::Tokyo::TyrantTable.new('localhost', 20090)
bm_tokyo_people(table)

2.times { puts }
puts 'PageViews'

table = Rufus::Tokyo::TyrantTable.new('localhost', 20091)
bm_tokyo_page_views(table)

# the_end