MySQL触发器HTTP性能瓶颈分析与优化
看到张宴的博客上关于"http/rest客户端的文章"，怎样安装啥的直接都跳过，下面直接进入测试阶段，测试环境：虚拟机
[chinastor.com-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 内存和交换分区：
[chinastor.com-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： [chinastor.com-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."
"; $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."rn
"; >
单独执行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."rn
"; >
单条连接添加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."rn
"; >
耗时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有研究的高手指教。
原文链接：http://www.cnblogs.com/sunss/archive/2011/05/09/2041283.html