mysql下mysql-udf-http效率测试小记

看到张宴的博客上关于""，怎样安装啥的直接都跳过，下面直接进入测试阶段，测试环境：虚拟机

[root@localhost ~]# uname -a
linux sunss 2.6.18-128.el5 #1 smp wed jan 21 10:44:23 est 2009 i686 i686 i386 gnu/linux

内存和交换分区：

[root@localhost ~]# free -m
total used free shared buffers cached
mem: 376 363 13 0 23 105
-/+ buffers/cache: 233 142
swap: 1023 133 890
mysql：
[root@localhost ~]# mysql -u root -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 57
server version: 5.1.26-rc-log source distribution
type 'help;' or '\h' for help. type '\c' to clear the buffer.
mysql>

使用的表结构：

drop table if exists `mytable`;
create table `mytable` (
`id` int(10) not null auto_increment,
`addtime` int(10) not null,
`title` varchar(255) not null,
primary key (`id`)
) engine=myisam default charset=utf8;

php操作mysql的程序：

<?php
$type = $_get['type'];
print_r($_get);
include_once("gettime.php");
$btime = getmicrotime();
$loop_cnt= 1000; //循环次数
$db_host = '127.0.0.1'; //
$db_user = 'sunss'; //
$db_pass = '123456'; //
$db_name = 'test'; //
$db_link = mysql_connect($db_host, $db_user, $db_pass) or die("connected failed: ".mysql_error()."\n");
mysql_query('set names utf8');
mysql_db_query($db_name, $db_link);
if ("put" == $type) {//修改
$i = 1;
while ($i <= $loop_cnt) {
$title = "jkjkjkjkjkjkjkjkjkjkjkjkjk";
$tt = time();
$sql = "update mytable set addtime=".$tt.",title='".$title."' where id='".$i."'";
$res = mysql_query($sql);
if (false == $res) {
echo "update failed!\n";
}
$i++;
}
} else if ("delete" == $type) { //删除
$i = 1;
while ($i <= $loop_cnt) {
$sql = "delete from mytable where id='".$i."'";
echo "delete sql: ".$sql."<br>";
$res = mysql_query($sql);
if (false == $res) {
echo "delete failed!\n";
}
$i++;
}

} else if ("post" == $type) { //添加
$i = 0;
while ($i < $loop_cnt) {
$title = "hahahahahahahahahahahahahahahahahaha";
$tt = time();
$sql = "insert into mytable(addtime, title) values($tt, '".$title."')";
//print "sql: ".$sql."<br>";
$res = mysql_query($sql);
if (false == $res) {
echo "insert failed!\n";
}
$i++;
}
}
mysql_close();
$etime = getmicrotime();
$runtime = round($etime - $btime, 4);
echo "runtime: ".$runtime."\r\n<br>";
?>

单独执行php连接mysql，单条连接添加1000条记录需要：0.9s左右
php操作memcache的程序：

<?php
include_once("gettime.php");
$btime = getmicrotime();
//杩炴帴
$mem_host = "192.168.0.134";
$mem_port = "11311";
$timeout = 3600;
$i = 0;
$cnt = 1000;
while ($i < $cnt) {
$mem = new memcache;
$mem->connect($mem_host, $mem_port) or die("could not connect!");
$ret = $mem->set($i, "11111111111", 0, $timeout);
if (false == $ret) {
file_put_contents("insert_failed.log", "post failed!\n", file_append);
}
$mem->close();
$i++;
}

//鍏抽棴杩炴帴
$etime = getmicrotime();
$runtime = round($etime - $btime, 4);
echo "runtime: ".$runtime."\r\n<br>";
?>

单条连接添加1000条记录，需要0.8s左右，
创建触发器：

delimiter $$
drop trigger /*!50032 if exists */ `test`.`mytable_insert`$$
create
/*!50017 definer = 'root'@'localhost' */
trigger `mytable_insert` after insert on `mytable`
for each row begin
set @tt_resu = (select http_put(concat('http://192.168.0.134/mem_ss.php?type=post&id=', new.id, "&data=", new.addtime), 11));
end;
$$

为触发器写个php更新memcache,代码如下：

<?php
$id = $_get['id'];
$type = $_get['type'];
$json_data = $_get['data'];
var_dump($_get);
//杩炴帴
$mem_host = "192.168.0.134";
$mem_port = "11211";
$timeout = 3600;
$mem = new memcache;
$mem->connect($mem_host, $mem_port) or die("could not connect!");
if ("get" == $type ) {
$val = $mem->get($id);
echo $val;
//$arr = jsondecode($val,'utf-8');
//print_r($arr);
} else if ("put" == $type) {
$ret = $mem->replace($id, $json_data, 0, $timeout);
if (false == $ret) {
file_put_contents("replace_failed.log", "replace failed!\n", file_append);
}
} else if ("delete" == $type) {
$ret = $mem->delete($id);
if (false == $ret) {
file_put_contents("delete_failed.log", "delete failed!\n", file_append);
}
} else if ("post" == $type) {
$ret = $mem->set($id, $json_data, 0, $timeout);
if (false == $ret) {
file_put_contents("post_failed.log", "post failed!\n", file_append);
}
}
$mem->close();
?>

使用php触发mysql添加1000条记录，同时触发器触动php更新memcache，使用时间9s左右，
因为每次都关闭链接memcache，看是不是关闭链接导致慢，又写了一个程序：

<?php
include_once("gettime.php");
$btime = getmicrotime();
//连接
$mem_host = "192.168.0.134";
$mem_port = "11311";
$timeout = 3600;
$i = 0;
$cnt = 1000;
while ($i < $cnt) {
$mem = new memcache;
$mem->connect($mem_host, $mem_port) or die("could not connect!");
$ret = $mem->set($i, "11111111111", 0, 3600);
if (false == $ret) {
file_put_contents("insert_failed.log", "post failed!\n", file_append);
}
$mem->close();
$i++;
}
//关闭连接
$etime = getmicrotime();
$runtime = round($etime - $btime, 4);
echo "runtime: ".$runtime."\r\n<br>";
?>

耗时0.9s左右，比一个连接慢不了多少。
为了定位是触发器慢还是http_put慢，创建一个临时表
tmp_mytable，表结构如下：

create table `mytable` (
`id` int(10) not null auto_increment,
`addtime` int(10) not null,
`title` varchar(255) not null
) engine=myisam default charset=utf8;

再次修改触发器，如下：

delimiter $$
drop trigger /*!50032 if exists */ `test`.`mytable_insert`$$
create
/*!50017 definer = 'root'@'localhost' */
trigger `mytable_insert` after insert on `mytable`
for each row begin
insert into tmp_mytable values(new.id,new.addtime,new.title);
end;
$$

再次用php向mysql中添加1000条记录，消耗时间0.7s左右，证明效率消耗在http_put，也就是mysql-udf-http慢。
不知道我的测试有错没？还请正在使用mysql-udf-http的高手，或者对mysql-udf-http有研究的高手指教。