mysql的mysql-udf-http效率测试小记_MySQL-mysql教程-PHP中文网

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

php中文网

发布： 2016-06-01 13:51:49

原创

1176人浏览过

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

[root@localhost ~]# uname -a<br />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<br />             total       used       free     shared    buffers     cached<br />Mem:           376        363         13          0         23        105<br />-/+ buffers/cache:        233        142<br />Swap:         1023        133        890

登录后复制

mysql：

[root@localhost ~]# mysql -u root -p<br />Enter password: <br />Welcome to the MySQL monitor.  Commands end with ; or /g.<br />Your MySQL connection id is 57<br />Server version: 5.1.26-rc-log Source distribution<br /><br />Type 'help;' or '/h' for help. Type '/c' to clear the buffer.<br />mysql>

登录后复制

使用的表结构：

DROP TABLE IF EXISTS `mytable`;<br /><br />CREATE TABLE `mytable` (<br />  `id` int(10) NOT NULL AUTO_INCREMENT,<br />  `addtime` int(10) NOT NULL,<br />  `title` varchar(255) NOT NULL,<br />  PRIMARY KEY (`id`)<br />) ENGINE=MyISAM DEFAULT CHARSET=utf8;

登录后复制

php操作MySQL的程序：

View Code

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

登录后复制

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

php操作memcache的程序：

View Code

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

登录后复制

单条连接添加1000条记录，需要0.8s左右，

创建触发器：

Pandora Avatars

可以制作100多种独特风格的头像

102

查看详情

DELIMITER $$<br /><br />DROP TRIGGER /*!50032 IF EXISTS */ `test`.`mytable_insert`$$<br /><br />CREATE<br />    /*!50017 DEFINER = 'root'@'localhost' */<br />    TRIGGER `mytable_insert` AFTER INSERT ON `mytable` <br />    FOR EACH ROW BEGIN  <br />    SET @tt_resu = (SELECT http_put(CONCAT('http://192.168.0.134/mem_ss.php?type=post&id=', NEW.id, "&data=", NEW.addtime), 11));<br />END;<br />$$

登录后复制

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

View Code

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

登录后复制

使用php触发MySQL添加1000条记录，同时触发器触动php更新memcache，使用时间9s左右，

因为每次都关闭链接memcache，看是不是关闭链接导致慢，又写了一个程序： mysql的mysql-udf-http效率测试小记_MySQL View Code

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

登录后复制

耗时0.9s左右，比一个连接慢不了多少。

为了定位是触发器慢还是http_put慢，创建一个临时表

tmp_mytable，表结构如下：<br /><pre class="brush:php;toolbar:false;">CREATE TABLE `mytable` (<br />  `id` int(10) NOT NULL AUTO_INCREMENT,<br />  `addtime` int(10) NOT NULL,<br />  `title` varchar(255) NOT NULL<br />) ENGINE=MyISAM DEFAULT CHARSET=utf8;

登录后复制

再次修改触发器，如下：

DELIMITER $$<br /><br />DROP TRIGGER /*!50032 IF EXISTS */ `test`.`mytable_insert`$$<br /><br />CREATE<br />    /*!50017 DEFINER = 'root'@'localhost' */<br />    TRIGGER `mytable_insert` AFTER INSERT ON `mytable` <br />    FOR EACH ROW BEGIN  <br />     insert into tmp_mytable values(NEW.id,NEW.addtime,NEW.title);   <br />END;<br />$$

登录后复制

再次用php向MySQL中添加1000条记录，消耗时间0.7s左右，证明效率消耗在http_put，也就是mysql-udf-http慢。

不知道我的测试有错没？还请正在使用mysql-udf-http的高手，或者对mysql-udf-http有研究的高手指教。