MacのMySQLでHandlerSocketのベンチマークをとった

インストールはこちらを参照
MacにMySQL(5.5系)とHandlerSocketをいれた


■前提として、1台のmacで行っているため、スケールアウトのメリットを享受できていません。
そのため、Cassandra やhbaseでは期待する効果が出ていません!!

# cassandra のキースペースを作る
cassandra-cli
[default@unknown] create keyspace Test;
[default@unknown] use Test;
[default@Test] create column family Users with column_type = 'Standard' and comparator = 'UTF8Type';

bench.rb

#!/usr/bin/ruby
# -*- encoding: utf-8 -*-
require 'rubygems'
require 'cassandra'
require 'active_record'
require 'benchmark'
require 'handlersocket'
require 'memcache'

# ---------------------------------------
# init
# ---------------------------------------
# mysql :
#   mysql
#   use Test; truncate table user;
# memcacha :
#   ps aux | grep mem
#   kill -TERM xxxx
#   memcached -d
# cassandra 
#   cassandra-cli
#   use Test;
#   truncate Users;


# ---------------------------------------
# Insert / Select 性能
# ---------------------------------------
count = 100000
p 'count:' + count.to_s

# ---------------------------------------
# mysql
ActiveRecord::Base.establish_connection(
  :adapter => 'mysql2',
  :host => 'localhost',
  :username => 'root',
  :password => '',
  :database => 'test'
)
class User < ActiveRecord::Base
  self.table_name = 'user'
end

# active record 利用
Benchmark.bm { |rep|
  rep.report("mysql(o/r) insert ") {
    for num in 1..count
      name = 'name' + num.to_s
      User.create(:name => name)
    end
  }
}
Benchmark.bm { |rep|
  rep.report("mysql(o/r) select") {
    for num in 1..count
      id = rand(count).to_i + 1
      User.find(id)
    end
  }
}
# すべて削除
User.connection.execute('truncate table user')

# SQL利用
Benchmark.bm { |rep|
  rep.report("mysql(sql) insert ") {
    for num in 1..count
      name = 'name' + num.to_s
      User.connection.execute(sprintf('insert into user (user_id, name) values(%s, "%s");', num.to_s, name))
    end
  }
}
Benchmark.bm { |rep|
  rep.report("mysql(sql) select") {
    for num in 1..count
      id = rand(count).to_i + 1
      User.connection.execute(sprintf('select user_id, name from user where user_id = %s;', num.to_s))
    end
  }
}
# すべて削除
User.connection.execute('truncate table user')


# ---------------------------------------
# cassandra
include Cassandra::Constants
client = Cassandra.new("Test", "127.0.0.1:9160")
Benchmark.bm { |rep|
  rep.report("cassandra insert ") {
    for num in 1..count
      name = 'name' + num.to_s
      client.insert(:Users, num.to_s, { 'name' => name } )
    end
  }
}
Benchmark.bm { |rep|
  rep.report("cassandra select ") {
    for num in 1..count
      id = rand(count).to_i + 1
      client.get(:Users, id.to_s)
    end
  }
}

# ---------------------------------------
# memcached
cache = MemCache.new('localhost:11211')
Benchmark.bm { |rep|
  rep.report("memcache insert ") {
    for num in 1..count
      name = 'name' + num.to_s
      cache[name] = [num, name]
    end
  }
}
Benchmark.bm { |rep|
  rep.report("memcache select ") {
    for num in 1..count
      id = rand(count).to_i + 1
      name = 'name' + id.to_s
      ret = cache[name]
    end
  }
}

# ---------------------------------------
# handlersocket
hs = HandlerSocket.new('127.0.0.1', 9999)
hs.open_index(3, 'test', 'user', 'PRIMARY', 'user_id,name');

Benchmark.bm { |rep|
  rep.report("handlersocket insert ") {
    for num in 1..count
      name = 'name' + num.to_s
      hs.execute_insert(3, [num, name]);
    end
  }
}
Benchmark.bm { |rep|
  rep.report("handlersocket select ") {
    for num in 1..count
      id = rand(count).to_i + 1
      ret = hs.execute_single(3, '=', [id], 1, 0);
    end
  }
}

100,000件で試験
mysql, cassandraはtruncate後
memcacheは再起動後

ruby bench.rb 

・1回目
"count:100000"
      user     system      total        real
mysql(o/r) insert  74.320000   5.670000  79.990000 (110.039184)
      user     system      total        real
mysql(o/r) select 50.610000   2.650000  53.260000 ( 65.465168)
      user     system      total        real
mysql(sql) insert   4.430000   1.530000   5.960000 ( 29.590552)
      user     system      total        real
mysql(sql) select  5.470000   1.780000   7.250000 ( 13.550391)
      user     system      total        real
cassandra insert  47.180000   3.140000  50.320000 ( 61.347904)
      user     system      total        real
cassandra select  69.150000   3.470000  72.620000 (111.568860)
      user     system      total        real
memcache insert  14.930000   2.280000  17.210000 ( 17.935214)
      user     system      total        real
memcache select  16.710000   2.080000  18.790000 ( 19.264883)
      user     system      total        real
handlersocket insert   1.430000   1.510000   2.940000 ( 26.721995)
      user     system      total        real
handlersocket select   1.320000   1.520000   2.840000 (  7.867557)


・2回目
"count:100000"
      user     system      total        real
mysql(o/r) insert  74.500000   5.680000  80.180000 (110.519581)
      user     system      total        real
mysql(o/r) select 48.570000   2.550000  51.120000 ( 63.455239)
      user     system      total        real
mysql(sql) insert   4.420000   1.510000   5.930000 ( 29.494878)
      user     system      total        real
mysql(sql) select  5.500000   1.800000   7.300000 ( 13.632243)
      user     system      total        real
cassandra insert  45.500000   3.130000  48.630000 ( 59.691931)
      user     system      total        real
cassandra select  66.840000   3.350000  70.190000 (108.169329)
      user     system      total        real
memcache set  14.870000   2.340000  17.210000 ( 17.894275)
      user     system      total        real
memcache get  17.220000   2.260000  19.480000 ( 20.023089)
      user     system      total        real
handlersocket insert   1.490000   1.540000   3.030000 ( 26.834126)
      user     system      total        real
handlersocket select   1.310000   1.520000   2.830000 (  7.845230)

# 前提として、1台のmacで行っているため、スケールアウトのメリットを享受できていません。
# そのため、Cassandra やhbaseでは期待する効果が出ていません!!

# Cassandraは1台だとパフォーマンスでないな。
# やっぱO/Rも重い。
# memcacheのsystem時間がたくさんかかってるのはなんでだろう。localhostだと重いのかな。



# 条件
# mac book air OSX
# 1.7GHz Intel Core i5
# 4GB 1333 MHz DDR3

mysql --version
mysql  Ver 14.14 Distrib 5.5.20, for osx10.7 (i386) using readline 5.1

cassandra -v
1.1.0

telnet localhost 11211
stats
STAT version 1.4.5 ←memcachedのバージョン

HandlerSocket-Plugin-for-MySQL
1.1.0