mysql vs. mysql2 benchmarks

mysql2-benchmarks.rb

require 'optparse'

module WordPicker
  def pick
    self[rand(length)]
  end

  LENGTHS = [1] + [2]*3 + [3]*4 + [4]*4 + [5]*4 + [6]*3 + [7]*2 + [8]
  def sentence
    words = []
    LENGTHS[rand(LENGTHS.length)].times do
      words << pick
    end
    words.join(" ")
  end
end

class Test
  def setup
    @client.query "create database mysql2_perf_comparison"
    select_database
    @client.query "create table items ( id int not null auto_increment, content varchar(255), created_at datetime, updated_at datetime, primary key (id) )"

    words = File.readlines("/usr/share/dict/words").select { |w| w.length <= 5 }.map { |w| w.chomp }
    words.extend(WordPicker)

    1_000.times do
      now = Time.now.utc.strftime("%Y-%m-%d %H:%M:%S")
      @client.query "insert into items (content, created_at, updated_at) values ('#{words.sentence}', '#{now}', '#{now}')"
    end
  end

  def select_database
    @client.query "use mysql2_perf_comparison"
  end

  def noop(name, value)
  end
end

class MysqlTest < Test
  def initialize(options={})
    require 'mysql'
    @client = Mysql.init
    @client.real_connect("localhost", "root", nil, nil, nil, nil, 0)
  end

  def name
    "mysql"
  end

  def run(options={})
    results = @client.query("SELECT #{options[:select] || "*"} FROM items")
    results.each_hash do |row|
      row.each do |name, value|
        value.force_encoding('utf-8') if options[:force] && value.is_a?(String)
        noop(name, value)
      end
    end
    results.free
  end
end

class Mysql2Test < Test
  def initialize(options={})
    require 'mysql2'
    @client = Mysql2::Client.new(:host => "localhost", :username => "root")
  end

  def name
    "mysql2"
  end

  def run(options={})
    results = @client.query("SELECT #{options[:select] || "*"} FROM items", :cast => options[:cast])
    results.each do |row|
      row.each { |name, value| noop(name, value) }
    end
  end
end

options = { :cast => true }

opts = OptionParser.new do |opts|
  opts.banner = "USAGE: #{$0} [options]"
  opts.separator ""

  opts.on "-p", "--prepare", "prepare the test database" do
    options[:prepare] = true
  end

  opts.on "-1", "--mysql1", "use mysql test" do
    options[:mysql2] = false
  end

  opts.on "-2", "--mysql2", "use mysql2 test" do
    options[:mysql2] = true
  end

  opts.on "-c", "--[no-]cast", "use typecasting (default true, mysql2 only)" do |flag|
    options[:cast] = flag
  end

  opts.on "-f", "--[no-]force-encoding", "force encoding to utf-8 (default false, mysql only)" do |flag|
    options[:force] = flag
  end

  opts.on "-h", "--help" do
    puts opts
    exit
  end
end.parse!

test = (options[:mysql2] ? Mysql2Test : MysqlTest).new(options)

if options[:prepare]
  test.setup
  puts "done!"
else
  require 'benchmark'

  puts "beginning #{test.name} test"
  puts "  --no-cast" if options[:mysql2] && !options[:cast]
  puts "  --force-encoding" if !options[:mysql2] && options[:force]

  test.select_database
  Benchmark.bm(30) do |x|
    x.report("select *") { 100.times { test.run(options.merge(:select => "*")) } }
    x.report("select id") { 100.times { test.run(options.merge(:select => "id")) } }
    x.report("select id, id, id") { 100.times { test.run(options.merge(:select => "id, id, id")) } }
    x.report("select content") { 100.times { test.run(options.merge(:select => "content")) } }
    x.report("select created_at") { 100.times { test.run(options.merge(:select => "created_at")) } }
    x.report("select created_at, updated_at") { 100.times { test.run(options.merge(:select => "created_at, updated_at")) } }
  end
end

results.txt

1.9.3|~ $ ruby mysql-test.rb -1
beginning mysql test
                                     user     system      total        real
select *                         0.410000   0.010000   0.420000 (  0.520260)
select id                        0.170000   0.000000   0.170000 (  0.230548)
select id, id, id                0.200000   0.010000   0.210000 (  0.281792)
select content                   0.190000   0.000000   0.190000 (  0.257460)
select created_at                0.180000   0.010000   0.190000 (  0.246156)
select created_at, updated_at    0.250000   0.000000   0.250000 (  0.336787)

1.9.3|~ $ ruby mysql-test.rb -1 --force-encoding
beginning mysql test
  --force-encoding
                                     user     system      total        real
select *                         0.560000   0.010000   0.570000 (  0.670385)
select id                        0.220000   0.000000   0.220000 (  0.279433)
select id, id, id                0.260000   0.010000   0.270000 (  0.331482)
select content                   0.240000   0.000000   0.240000 (  0.293491)
select created_at                0.220000   0.000000   0.220000 (  0.285442)
select created_at, updated_at    0.340000   0.010000   0.350000 (  0.410270)

1.9.3|~ $ ruby mysql-test.rb -2
beginning mysql2 test
                                     user     system      total        real
select *                         3.460000   0.070000   3.530000 (  3.623680)
select id                        0.260000   0.040000   0.300000 (  0.351113)
select id, id, id                0.280000   0.030000   0.310000 (  0.388913)
select content                   0.380000   0.050000   0.430000 (  0.479104)
select created_at                1.710000   0.050000   1.760000 (  1.816020)
select created_at, updated_at    3.110000   0.070000   3.180000 (  3.239320)

1.9.3|~ $ ruby mysql-test.rb -2 --no-cast
beginning mysql2 test
  --no-cast
                                     user     system      total        real
select *                         0.730000   0.060000   0.790000 (  0.893006)
select id                        0.400000   0.040000   0.440000 (  0.500514)
select id, id, id                0.540000   0.050000   0.590000 (  0.673228)
select content                   0.410000   0.040000   0.450000 (  0.514821)
select created_at                0.290000   0.040000   0.330000 (  0.387894)
select created_at, updated_at    0.370000   0.040000   0.410000 (  0.480144)