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 , '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快多了

参考资料：memcached_functions_mysql测试 

转载于:https://www.cnblogs.com/sunss/archive/2011/06/07/2074435.html