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
