Starting with the great work of Yoshinori-san Using MySQL as a NoSQL – A story for exceeding 750,000 qps on a commodity server and Golan Zakai who posted Installing Dena’s HandlerSocket NoSQL plugin for MySQL on Centos I configured and tested HandlerSocket under Ubuntu 10.04 64bit.
NOTE: This machine already compiles MySQL and Drizzle. You should refer to appropriate source compile instructions for necessary dependencies.
# Get Software
cd /some/path
export DIR=`pwd`
wget http://download.github.com/ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-10-gd032ec0.tar.gz
wget http://mysql.mirror.iweb.ca/Downloads/MySQL-5.1/mysql-5.1.52.tar.gz
wget http://mysql.mirror.iweb.ca/Downloads/MySQL-5.1/mysql-5.1.52-linux-x86_64-glibc23.tar.gz
# Install MySQL Binaries
tar xvfz mysql-5.1.52-linux-x86_64-glibc23.tar.gz
ln -s mysql-5.1.52-linux-x86_64-glibc23 mysql5152
rm -rf mysql5152/mysql-test
rm -rf mysql5152/sql-bench
cd mysql5152
scripts/mysql_install_db
bin/mysqld_safe &
# Install MySQL Source
tar xvfz mysql-5.1.52.tar.gz
# Install HandlerSocket
tar xvfz ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-10-gd032ec0.tar.gz
cd ahiguti-HandlerSocket-Plugin-for-MySQL-d032ec0/
./autogen.sh
./configure --with-mysql-source=${DIR}/mysql-5.1.51 --with-mysql-bindir=${DIR}/mysql5152/bin
make
sudo make install
# libtool: install: /usr/bin/install -c .libs/handlersocket.lai /home/rbradfor/projects/handlersocket/mysql5152/lib/plugin/handlersocket.la
# Install the Perl dependency
cd perl-Net-HandlerSocket
perl Makefile.PL
Checking if your kit is complete...
Warning: the following files are missing in your kit:
lib/HandlerSocket.pm
Please inform the author.
Writing Makefile for Net::HandlerSocket
# For some reason I had to run this twice, after error I check, saw nothing wrong, ran a second time and it worked ???
perl Makefile.PL
Writing Makefile for Net::HandlerSocket
make
sudo make install
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/local/lib/perl/5.10.1/auto/Net/HandlerSocket/HandlerSocket.so
Installing /usr/local/lib/perl/5.10.1/auto/Net/HandlerSocket/HandlerSocket.bs
Installing /usr/local/lib/perl/5.10.1/Net/HandlerSocket.pm
Installing /usr/local/lib/perl/5.10.1/Net/HandlerSocket/Pool.pm
Installing /usr/local/man/man3/Net::HandlerSocket.3pm
Appending installation info to /usr/local/lib/perl/5.10.1/perllocal.pod
cp lib/Net/HandlerSocket.pm blib/lib/Net/HandlerSocket.pm
cp lib/Net/HandlerSocket/Pool.pm blib/lib/Net/HandlerSocket/Pool.pm
/usr/bin/perl /usr/share/perl/5.10/ExtUtils/xsubpp -typemap /usr/share/perl/5.10/ExtUtils/typemap HandlerSocket.xs > HandlerSocket.xsc && mv HandlerSocket.xsc HandlerSocket.c
Please specify prototyping behavior for HandlerSocket.xs (see perlxs manual)
g++ -fPIC -c -I. -I../libhsclient -D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -g -O3 -Wall -Wno-unused -DVERSION=\"0.01\" -DXS_VERSION=\"0.01\" -fPIC "-I/usr/lib/perl/5.10/CORE" HandlerSocket.c
Running Mkbootstrap for Net::HandlerSocket ()
chmod 644 HandlerSocket.bs
rm -f blib/arch/auto/Net/HandlerSocket/HandlerSocket.so
LD_RUN_PATH="/usr/local/lib" g++ -fPIC -shared -O2 -g -L/usr/local/lib -fstack-protector HandlerSocket.o -o blib/arch/auto/Net/HandlerSocket/HandlerSocket.so \
-lhsclient \
chmod 755 blib/arch/auto/Net/HandlerSocket/HandlerSocket.so
cp HandlerSocket.bs blib/arch/auto/Net/HandlerSocket/HandlerSocket.bs
chmod 644 blib/arch/auto/Net/HandlerSocket/HandlerSocket.bs
Manifying blib/man3/Net::HandlerSocket.3pm
Now all built, it’s as simple as the following to install:
${DIR}/mysql5152/bin/mysql -uroot
mysql> INSTALL PLUGIN HandlerSocket SONAME 'handlersocket.so';
mysql> SHOW PLUGINS;
+---------------+----------+----------------+------------------+---------+
| Name | Status | Type | Library | License |
+---------------+----------+----------------+------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| handlersocket | ACTIVE | DAEMON | handlersocket.so | BSD |
+---------------+----------+----------------+------------------+---------+
11 rows in set (0.00 sec)
However, I found it not actually running even after restarting MySQL. You need the following configuration
cd ${DIR}/mysql5152
echo "[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)" >> my.cnf
After restarting you get the following confirmation.
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 | | 18 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+ 18 rows in set (0.00 sec)
My Test Data
USE test CREATE TABLE user ( user_id INT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, email VARCHAR(255) NOT NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(user_id) ) ENGINE=InnoDB; INSERT INTO user (user_id,name, email) VALUES (100,'Mickey Mouse','mickey@acme.inc'), (101,'Marvin the Martian','mars@warnerbros.inc'), (102,'Spiderman','spidy@marvelcomics.inc'); SELECT user_id,name,email FROM user WHERE user_id=101; +---------+--------------------+---------------------+ | user_id | name | email | +---------+--------------------+---------------------+ | 101 | Marvin the Martian | mars@warnerbros.inc | +---------+--------------------+---------------------+
My test retrieval program is basically unchanged from the original post.
$ cat retrieve.pl
#!/usr/bin/perl
use strict;
use warnings;
use Net::HandlerSocket;
#1. establishing a connection
my $args = { host => '10.0.0.6', 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',
'name,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();
If your HandlerSocket is not running you will see:
$ perl retrieve.pl FATAL_EXIT: getaddrinfo failed: ip_to_remote_host:9998 $ telnet 10.0.0.6 9998 Trying 10.0.0.6... telnet: Unable to connect to remote host: Connection refused
When working
$ telnet 10.0.0.6 9998 Trying 10.0.0.6... Connected to 10.0.0.6. Escape character is '^]'. P 0 test user PRIMARY name,email,created 0 1 0 = 1 101 0 3 Marvin the Martian mars@warnerbros.inc 2010-11-05 13:11:34 $ perl retrieve.pl Marvin the Martian mars@warnerbros.inc 2010-11-05 13:11:34
Again, great work by Yoshinori-san to have this code as production deployable. Now to run some real benchmarks.
Tags: Compiling, HandlerSocket, MySQL, performance
two little typos:
cd $DIR}/mysql5152
-> cd${DIR}
echo ” ..” > my.cnf
here Im not sure if it is ok/recommended to truncate my.cnf first.
[...] MySQL HandlerSocket under Ubuntu | MySQL Expert | MySQL Performance | MySQL Consulting [...]
Thanks.
For a tar install of MySQL, there is no my.cnf in this directory so truncation would not be an issue, and I don’t want multiple section headers. however for completeness I did make it an append rather then truncation as that also works.
Now to run some real benchmarks.
=====================
Could you give me any test results about this plugin?
I installed this plugin on mysql5.1.41 too, it works.
but it’s not that good as the author said(75000qps..), I uses python’s client.
All the links seem to be 404, can you update them ?
So far the best/detailed description ive found to get handlersocket setup…