2012年4月30日月曜日

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

MacにMySQL(5.5系)とHandlerSocketをいれた


# mysqlをインストール
# 既にインストール済みなら同じverのソースを持ってくる
brew install mysql

# ここを参考に。
http://www.bigegg.net/post/4651592197/handlersocket-osx

cp /Library/Caches/Homebrew/mysql-5.5.20.tar.gz .
tar zxvf mysql-5.5.20.tar.gz 
cd mysql-5.5.20
# configure.cmakeにバグがあるので、下記をみて修正。
http://bugs.mysql.com/bug.php?id=65050
cmake .

# 起動するも失敗
mysql.server start
ERROR! The server quit without updating PID file (…)

# conf
cp /usr/local/Cellar/mysql/5.5.20/support-files/my-small.cnf /etc/my.cnf 

mysql_install_db --verbose --user=`whoami` --basedir=/usr/local/Cellar/mysql/5.5.20 --datadir=/usr/local/var/mysql

# 再度起動
mysql.server start
Starting MySQL
.. SUCCESS! 

# handler socketをインストール
# ここを参考に。
https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

git clone git://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL.git
cd HandlerSocket-Plugin-for-MySQL/

./autogen.sh
./configure --with-mysql-source=/Users/xxx/src/mysql-5.5.20(mysqlソースパス) --with-mysql-bindir=/usr/local/Cellar/mysql/5.5.20/bin/(mysqlコマンドのパス)  --with-mysql-plugindir=/usr/local/Cellar/mysql/5.5.20/lib/plugin
make
make install

# 別コンソールでmysqlを起動し、
mysql> install plugin handlersocket soname 'handlersocket.so';

# プラグインが入ったかな。
ls /usr/local/Cellar/mysql/5.5.20/lib/plugin/

# handlersocketの設定を入れる。
sudo vi /etc/my.cnf 
handlersocket_port = 9998
handlersocket_port_wr = 9999
handlersocket_address =
handlersocket_verbose = 0
handlersocket_timeout = 300
handlersocket_threads = 16
thread_concurrency = 128
open_files_limit = 65535

# mysql再起動
mysql.server stop
Shutting down MySQL
. SUCCESS! 
mysql.server start
Starting MySQL
.. SUCCESS! 


# 別コンソールでmysqlを起動し、
mysql> show plugins;
+-----------------------+--------+--------------------+------------------+---------+
| Name                  | Status | Type               | Library          | License |
+-----------------------+--------+--------------------+------------------+---------+
| binlog                | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| mysql_native_password | ACTIVE | AUTHENTICATION     | NULL             | GPL     |
| mysql_old_password    | ACTIVE | AUTHENTICATION     | NULL             | GPL     |
| CSV                   | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| MEMORY                | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| MyISAM                | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| MRG_MYISAM            | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| InnoDB                | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| INNODB_TRX            | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_LOCKS          | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_LOCK_WAITS     | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMP            | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMP_RESET      | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMPMEM         | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMPMEM_RESET   | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| PERFORMANCE_SCHEMA    | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| partition             | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| handlersocket         | ACTIVE | DAEMON             | handlersocket.so | BSD     |
+-----------------------+--------+--------------------+------------------+---------+
18 rows in set (0.00 sec)

# 入った!!


# 使ってみる
use test;
CREATE TABLE `user` (
    `user_id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NULL DEFAULT NULL,
    `birthday` DATE NULL DEFAULT NULL,
    `memo` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`user_id`)
);



# ruby用クライアントを入れる
# tarはここからダウンロード
https://bitbucket.org/winebarrel/ruby-handlersocket/src


cd HandlerSocket-Plugin-for-MySQL/ ←ここでmakeせねばならん。
tar zxvf winebarrel-ruby-handlersocket-c19841e47ea2.tar.gz 
cp winebarrel-ruby-handlersocket-c19841e47ea2/* .
ruby extconf.rb
make
make install

# ------------------------------------
# sample.rb
require 'handlersocket'

hs = HandlerSocket.new('127.0.0.1', 9999)
# Args (default value)
# host(localhost), port(9998), timeout(600), listen_backlog(256)

# 3は識別子
hs.open_index(3, 'test', 'user', 'PRIMARY', 'user_id,name,birthday,memo');

# select 接続、条件、条件値、count、offset
p res = hs.execute_single(3, '>=', ['2'], 100, 0);

# insert 接続、行
hs.execute_insert(3, [1,"name1","2001-01-01","memo1"]);
hs.execute_insert(3, [2,"name2","2001-01-01","memo2"]);
hs.execute_insert(3, [3,"name3","2001-01-01","memo3"]);

# delete 接続、条件、条件値、count、offset ← selectして消すため
# 詳細はここ
# https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-ja/perl-client.ja.txt
hs.execute_delete(3, '=', ['2'], 1, 0);

# 高速なexecute_multi
p hs.execute_multi(
  [
    [3, '=', ['3'], 1, 0],
    [3, '=', ['1'], 1, 0]
  ]
)

hs.close




# ------------------------------------
sample.rb

require 'handlersocket'

hs = HandlerSocket.new('127.0.0.1', 9999)
# Args (default value)
# host(localhost), port(9998), timeout(600), listen_backlog(256)

# 3は識別子
hs.open_index(3, 'test', 'user', 'PRIMARY', 'user_id,name,birthday,memo');

# select 接続、条件、条件値、count、offset
p res = hs.execute_single(3, '>=', ['2'], 100, 0);

# insert 接続、行
hs.execute_insert(3, [1,"name1","2001-01-01","memo1"]);
hs.execute_insert(3, [2,"name2","2001-01-01","memo2"]);
hs.execute_insert(3, [3,"name3","2001-01-01","memo3"]);

# delete 接続、条件、条件値、count、offset ← selectして消すため
# 詳細はここ
# https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-ja/perl-client.ja.txt
hs.execute_delete(3, '=', ['2'], 1, 0);

# 高速なexecute_multi
p hs.execute_multi(
  [
    [3, '=', ['3'], 1, 0],
    [3, '=', ['1'], 1, 0]
  ]
)

hs.close
次回はこちら
MacのMySQLでHandlerSocketのベンチマークをとった