mysql使用UDF自動同步memcached效率

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大概650s記錄還是可以滿足大多數應用的