分类： Mysql/postgreSQL
2011-02-22 21:22:47
一、下载相关软件：
MySQL（目前的最新版本是5.5.8）源码：
http://dev.mysql.com/downloads/mysql/
Cmake（MySQL5.5开始使用的编译工具）：
bison（一个替代yacc的语法分析程序生成器，用来generate sql_yacc.cc from sql_yacc.yy）：
HandlerSocket Plugin for MySQL:
php-handlersocket（php客户端，测试用）：
二、安装MySQL5.5：
[root@localhost handlersocket]# tar zxvf mysql-5.5.8.tar.gz
[root@localhost handlersocket]# tar zxvf cmake-2.8.3-Linux-i386.tar.gz
[root@localhost bin]# ln -s /home/zoudp/handlersocket/cmake-2.8.3-Linux-i386/bin/ccmake /usr/bin/ccmake
[root@localhost handlersocket]# tar zxvf bison-2.4.tar.gz
[root@localhost handlersocket]# cd bison-2.4
[root@localhost bison-2.4]# ./configure
[root@localhost bison-2.4]# make
[root@localhost bison-2.4]# make install
[root@localhost handlersocket]# cd mysql-5.5.8
[root@localhost mysql-5.5.8]# cmake . -DCMAKE_INSTALL_PREFIX=/home/zoudp/handlersocket/mysql558/ -DSYSCONFDIR=/home/zoudp/handlersocket/mysql558/ -DWITH_EMBEDDED_SERVER=on -DWITH_READLINE=on -DWITH_SSL=yes -DENABLED_LOCAL_INFILE=on -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1
[root@localhost mysql-5.5.8]# make
[root@localhost mysql-5.5.8]# make install
这里要注意的是，如果之前编译失败过，官方的文档说执行以下代码清理：
shell> make clean
shell> rm CMakeCache.txt
而实际上这样是清理不完整的，建议直接删除源码，重新解压一个新鲜的源码出来编译。否则会遇到很多莫名其妙的错误。
还有DSYSCONFDIR这个参数会害死人的，mysqld仍然优先去找/etc/my.cnf而不是这里设置的路径，这是一直以来的一个bug.
三、配置MySQL：
[root@localhost handlersocket]# cd mysql558
[root@localhost mysql558]# vi my.cnf
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3316
socket = /home/zoudp/handlersocket/mysql558/mysql.sock
default-character-set=utf8
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3316
socket = /home/zoudp/handlersocket/mysql558/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
character-set-server=utf8
collation-server=utf8_general_ci
basedir=/home/zoudp/handlersocket/mysql558
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
server-id = 1
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /home/zoudp/handlersocket/mysql558/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/zoudp/handlersocket/mysql558/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
四、安装MySQL授权表、启动数据库测试并设置root用户密码：
[root@localhost mysql558]# chown -R mysql .
[root@localhost mysql558]# chgrp -R mysql .
[root@localhost mysql558]# scripts/mysql_install_db --defaults-file=/home/zoudp/handlersocket/mysql558/my.cnf --user=mysql
[root@localhost mysql558]# bin/mysqld_safe --defaults-file=/home/zoudp/handlersocket/mysql558/my.cnf --user=mysql &
[root@localhost mysql558]# bin/mysqladmin version
bin/mysqladmin Ver 8.42 Distrib 5.5.8, for Linux on i686
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.5.8-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /home/zoudp/handlersocket/mysql558/mysql.sock
Uptime: 2 min 46 sec
Threads: 1 Questions: 1 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.6
[root@localhost mysql558]# bin/mysqladmin -u root shutdown
[root@localhost mysql558]# bin/mysqld_safe --defaults-file=/home/zoudp/handlersocket/mysql558/my.cnf --user=mysql &
[root@localhost mysql558]# bin/mysqlshow
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@localhost mysql558]# bin/mysql -u root
mysql> set password for 'root'@'localhost'=password('banping');
Query OK, 0 rows affected (0.00 sec)
五、安装HandlerSocket Plugin：
[root@localhost handlersocket]# tar -zxvf ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-67-g25f4957.tar.gz
[root@localhost handlersocket]# cd handlersocket/
[root@localhost handlersocket]# ./autogen.sh
[root@localhost handlersocket]# ./configure --with-mysql-source=/home/zoudp/handlersocket/mysql-5.5.8 --with-mysql-bindir=/home/zoudp/handlersocket/mysql558/bin --with-mysql-plugindir=/home/zoudp/handlersocket/mysql558/lib/plugin
[root@localhost handlersocket]# make
[root@localhost handlersocket]# make install
在my.cnf配置文件增加以下内容：
[root@localhost mysql558]# vi my.cnf
# The MySQL server
[mysqld]
......
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.
登录mysql安装这个plugin：
[root@localhost mysql558]# bin/mysqladmin -u root shutdown -p
[root@localhost mysql558]# bin/mysqld_safe --defaults-file=/home/zoudp/handlersocket/mysql558/my.cnf --user=mysql &
[root@localhost mysql558]# bin/mysql -u root -p
mysql> install plugin handlersocket soname 'handlersocket.so';
Query OK, 0 rows affected (0.01 sec)
mysql> show processlist;
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| 1 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 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 | NULL | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL |
| 18 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
18 rows in set (0.00 sec)
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 |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | 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 |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| handlersocket | ACTIVE | DAEMON | handlersocket.so | BSD |
+-----------------------+--------+--------------------+------------------+---------+
19 rows in set (0.00 sec)
六、安装php扩展包测试
[root@localhost handlersocket]# tar zxvf php-handlersocket-0.0.7.tar.gz
[root@localhost php-handlersocket]# /usr/local/php533/bin/phpize
[root@localhost php-handlersocket]# ./configure --with-php-config=/usr/local/php533/bin/php-config
[root@localhost php-handlersocket]# make
[root@localhost php-handlersocket]# make install
[root@localhost php-handlersocket]# cd /usr/local/php533/etc
[root@localhost etc]# vi php.ini
extension = "handlersocket.so"
[root@localhost etc]# ps -ef|grep fpm
nobody 394 24696 0 Jan14 ? 00:00:32 /usr/local/php533/sbin/php-fpm
nobody 419 24696 0 Jan14 ? 00:00:31 /usr/local/php533/sbin/php-fpm
nobody 485 24696 0 Jan14 ? 00:00:32 /usr/local/php533/sbin/php-fpm
nobody 486 24696 0 Jan14 ? 00:00:32 /usr/local/php533/sbin/php-fpm
nobody 520 24696 0 Jan14 ? 00:00:39 /usr/local/php533/sbin/php-fpm
nobody 546 24696 0 Jan14 ? 00:00:31 /usr/local/php533/sbin/php-fpm
nobody 591 24696 0 Jan14 ? 00:00:32 /usr/local/php533/sbin/php-fpm
nobody 824 24696 0 Jan14 ? 00:00:30 /usr/local/php533/sbin/php-fpm
nobody 829 24696 0 Jan14 ? 00:00:31 /usr/local/php533/sbin/php-fpm
nobody 928 24696 0 Jan14 ? 00:00:30 /usr/local/php533/sbin/php-fpm
nobody 8274 24696 0 Jan14 ? 00:00:28 /usr/local/php533/sbin/php-fpm
nobody 8568 24696 0 Jan14 ? 00:00:28 /usr/local/php533/sbin/php-fpm
nobody 9083 24696 0 Jan14 ? 00:00:28 /usr/local/php533/sbin/php-fpm
nobody 9421 24696 0 Jan14 ? 00:00:27 /usr/local/php533/sbin/php-fpm
nobody 11369 24696 0 Jan14 ? 00:00:27 /usr/local/php533/sbin/php-fpm
root 22589 28942 0 14:45 pts/3 00:00:00 grep fpm
root 24696 1 0 2010 ? 00:00:33 /usr/local/php533/sbin/php-fpm
nobody 28846 24696 0 Jan19 ? 00:00:02 /usr/local/php533/sbin/php-fpm
[root@localhost etc]# kill -SIGINT 24696
[root@localhost etc]# /usr/local/php533/sbin/php-fpm
建立测试表：
mysql> create table t (id int(10) unsigned primary key,k varchar(10),v varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> desc t;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| k | varchar(10) | YES | | NULL | |
| v | varchar(10) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t values(1,'k1','v1');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t values(2,'k2','v2');
Query OK, 1 row affected (0.01 sec)
mysql> create index inx_k on t(k);
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
用php程序测试：
[root@localhost banping]# vi test.php
$host = 'localhost';
$port = 9998;
$port_wr = 9999;
$dbname = 'test';
$table = 't';
//GET
$hs = new HandlerSocket($host, $port);
if (!($hs->openIndex(1, $dbname, $table,'inx_k', 'k,v')))
{
echo $hs->getError(), PHP_EOL;
die();
}
$retval = $hs->executeSingle(1, '=', array('k1'), 1, 0);
var_dump($retval);
$retval = $hs->executeMulti(
array(array(1, '=', array('k1'), 1, 0),
array(1, '=', array('k2'), 1, 0)));
var_dump($retval);
unset($hs);
?>
运行这个php查看输出结果：
array(1) { [0]=> array(2) { [0]=> string(2) "k1" [1]=> string(2) "v1" } } array(2) { [0]=> array(1) { [0]=> array(2) { [0]=> string(2) "k1" [1]=> string(2) "v1" } } [1]=> array(1) { [0]=> array(2) { [0]=> string(2) "k2" [1]=> string(2) "v2" } } }
至此测试完成，这个功能可用了，但是个人认为，用PHP程序不能最大限度的发挥HandlerSocket for MySQL的性能，因为建立一个HandlerSocket的openIndex后，这个openIndex最好能够不断的重用，这样能最大限度的减少开 销，那么客户端的数据库连接池就是实现这一功能的最好方式。但php由于程序架构上的设计，单个请求完成就释放掉全部资源，并不支持连接池，所以只能每个 请求就open一个index.所以和java等其他语言比，效率会低一些。
