广

MYSQL

  • MYSQL
  • MSSQL
  • Redis
  • MongoDB
  • oracle数据库
  • 数据管理

    mysql下mysql-udf-http效率测试小记

    2018-09-29 11:23:15 次阅读 稿源:互联网
    广告
    全网推广平台,软文发布
    看到张宴的博客上关于"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
    $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."<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>";
    ?>

    单独执行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."/r/n<br>";
    ?>

    单条连接添加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."/r/n<br>";
    ?>

    耗时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有研究的高手指教。

    一起学吧部分文章转载自互联网,供读者交流和学习,若有涉及作者版权等问题请及时与我们联系,以便更正、删除或按规定办理。感谢所有提供资讯的网站,欢迎各类媒体与一起学吧进行文章共享合作。

    广告
    广告
    广告
    广告