mysql使用UDF自动同步memcached效率笔记_MySQL

memcache

接上篇：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在：<br>libmemcached-0.49/libmemcached-0.48/libmemcached-0.47/libmemcached-0.30/libmemcached-0.43//libmemcached-0.42/<br>下安装有错误<br>memcached_functions_mysql-0.10在：<br>libmemcached-0.42/下安装有错误<br>memcached_functions_mysql-0.8在：<br>libmemcached-0.49/libmemcached-0.48/libmemcached-0.47/libmemcached-0.44/libmemcached-0.43/<br>/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 /<br>--with-memcached=/home/memcache/bin/memcached<br>[root@sunss24 libmemcached-0. 34]# make<br>[root@sunss24 libmemcached-0. 34]# make install<br>再运行一下memstat，算成功了<br>[root@sunss24 ~]# ln -s /usr/local/lib/libmemcached.so.3 /usr/lib/<br>[root@sunss24 ~]# cd memcached_functions_mysql-0.9<br>[root@sunss24 memcached_functions_mysql-0.9]# ./configure /<br>--with-mysql=/usr/local/mysql/bin/mysql_config /<br>--libdir=/usr/local/mysql/lib/<br>[root@sunss memcached_functions_mysql-0.9]# make && make install

安装完成后将UDFs加载到MySQL中：

mysql> show variables like "%plugin%";<br>+---------------+-----------------------------------+<br>| Variable_name | Value                             |<br>+---------------+-----------------------------------+<br>| plugin_dir    | /usr/local/mysql/lib/mysql/plugin | <br>+---------------+-----------------------------------+<br>1 row in set (0.00 sec)<br><br>[root@sunss ~]# find / -name "libmemcached_functions_mysql.so"<br>/usr/local/mysql/lib/libmemcached_functions_mysql.so<br>/root/memcached_functions_mysql-0.9/src/.libs/libmemcached_functions_mysql.so<br>You have new mail in /var/spool/mail/root<br>[root@sunss ~]# cp /usr/local/mysql/lib/libmemcached_functions_mysql.so /usr/local/mysql/lib/mysql/plugin/<br>[root@sunss ~]# cd memcached_functions_mysql-0.9/<br>[root@sunss ~]#cd sql/<br>mysql> source install_functions.sql;

查看各种版本：

mysql> select memc_udf_version();<br>+--------------------+<br>| memc_udf_version() |<br>+--------------------+<br>| 0.9                | <br>+--------------------+<br>1 row in set (0.00 sec)<br><br>mysql> select memc_libmemcached_version();<br>+-----------------------------+<br>| memc_libmemcached_version() |<br>+-----------------------------+<br>| 0.34                        | <br>+-----------------------------+<br>1 row in set (0.00 sec)<br><br>mysql>

 遇到问题：

No package 'libmemcached' found<br><br>Consider adjusting the PKG_CONFIG_PATH environment variable if you<br>installed software in a non-standard prefix.<br><br>Alternatively, you may set the environment variables DEPS_CFLAGS<br>and DEPS_LIBS to avoid the need to call pkg-config.<br>See the pkg-config man page for more details.<br>解决办法：<br>[root@sunss24 memcached_functions_mysql-0.9]# whereis pkgconfig<br>[root@sunss24 memcached_functions_mysql-0.9]# export /<br>PKG_CONFIG_PATH=/usr/local/lib/pkgconfig:/usr/lib/pkgconfig

导出所有memcache内容：

使用：memcached-hack.zip

效率测试：

插入：

<?php <br />    include_once("gettime.php");<br><br>    $btime = getmicrotime();<br>    $i = 0; <br>    $mem = new Memcache();<br>    $mem->addServer('192.168.0.10', 11212);<br>    <br>    $local_db = mysql_connect("192.168.0.208", "sunss", "123456");<br>    if(!$local_db)<br>    {<br>        die('Could not connect: '.mysql_error());<br>    }<br>    $local_db_sel = mysql_select_db("test", $local_db);<br>    mysql_query("set names utf8", $local_db);<br>        while ( $i         $re_sql = "insert into urls (id,url) values ($i, 'www.gongchang.com')";<br>        $res = mysql_query($re_sql, $local_db);<br>                $i++;<br>        } <br>    mysql_close($local_db);<br>    $etime = getmicrotime();<br>    $runTime = round($etime - $btime, 4);<br>    echo "runTime: ".$runTime."/r/n";<br>?>

1000条，插入时间：runTime: 1.4072

删除：

<?php <br />    include_once("gettime.php");<br><br>    $btime = getmicrotime();<br>    $i = 0;<br>    <br>    $mem = new Memcache();<br>    $mem->addServer('192.168.0.10', 11212);<br>    <br>    $local_db = mysql_connect("192.168.0.208", "sunss", "123456");<br>    if(!$local_db)<br>    {<br>        die('Could not connect: '.mysql_error());<br>    }<br><br><br>    $local_db_sel = mysql_select_db("test", $local_db);<br>    mysql_query("set names utf8", $local_db);<br>        while ( $i         //$re_sql = "insert into urls (id,url) values ($i, 'www.gongchang.com')";<br>        $re_sql = "delete from urls where id=".$i;<br>        //echo "re_sql_1: ".$re_sql."/n";<br>                $res = mysql_query($re_sql, $local_db);<br>                $i++;<br>        }<br><br>    mysql_close($local_db);<br>    $etime = getmicrotime();<br>    $runTime = round($etime - $btime, 4);<br>    echo "runTime: ".$runTime."/r/n";<br>?>

删除1000条，运行时间：runTime: 1.5534

更新未作

结论：每秒query大概650条记录，比上次的mysql-udf-http快多了

参考资料：memcached_functions_mysql测试