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 |
+—————+———————————–+ 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, ”)”; $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, ”)”; $re_sql = “delete from urls where 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快多了

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图