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 !
