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

接上篇：mysql使用mysql-udf-http效率测试笔记 ，这次不使用rest架构，而是使用：libmemcached和memcached_functions_mysql

<br>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\下安装有错误 <br> <br>MySQL测试版本：5.1.55，操作系统Centos5.4 64bit，内存2G <br><br>安装libmemcached-0.34和memcached_functions_mysql-0.9， <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>[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 <br> <br>安装完成后将UDFs加载到MySQL中： <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>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; <br> <br>查看各种版本： <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>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> <br> <br>遇到问题： <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>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 <br> <br>导出所有memcache内容： <br>使用： <br>效率测试： <br>插入： <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br><!--?php <BR-->include_once("gettime.php"); <br>$btime = getmicrotime(); <br>$i = 0; <br>$mem = new Memcache(); <br>$mem->addServer('192.168.0.10', 11212); <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 < 1000) { <br>$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>?> <br> <br>1000条，插入时间：runTime: 1.4072 <br>删除： <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br><!--?php <BR-->include_once("gettime.php"); <br>$btime = getmicrotime(); <br>$i = 0; <br>$mem = new Memcache(); <br>$mem->addServer('192.168.0.10', 11212); <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 < 1000) { <br>//$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>mysql_close($local_db); <br>$etime = getmicrotime(); <br>$runTime = round($etime - $btime, 4); <br>echo "runTime: ".$runTime."\r\n"; <br>?> <br> <br>删除1000条，运行时间：runTime: 1.5534 <br>更新未作 <br>结论：每秒query大概650条记录，比上次的mysql-udf-http快多了                    </li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre>