mysql下mysql-udf-http效率測(cè)試小記

更新時(shí)間：2011年08月15日 01:09:38 作者：

看到張宴的博客上關(guān)于"http/rest客戶端的文章"，怎樣安裝啥的直接都跳過(guò)，下面直接進(jìn)入測(cè)試階段。

看到張宴的博客上關(guān)于"

 
[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 

內(nèi)存和交換分區(qū)：

復(fù)制代碼代碼如下:

 
[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> 

使用的表結(jié)構(gòu)：

復(fù)制代碼代碼如下:

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

復(fù)制代碼代碼如下:

 
<?php 
$type = $_GET['type']; 
print_r($_GET); 
include_once("gettime.php"); 
$btime = getmicrotime(); 
$loop_cnt= 1000; //循環(huán)次數(shù) 
$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>"; 
?> 

單獨(dú)執(zhí)行php連接MySQL，單條連接添加1000條記錄需要：0.9s左右
php操作memcache的程序：

復(fù)制代碼代碼如下:

 
<?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左右，
創(chuàng)建觸發(fā)器：

復(fù)制代碼代碼如下:

 
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; 
$$ 

為觸發(fā)器寫個(gè)php更新memcache,代碼如下：

復(fù)制代碼代碼如下:

 
<?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觸發(fā)MySQL添加1000條記錄，同時(shí)觸發(fā)器觸動(dòng)php更新memcache，使用時(shí)間9s左右，
因?yàn)槊看味缄P(guān)閉鏈接memcache，看是不是關(guān)閉鏈接導(dǎo)致慢，又寫了一個(gè)程序：

復(fù)制代碼代碼如下:

 
<?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++; 
} 
//關(guān)閉連接 
$etime = getmicrotime(); 
$runTime = round($etime - $btime, 4); 
echo "runTime: ".$runTime."\r\n<br>"; 
?> 

耗時(shí)0.9s左右，比一個(gè)連接慢不了多少。
為了定位是觸發(fā)器慢還是http_put慢，創(chuàng)建一個(gè)臨時(shí)表
tmp_mytable，表結(jié)構(gòu)如下：

復(fù)制代碼代碼如下:

 
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; 

再次修改觸發(fā)器，如下：

復(fù)制代碼代碼如下:

 
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條記錄，消耗時(shí)間0.7s左右，證明效率消耗在http_put，也就是mysql-udf-http慢。
不知道我的測(cè)試有錯(cuò)沒(méi)？還請(qǐng)正在使用mysql-udf-http的高手，或者對(duì)mysql-udf-http有研究的高手指教。

您可能感興趣的文章: