UsingMySQLasaNoSQL_MySQL

[matsunobu@host ~]$ mysqlslap --query="select user_name,.. from test.user where user_id=1" \\
--number-of-queries=10000000 --concurrency=30 --host=xxx -uroot

[matsunobu@host ~]$ mysqladmin extended-status -i 1 -r -uroot \\
| grep -e "Com_select"

...
| Com_select | 107069 |
| Com_select | 108873 |
| Com_select | 108921 |
| Com_select | 109511 |
| Com_select | 108084 |
| Com_select | 108483 |
| Com_select | 108115 |
...

[matsunobu@host ~]$ vmstat 1

r b swpd free buff cache in cs us sy id wa st
23 0 0 963004 224216 29937708 58242 163470 59 28 12 0 0
24 0 0 963312 224216 29937708 57725 164855 59 28 13 0 0
19 0 0 963232 224216 29937708 58127 164196 60 28 12 0 0
16 0 0 963260 224216 29937708 58021 165275 60 28 12 0 0
20 0 0 963308 224216 29937708 57865 165041 60 28 12 0 0

samples % app name symbol name
259130 4.5199 mysqld MYSQLparse(void*)
196841 3.4334 mysqld my_pthread_fastmutex_lock
106439 1.8566 libc-2.5.so _int_malloc
94583 1.6498 bnx2 /bnx
284550 1.4748 ha_innodb_plugin.so.0.0.0 ut_delay
67945 1.1851 mysqld _ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
63435 1.1065 mysqld JOIN::optimize()
55825 0.9737 vmlinux wakeup_stack_begin
55054 0.9603 mysqld MYSQLlex(void*, void*)
50833 0.8867 libpthread-2.5.so pthread_mutex_trylock
49602 0.8652 ha_innodb_plugin.so.0.0.0 row_search_for_mysql
47518 0.8288 libc-2.5.so memcpy
46957 0.8190 vmlinux .text.elf_core_dump
46499 0.8111 libc-2.5.so malloc

CREATE TABLE user
(
user_id INT UNSIGNED PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(255),
created DATETIME
)
ENGINE=InnoDB;

mysql> SELECT user_name, user_email, created FROM user WHERE user_id=101;
+---------------+-----------------------+---------------------+
| user_name | user_email | created
| +---------------+-----------------------+---------------------+
| Yukari Takeba | yukari.takeba@dena.jp | 2010-02-03 11:22:33
| +---------------+-----------------------+---------------------+
1 row in set (0.00 sec)

[root@localhost handlersocket]# ./configure --with-mysql-source=mysql-source-dir --with-mysql-bindir=mysql-server-bin-dir
[root@localhost handlersocket]# make
[root@localhost handlersocket]# make install

mysql> INSTALL PLUGIN \'handlersocket\' soname \'handlersocket.so\';

#!/usr/bin/perl 

use strict;
use warnings;
use Net::HandlerSocket; 

#1. establishing a connection
my $args = { host => \'ip_to_remote_host\', port => 9998 };
my $hs = new Net::HandlerSocket($args); 

#2. initializing an index so that we can use in main logics.
# MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, \'test\', \'user\', \'PRIMARY\',
 \'user_name,user_email,created\');
die $hs->get_error() if $res != 0; 

#3. main logic
#fetching rows by id
#execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, \'=\', [ \'101\' ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
for (my $row = 0; $row < 1; ++$row) {
 my $user_name= $res->[$row + 0];
 my $user_email= $res->[$row + 1];
 my $created= $res->[$row + 2];
 print "$user_name\\t$user_email\\t$created\\n";
} 

#4. closing the connection
$hs->close()

#!/usr/bin/perl 

use strict;
use warnings;
use Net::HandlerSocket; 

#1. establishing a connection
my $args = { host => \'ip_to_remote_host\', port => 9998 };
my $hs = new Net::HandlerSocket($args); 

#2. initializing an index so that we can use in main logics.
# MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, \'test\', \'user\', \'PRIMARY\',
 \'user_name,user_email,created\');
die $hs->get_error() if $res != 0; 

#3. main logic
#fetching rows by id
#execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, \'=\', [ \'101\' ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
for (my $row = 0; $row < 1; ++$row) {
 my $user_name= $res->[$row + 0];
 my $user_email= $res->[$row + 1];
 my $created= $res->[$row + 2];
 print "$user_name\\t$user_email\\t$created\\n";
} 

#4. closing the connection
$hs->close();

[matsunobu@host ~]$ perl sample.pl
Yukari Takeba yukari.takeba@dena.jp 2010-02-03 11:22:33

[matsunobu@host ~]$ telnet 192.168.1.2 9998
Trying 192.168.1.2...
Connected to xxx.dena.jp (192.168.1.2).
Escape character is \'^]\'.
P 0 test user PRIMARY user_name,user_email,created
0 1
0 = 1 101
0 3 Yukari Takeba yukari.takeba@dena.jp 2010-02-03 11:22:33
(Green lines are request packets, fields must be separated by TAB)

 approx qps server CPU util
MySQL via SQL 105,000 %us 60% %sy 28%
Memcached 420,000 %us 8% %sy 88%
MySQL via HandlerSocket 750,000 %us 45% %sy 53%

samples % app name symbol name
984785 5.9118 bnx2 /bnx2
847486 5.0876 ha_innodb_plugin.so.0.0.0 ut_delay
545303 3.2735 ha_innodb_plugin.so.0.0.0 btr_search_guess_on_hash
317570 1.9064 ha_innodb_plugin.so.0.0.0 row_search_for_mysql
298271 1.7906 vmlinux tcp_ack
291739 1.7513 libc-2.5.so vfprintf
264704 1.5891 vmlinux .text.super_90_sync 

248546 1.4921 vmlinux blk_recount_segments
244474 1.4676 libc-2.5.so _int_malloc
226738 1.3611 ha_innodb_plugin.so.0.0.0 _ZL14build_template P19row_prebuilt_structP3THDP8st_tablej
206057 1.2370 HandlerSocket.so dena::hstcpsvr_worker::run_one_ep()
183330 1.1006 ha_innodb_plugin.so.0.0.0 mutex_spin_wait
175738 1.0550 HandlerSocket.so dena::dbcontext:: cmd_find_internal(dena::dbcallback_i&, dena::prep_stmt const&, ha_rkey_function, dena::cmd_exec_args const&)
169967 1.0203 ha_innodb_plugin.so.0.0.0 buf_page_get_known_nowait
165337 0.9925 libc-2.5.so memcpy
149611 0.8981 ha_innodb_plugin.so.0.0.0 row_sel_store_mysql_rec
148967 0.8943 vmlinux generic_make_request

$ mysqladmin extended-status -uroot -i 1 -r | grep "InnoDB_rows_read"
...
| Innodb_rows_read | 750192 |
| Innodb_rows_read | 751510 |
| Innodb_rows_read | 757558 |
| Innodb_rows_read | 747060 |
| Innodb_rows_read | 748474 |
| Innodb_rows_read | 759344 |
| Innodb_rows_read | 753081 |
| Innodb_rows_read | 754375 |
...