MySQL可以使用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
#p#
导出所有memcache内容:
效率测试:
插入:
- <?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
原文链接:http://www.cnblogs.com/sunss/archive/2011/06/07/2074435.html
【编辑推荐】
- MySQL中创建及优化索引组织结构的思路
- 微博 请问你是怎么优化数据库的?
- MySQL技巧:结合相关参数 做好Limit优化
- MySQL数据库的优化(下)MySQL数据库的高可用架构方案
- MySQL数据库的优化(上)单机MySQL数据库的优化