看到張宴的博客上關于"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的程序：
復制代碼 代碼如下:
$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."
";
$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."
";
$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
";
?>
單獨執行php連接MySQL，單條連接添加1000條記錄需要：0.9s左右
php操作memcache的程序：
復制代碼 代碼如下:
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
";
?>
單條連接添加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,代碼如下：
復制代碼 代碼如下:
$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，看是不是關閉鏈接導致慢，又寫了一個程序：
復制代碼 代碼如下:
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
";
?>
耗時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有研究的高手指教。
本文標題: mysql下mysql-udf-http效率測試小記
本文地址: http://www.cppcns.com/shujuku/mysql/83441.html
