Installing Dena's HandlerSocket NoSQL plugin for MySQL on Centos

So after reading Yoshinori Matsunobu's blog post I could not resist installing HandlerSocket NoSQL plugin for MySQL. I took a testing machine DELL R510 with 32GB ram, 2x143GB system disks and 12x300GB raid 10 array for MySQL data dir.

So let's start...

Running as root user

cd
mkdir install
cd install

MySQL data dir LVM setup

yum install kmod-xfs.x86_64 xfsprogs.x86_64
pvcreate /dev/sdb
vgcreate mysql /dev/sdb
lvcreate -L500G -n data mysql
mkfs.xfs /dev/mysql/data
echo "/dev/mapper/mysql-data  /var/lib/mysql          xfs     rw,noatime,nobarrier    0 0" >> /etc/fstab
mount /var/lib/mysql

Handler socket source from http://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

wget http://download.github.com/ahiguti-HandlerSocket-Plugin-for-MySQL-7aceb31.tar.gz

* notte the file name you might get a different one

Downloading MySQL binaries

wget http://mysql_mirror/mysql/Downloads/MySQL-5.1/MySQL-server-community-5.1.51-1.rhel5.x86_64.rpm
wget http://mysql_mirror/mysql/Downloads/MySQL-5.1/MySQL-client-community-5.1.51-1.rhel5.x86_64.rpm
wget http://mysql_mirror/mysql/Downloads/MySQL-5.1/MySQL-shared-compat-5.1.51-1.rhel5.x86_64.rpm
wget http://mysql_mirror/mysql/Downloads/MySQL-5.1/MySQL-devel-community-5.1.51-1.rhel5.x86_64.rpm

Downloading MySQL source

wget http://mysql_mirror/mysql/Downloads/MySQL-5.1/mysql-5.1.51.tar.gz

Installing centos packages

yum install perl libtool gcc make openssl-devel gcc-c++

Installing HandlerSocket

tar zxvf ahiguti-HandlerSocket-Plugin-for-MySQL-7aceb31.tar.gz
cd ahiguti-HandlerSocket-Plugin-for-MySQL-7aceb31
./autogen.sh
./configure --with-mysql-source=/root/install/mysql-5.1.51 --with-mysql-bindir=/usr/bin
make
make install

yum install perl-DBI perl-DBD-MySQL.x86_64
cd perl-Net-HandlerSocket/
perl Makefile.PL
make
make install

Preparing MySQL

cd
cd install
rpm -i MySQL-server-community-5.1.51-1.rhel5.x86_64.rpm
rpm -i MySQL-client-community-5.1.51-1.rhel5.x86_64.rpm
rpm -i MySQL-shared-compat-5.1.51-1.rhel5.x86_64.rpm
rpm -i MySQL-devel-community-5.1.51-1.rhel5.x86_64.rpm

I am using a bit diffrent directory structure for MySQL data dir and logs.
This my.cnf file

[all]
socket=/mysql/mysql.sock

[/usr/bin/perl]
socket=/mysql/mysql.sock

[mysqladmin]
socket=/mysql/mysql.sock

[mysqlslap]
socket=/mysql/mysql.sock

[mysqldump]
socket=/mysql/mysql.sock
quick
max_allowed_packet = 16M

[mysql]
socket=/mysql/mysql.sock
#ervero-auto-rehash
character-set=utf8

[isamchk]
datadir=/var/lib/mysql
key_buffer = 8M
sort_buffer_size = 512M
read_buffer = 2M
write_buffer = 8M

[myisamchk]
datadir=/var/lib/mysql
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
datadir=/var/lib/mysql
socket=/mysql/mysql.sock
interactive-timeout

[mysql_installdb]
datadir=/var/lib/mysql

[safe_mysqld]
datadir=/var/lib/mysql
log-error=/mysql/logs/error.log
pid-file=/mysql/mysqld.pid
open-files-limit = 8192

[mysqld]
plugin-load=handlersocket.so
loose_handlersocket_port = 9998
    # the port number to bind to (for read requests)
loose_handlersocket_port_wr = 9999
    # the port number to bind to (for write requests)
loose_handlersocket_threads = 16
    # the number of worker threads (for read requests)
loose_handlersocket_threads_wr = 1
    # the number of worker threads (for write requests)
#open_files_limit = 65535
    # to allow handlersocket accept many concurrent
    # connections, make open_files_limit as large as
    # possible.

auto-increment-increment = 4
auto-increment-offset = 2
character-set-server=utf8
default_table_type = InnoDB
pid-file=/mysql/mysqld.pid
datadir=/var/lib/mysql
socket=/mysql/mysql.sock
log-bin=/mysql/binlogs/handler-socket-test-server-binlog
expire_logs_days=1
relay-log=/mysql/relaylogs/handler-socket-test-server-relay-bin
relay-log-index=/mysql/relaylogs/handler-socket-test-server-relay-bin.index
relay-log-info-file=/mysql/relaylogs/handler-socket-test-server-relay-log.info
master-info-file=/mysql/mysql-master.info
log-slave-updates
server-id=2
slow_query_log=/mysql/logs/slow.log
long_query_time=2
log_short_format
skip-external-locking
skip-name-resolve
max_connections=2000
key_buffer_size = 32M
table_cache=4096
query_cache_type = 0
query_cache_limit = 4M
query_cache_size = 0
long_query_time=3
max_allowed_packet = 16M
sort_buffer_size = 128k
join_buffer_size = 2M
read_buffer_size = 128k
read_rnd_buffer_size = 128k
myisam_sort_buffer_size = 16M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

innodb_file_per_table
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 25G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 16
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_flush_method=O_DIRECT
innodb_commit_concurrency=0

thread_cache_size = 16
thread_cache = 8
thread_concurrency = 8

max_connect_errors = 1000
back_log = 50
thread_stack = 128K
transaction_isolation = REPEATABLE-READ
max_heap_table_size = 256M
tmp_table_size = 256M
bulk_insert_buffer_size = 32M
ft_min_word_len = 4

Now let's configure and start MySQL

mkdir /mysql/{logs,relaylogs,binlogs} -p
chown mysql:mysql /mysql/ -R

rm -rf /var/lib/mysql/*
chown mysql:mysql /var/lib/mysql/
su -c mysql_install_db - mysql
/etc/init.d/mysql start

mysql
show processlist
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| Id | User        | Host            | db            | Command | Time | State                                     | Info             |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
|  1 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  2 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  3 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  4 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  5 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  6 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  7 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  8 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  9 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 10 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 11 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 12 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 13 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 14 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 15 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 16 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 17 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL             |
| 20 | root        | localhost       | NULL          | Query   |    0 | NULL                                      | show processlist |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+

If you are not seeing HandlerSocket threads in processlist like above something went wrong :(

Create a test table

use test
CREATE TABLE user (
  user_id INT UNSIGNED PRIMARY KEY,
  user_name VARCHAR(50),
  user_email VARCHAR(255),
  created DATETIME 
) ENGINE=InnoDB; 
insert into user (user_id, user_name, user_email, created ) values ( 101, 'golan zakai', 'golan.zakai@lalal4nd.org', now() );

Here is a script for testing copied from Yoshinori Matsunobu's blog post

#!/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();

Testing...

root@localhost.localdomain ~ <$> perl sample.pl
golan zakai     golan.zakai@lalal4nd.org        2010-10-23 15:07:16
root@localhost.localdomain ~ <$>

The weekend almost over I hope to get a chance to benchmark this soon enough....


Cheers !