mysql udf 异步_mysql使用UDF自动同步memcached效率笔记

接上篇：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|+---------------+-----------------------------------+1rowinset (0.00sec)

[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 mailin/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|+--------------------+1rowinset (0.00sec)

mysql>select memc_libmemcached_version();+-----------------------------+|memc_libmemcached_version()|+-----------------------------+|0.34|+-----------------------------+1rowinset (0.00sec)

mysql>

遇到问题：

No package 'libmemcached' found

Consider adjusting the PKG_CONFIG_PATH environment variableifyou

installed softwareina 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 pageformore 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内容：

效率测试：

插入：

<?phpinclude_once ("gettime.php");$btime=getmicrotime();$i=0;$mem=newMemcache();$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

删除：

<?phpinclude_once ("gettime.php");$btime=getmicrotime();$i=0;$mem=newMemcache();$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快多了