广

MYSQL

  • MYSQL
  • MSSQL
  • Redis
  • MongoDB
  • oracle数据库
  • 数据管理

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

    2018-09-29 11:23:12 次阅读 稿源:互联网
    零七网广告
    全网推广平台,软文发布
    接上篇: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快多了

    零七网部分新闻及文章转载自互联网,供读者交流和学习,若有涉及作者版权等问题请及时与我们联系,以便更正、删除或按规定办理。感谢所有提供资讯的网站,欢迎各类媒体与零七网进行文章共享合作。

    零七网广告
    零七网广告
    零七网广告
    零七网广告