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

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

[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的程序：

View Code

1 <? php
2 $type = $_GET [ ' type ' ];
3 print_r ( $_GET );
4 include_once ( " gettime.php " );
5 $btime = getmicrotime();
6 $loop_cnt = 1000 ; // 循环次数
7 $db_host = ' 127.0.0.1 ' ; //
8 $db_user = ' sunss ' ; //
9 $db_pass = ' 123456 ' ; //
10 $db_name = ' test ' ; //
11 $db_link = mysql_connect ( $db_host , $db_user , $db_pass ) or die ( " Connected failed: " . mysql_error () . " \n " );
12 mysql_query ( ' set names utf8 ' );
13 mysql_db_query ( $db_name , $db_link );
14 if ( " put " == $type ) { // 修改
15 $i = 1 ;
16 while ( $i <= $loop_cnt ) {
17 $title = " jkjkjkjkjkjkjkjkjkjkjkjkjk " ;
18 $tt = time ();
19 $sql = " update mytable set addtime= " . $tt . " ,title=' " . $title . " ' where id=' " . $i . " ' " ;
20 $res = mysql_query ( $sql );
21 if ( FALSE == $res ) {
22 echo " update failed!\n " ;
23 }
24 $i ++ ;
25 }
26 } else if ( " delete " == $type ) { // 删除
27 $i = 1 ;
28 while ( $i <= $loop_cnt ) {
29 $sql = " delete from mytable where id=' " . $i . " ' " ;
30 echo " delete sql: " . $sql . " <br> " ;
31 $res = mysql_query ( $sql );
32 if ( FALSE == $res ) {
33 echo " delete failed!\n " ;
34 }
35 $i ++ ;
36 }
37
38 } else if ( " post " == $type ) { // 添加
39 $i = 0 ;
40 while ( $i < $loop_cnt ) {
41 $title = " hahahahahahahahahahahahahahahahahaha " ;
42 $tt = time ();
43 $sql = " insert into mytable(addtime, title) values( $tt , ' " . $title . " ') " ;
44 // print "SQL: ".$sql."<br>";
45 $res = mysql_query ( $sql );
46 if ( FALSE == $res ) {
47 echo " insert failed!\n " ;
48 }
49 $i ++ ;
50 }
51 }
52 mysql_close ();
53 $etime = getmicrotime();
54 $runTime = round ( $etime - $btime , 4 );
55 echo " runTime: " . $runTime . " \r\n<br> " ;
56 ?>

单独执行php连接MySQL，单条连接添加1000条记录需要：0.9s左右

php操作memcache的程序：

View Code

1 <? php
2 include_once ( " gettime.php " );
3 $btime = getmicrotime();
4 // 杩炴帴
5 $mem_host = " 192.168.0.134 " ;
6 $mem_port = " 11311 " ;
7 $timeout = 3600 ;
8 $i = 0 ;
9 $cnt = 1000 ;
10 while ( $i < $cnt ) {
11 $mem = new Memcache;
12 $mem -> connect( $mem_host , $mem_port ) or die ( " Could not connect! " );
13 $ret = $mem -> set( $i , " 11111111111 " , 0 , $timeout );
14 if ( false == $ret ) {
15 file_put_contents ( " insert_failed.log " , " post failed!\n " , FILE_APPEND);
16 }
17 $mem -> close();
18 $i ++ ;
19 }
20
21 // 鍏抽棴杩炴帴
22 $etime = getmicrotime();
23 $runTime = round ( $etime - $btime , 4 );
24 echo " runTime: " . $runTime . " \r\n<br> " ;
25 ?>

单条连接添加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,代码如下：

View Code

<? 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，看是不是关闭链接导致慢，又写了一个程序：

View Code

<? 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有研究的高手指教。

转载于:https://www.cnblogs.com/sunss/archive/2011/05/09/2041283.html