接上篇：mysql使用mysql-udf-http效率测试笔记 ，这次不使用rest架构，而是使用：libmemcached和memcached_functions_mysql，测试版本是： libmemcached-0.34.tar.gz和memcached_functions_mysql-0.9.tar.gz，其它版本
接上篇：mysql使用mysql-udf-http效率测试笔记 ，这次不使用rest架构，而是使用：libmemcached和memcached_functions_mysql，测试版本是： libmemcached-0.34.tar.gz和memcached_functions_mysql-0.9.tar.gz，其它版本配对都有问题，我安装测试过有问题的版本有： 复制代码 代码如下: memcached_functions_mysql-1.1在： libmemcached-0.49\libmemcached-0.48\libmemcached-0.47\libmemcached-0.30\libmemcached-0.43\\libmemcached-0.42\ 下安装有错误 memcached_functions_mysql-0.10在： libmemcached-0.42\下安装有错误 memcached_functions_mysql-0.8在： libmemcached-0.49\libmemcached-0.48\libmemcached-0.47\libmemcached-0.44\libmemcached-0.43\ \libmemcached-0.42\下安装有错误 MySQL测试版本：5.1.55，操作系统Centos5.4 64bit，内存2G 安装libmemcached-0.34和memcached_functions_mysql-0.9， 复制代码 代码如下: [root@sunss24 libmemcached-0.34]#./configure \ --with-memcached=/home/memcache/bin/memcached [root@sunss24 libmemcached-0. 34]# make [root@sunss24 libmemcached-0. 34]# make install 再运行一下memstat，算成功了 [root@sunss24 ~]# ln -s /usr/local/lib/libmemcached.so.3 /usr/lib/ [root@sunss24 ~]# cd memcached_functions_mysql-0.9 [root@sunss24 memcached_functions_mysql-0.9]# ./configure \ --with-mysql=/usr/local/mysql/bin/mysql_config \ --libdir=/usr/local/mysql/lib/ [root@sunss memcached_functions_mysql-0.9]# make && make install 安装完成后将UDFs加载到MySQL中： 复制代码 代码如下: mysql> show variables like "%plugin%"; +---------------+-----------------------------------+ | Variable_name | Value | +---------------+-----------------------------------+ | plugin_dir | /usr/local/mysql/lib/mysql/plugin | +---------------+-----------------------------------+ 1 row in set (0.00 sec) [root@sunss ~]# find / -name "libmemcached_functions_mysql.so" /usr/local/mysql/lib/libmemcached_functions_mysql.so /root/memcached_functions_mysql-0.9/src/.libs/libmemcached_functions_mysql.so You have new mail in /var/spool/mail/root [root@sunss ~]# cp /usr/local/mysql/lib/libmemcached_functions_mysql.so /usr/local/mysql/lib/mysql/plugin/ [root@sunss ~]# cd memcached_functions_mysql-0.9/ [root@sunss ~]#cd sql/ mysql> source install_functions.sql; 查看各种版本： 复制代码 代码如下: mysql> select memc_udf_version(); +--------------------+ | memc_udf_version() | +--------------------+ | 0.9 | +--------------------+ 1 row in set (0.00 sec) mysql> select memc_libmemcached_version(); +-----------------------------+ | memc_libmemcached_version() | +-----------------------------+ | 0.34 | +-----------------------------+ 1 row in set (0.00 sec) mysql> 遇到问题： 复制代码 代码如下: No package 'libmemcached' found Consider adjusting the PKG_CONFIG_PATH environment variable if you installed software in a non-standard prefix. Alternatively, you may set the environment variables DEPS_CFLAGS and DEPS_LIBS to avoid the need to call pkg-config. See the pkg-config man page for more details. 解决办法： [root@sunss24 memcached_functions_mysql-0.9]# whereis pkgconfig [root@sunss24 memcached_functions_mysql-0.9]# export \ PKG_CONFIG_PATH=/usr/local/lib/pkgconfig:/usr/lib/pkgconfig 导出所有memcache内容： 使用：memcached-hack.zip 效率测试： 插入： 复制代码 代码如下: <?php include_once("gettime.php"); $btime = getmicrotime(); $i = 0; $mem = new Memcache(); $mem->addServer('192.168.0.10', 11212); $local_db = mysql_connect("192.168.0.208", "sunss", "123456"); if(!$local_db) { die('Could not connect: '.mysql_error()); } $local_db_sel = mysql_select_db("test", $local_db); mysql_query("set names utf8", $local_db); while ( $i < 1000) { $re_sql = "insert into urls (id,url) values ($i, 'www.gongchang.com')"; $res = mysql_query($re_sql, $local_db); $i++; } mysql_close($local_db); $etime = getmicrotime(); $runTime = round($etime - $btime, 4); echo "runTime: ".$runTime."\r\n"; ?> 1000条，插入时间：runTime: 1.4072 删除： 复制代码 代码如下: <?php include_once("gettime.php"); $btime = getmicrotime(); $i = 0; $mem = new Memcache(); $mem->addServer('192.168.0.10', 11212); $local_db = mysql_connect("192.168.0.208", "sunss", "123456"); if(!$local_db) { die('Could not connect: '.mysql_error()); } $local_db_sel = mysql_select_db("test", $local_db); mysql_query("set names utf8", $local_db); while ( $i < 1000) { //$re_sql = "insert into urls (id,url) values ($i, 'www.gongchang.com')"; $re_sql = "delete from urls where id=".$i; //echo "re_sql_1: ".$re_sql."\n"; $res = mysql_query($re_sql, $local_db); $i++; } mysql_close($local_db); $etime = getmicrotime(); $runTime = round($etime - $btime, 4); echo "runTime: ".$runTime."\r\n"; ?> 删除1000条，运行时间：runTime: 1.5534 更新未作 结论：每秒query大概650条记录，比上次的mysql-udf-http快多了
