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

單獨執行PHP連接MySQL，單條連接添加1000條記錄需要：0.9s左右

PHP操作memcache的程序：

 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,代碼如下：

<?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左右，為了定位是觸發器慢還是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有研究的高手指教。