MySQL Benchmark Script

#!/usr/bin/env ruby

# Script to benchmark mysql equivalents of the Tokyo Table Queries

require 'rubygems'
require 'mysql'
require 'benchmark'

puts "Benchmarking mysql with 2888750 email records"

$list_of_pks = ["56811346", "69405964", "56811364", "56811376", "56811382", "56811388", "56811430", "56811436", "56811448", "56811454", "56811472", "56811478", "56811484", "56811508", "56811526", "56811532", "56811574", "56811592", "56811601", "56811616", "56811646", "56811649", "56811694", "56811721", "56811748", "56811766", "56811784", "56811796", "56811799", "56811811", "56811823", "56811853", "56811862", "56874616", "56811892", "56811895", "56811913", "56811919", "56818771", "56811946"]

$message_id = "5b52cc74302e11deafbad6bb3bc7cc5c"
$domain = "szul.com"
$policy_id = 2137041988

### MySQL Query Function #########################################
def bm_mysql(dbh)
  Benchmark.benchmark(' ' * 25 + Benchmark::Tms::CAPTION, 25) do |b|

    b.report('find record by pk') do
      pk_to_get = $list_of_pks[rand(40)]
      results = dbh.query("SELECT * FROM metadata WHERE id = #{pk_to_get}")
      results_to_array(results)
    end
    
    b.report('find by message_id') do
      results = dbh.query("SELECT * FROM metadata WHERE message_id = '#{$message_id}'")
      results_to_array(results)
    end
    
    b.report('pk_only message id') do
      results = dbh.query("SELECT id FROM metadata WHERE message_id = '#{$message_id}'")
      results_to_array(results)
    end
    
    b.report('find by domain') do
      results = dbh.query("SELECT * FROM metadata WHERE domain = '#{$domain}'")
      results_to_array(results)
    end
    
    b.report('pk_only domains') do
      results = dbh.query("SELECT id FROM metadata WHERE domain = '#{$domain}'")
      results_to_array(results)
    end
    
    b.report('find by policy_id') do
      results = dbh.query("SELECT * FROM metadata WHERE policy_id = #{$policy_id}")
      results_to_array(results)
    end
    
    b.report('pk_only policy_id') do
      results = dbh.query("SELECT id FROM metadata WHERE policy_id = #{$policy_id}")
      results_to_array(results)
    end
    
    b.report('pk/ruby policy_id (b=1K)') do
      results = dbh.query("SELECT id FROM metadata WHERE policy_id = #{$policy_id}")
      full_results = map_results_to_records(dbh, results, 1000)
    end
    
    # b.report('pk/ruby policy_id (b=10K)') do
    #   results = dbh.query("SELECT id FROM metadata WHERE policy_id = #{$policy_id}")
    #   full_results = map_results_to_records(dbh, results, 10000)
    # end
    # 
    # b.report('pk/ruby policy_id (b=100)') do
    #   results = dbh.query("SELECT id FROM metadata WHERE policy_id = #{$policy_id}")
    #   full_results = map_results_to_records(dbh, results, 100)
    # end
  end
end

def results_to_array(results)
  r = []
  results.each_hash do |row|
    r << row
  end
  r
end

def map_results_to_records(dbh, results, batch_size = 1000)
  ids = []
  results.each { |r| ids << r.first.to_i }
  slices = slice_array(ids, batch_size)
  hashes = []
  slices.each do |batch|
    res = dbh.query("SELECT * FROM metadata WHERE id IN(#{batch.join(", ")})")
    hashes << results_to_array(res)
  end
  hashes.flatten
end

def slice_array(array, batch_size = 1000)
  slices = []
  counter = 0
  while(counter < array.size) do
    slices << array[counter, batch_size]
    counter += batch_size
  end
  slices
end

dbh = Mysql.new('localhost', 'merf', 'ha-ha-ha', 'message_metadata_test')

2.times { puts }
puts 'MySQL ISAM / MySQL-Ruby / Indexed / Untuned'
puts

bm_mysql(dbh)